Android Question Android SQLLite and Vb.net

[raphipps2002]

I am new to SQL so please go easy! I have created a very simple DB in B4A using the Tutorial here -

SQL1.Initialize(File.DirDefaultExternal, "test1.db", True)

and then Create a table...

 SQL1.ExecNonQuery("CREATE TABLE table1 (col1 TEXT , col2 INTEGER, col3 INTEGER)")

My app has an option to email the db to me and I wanted to write a vb.net app that reads the DB. However what I learned was that the tutorial in android avoids a integer PRIMARY KEY whereas in vb.net an id PRIMARY KEY seems necessary. I obtained the SQLlite library on vb from Finisar.

Using SQL browser there is an 'id' in vb.net but in android there is not one. So when trying to read the android db via vb.net is says there is an unsupported format.

I suppose therefore I need a similar primary key column one in my Android DB

Can someone explain this to me please

 

[mangojack]

What tutorial were you reading ??

If you haven't already , this might be worth viewing ..

SQLite two simple SQlite projects ...
both projects create dbs with a field ID Primary Key.

example ..

SQL1.ExecNonQuery("CREATE TABLE table1 (ID INTEGER PRIMARY KEY, col2 TEXT , col3 TEXT, col4 INTEGER)")

Cheers mj

 

[LucaMs]

Perhaps this might be useful to you too (not only Autoincrement):

http://www.sqlite.org/autoinc.html

 

[raphipps2002]

Thanks very much Mango and Luca...that is a really helpful site.

It seems that the ROWID is the norm rather than exception, so why would the B4J tutorial omit this? Just a query not a critisism.

SO, in order to write code to create a relational DB would I be correct in saying that If Table1, being customers, and contains 1) ROWID (2) Customer account number (3) Name

would the protocol be to have a relationship with a Table2 containing Invoices for example (Columns being 1) ROWID etc)...then is the relationship with the ROWID in Table1 which is Unique or as in my example the Customer Account code which is unique?

What tutorial were you reading ??

If you haven't already , this might be worth viewing ..

SQLite two simple SQlite projects ...
both projects create dbs with a field ID Primary Key.

example ..

SQL1.ExecNonQuery("CREATE TABLE table1 (ID INTEGER PRIMARY KEY, col2 TEXT , col3 TEXT, col4 INTEGER)")

Cheers mj

 

[LucaMs]

ROWID in Customers is not automatic related to ROWID in Invoices.

As you wrote, ROWID is ever present (with AutoIncrement or not).

I think that the best way is (classic):

Table Customers:
ID (it replaces CustomerAccount) as Primary key (PK)
Name
...

Table Invoices:
ID (PK)
CustomerID as Foreign Key (FK) related to Customers.ID
Date
...

Table InvDetails:
ID (PK)
InvoiceID (FK to Invoices.ID)
...


I mean, I do not hold account of the ROWID, even though, unfortunately, SQLite instead uses and indexes it