Creating Multiple Tables in a SQLite Database

Databases
1

For whatever reason I’m having a ton of trouble creating multiple tables in a SQLite database.
I’ve tried two methods, tons of changes to the query string, nothing works.

Method 1:

database.SQLExecute("CREATE TABLE documentSettings (id INTEGER PRIMARY KEY, key TEXT, value TEXT);") database.SQLExecute("CREATE TABLE documentCategories (id INTEGER PRIMARY KEY, order INTEGER, name TEXT);") database.SQLExecute("CREATE TABLE documentData (id INTEGER PRIMARY KEY, order INTEGER, category INTEGER, question TEXT, shortAnswer TEXT, longAnswer TEXT);")

Method 2:

database.SQLExecute("CREATE TABLE documentSettings (id INTEGER PRIMARY KEY, key TEXT, value TEXT); CREATE TABLE documentCategories (id INTEGER PRIMARY KEY, order INTEGER, name TEXT); CREATE TABLE documentData (id INTEGER PRIMARY KEY, order INTEGER, category INTEGER, question TEXT, shortAnswer TEXT, longAnswer TEXT);")

When I backup and read the database I get only the first table, documentSettings.
If I take out the CREATE TABLE for documentSettings I get nothing useful for the backup. I think it’s dummy text I get actually.

Been thinking too hard for too long, does anyone have any insight?

2

Are you committing somewhere? I’m pretty sure if you don’t commit at the end then you haven’t really finished creating the tables.

3

What happens if you do:

database.SQLExecute("BEGIN TRANSACTION") database.SQLExecute("CREATE TABLE documentSettings (id INTEGER PRIMARY KEY, key TEXT, value TEXT);") database.SQLExecute("CREATE TABLE documentCategories (id INTEGER PRIMARY KEY, order INTEGER, name TEXT);") database.SQLExecute("CREATE TABLE documentData (id INTEGER PRIMARY KEY, order INTEGER, category INTEGER, question TEXT, shortAnswer TEXT, longAnswer TEXT);") If Not database.Error Then database.Commit() Else // Log database.ErrorMessage End If
?

4

After a SQLExecute, you should always check for Error and, if true, the ErrorMessage. In this case it would have reported a syntax error near “order”. In other words, you can’t use “order” as a field name. Choose something else and it works fine.

5

You guys are absolutely right, I need to be error checking. I was just so tired of trying to find a data storage method that I suppose I skipped over error checking.

Anywhoo, Kem was right - naming a column “order” was the problem. Changed it to sortOrder and everything’s happy now :slight_smile:

Thanks for the help!