Adding a multi-column SQLite primary key

[rleiman]

Hi Everyone,

Can you check my syntax and correct it for me? I'm trying to create a primary key on the StoreName and ItemName columns of this SQLite table.

objSql.ExecNonQuery("CREATE TABLE GroceryItems (" & _
    "StoreName TEXT DEFAULT ' ', " & _
    "ItemName TEXT DEFAULT ' ', " & _
    "LocationInStore TEXT DEFAULT ' ', " & _
    "IsleNumber TEXT DEFAULT ' ', " & _
    "LocationWithinIsle TEXT DEFAULT ' ', " & _
    "LocationHeight TEXT DEFAULT ' ', " & _
    "PRIMARY KEY (StoreName, ItemName) ' ' " & _
    ")")

Thanks.

 

[NJDude]

Unless is absolutely neccessary I would rather create the databases using an editor like SQLite Studio, it's easier.

My humble opinion.

 

[Mahares]

I would not go against anything NJDude recommends, but here is what you are looking for if you want to use your code:

Dim objSql As SQL  'process globals

Dim txt As String    'in globals

objSql.Initialize(File.DirRootExternal, "MyShopping.db", True)  'created in root external
txt="CREATE TABLE GroceryItems (StoreName TEXT , ItemName TEXT , LocationInStore TEXT ,IsleNumber TEXT , LocationWithinIsle TEXT ,LocationHeight TEXT , PRIMARY KEY (StoreName, ItemName))"
objSql.ExecNonQuery(txt)

 

[rleiman]

Thanks Mahares.

I found with your help I messed up with the rounded bracket.


NJDude,
I'm going to try SQLite Studio. Can it work like SQL Server Management Studio?

It would be nice to connect to databases on the Android device from within SQLite Studio and perform test queries, etc.

 

[NJDude]

It would be nice to connect to databases on the Android device from within SQLite Studio and perform test queries, etc.

If you set your device as USB Storage/Disk Drive you can do something like that.

 

[rleiman]

I will experiment with it.

Glad to know there's something out there that will help in creating the queries.

Are there any other open source programs for us B4A developers?

 

[poseidon]

there is also a freeware based on .NET framework 2, no installation needed

http://www.pipiscrew.com/works/sqlite-manager-v1-0/

be sure you watch the video tutorial

 

[rleiman]

Hi poseidon,

Thanks for the info. I use VS 2010. I hope it works with that version as well as the 2008 version the web site described.