Android Question SQLite db with Primary key as an autoincrement

[Richard S]

Greetings again. I have another problem I do not seem to be able to solve. I am using a SQLite database with a table named Contacts. The first record in the table is defined as an integer, Primary Key, Autoincrement.
I know everyone is going to aske why I don't use the built in rowID but there are very good reasons that I do not want to do that. One being when SQLite gets to the rowID limit it uses old rowID's that are available. I cannot use them because I use my autoincrement as a record in subsequent tables.
Ok having said that, I have not been able to figure out how to add a new record in the Contacts table. I am using ExecNonQuery2 like the following code

        Private EMPTY As String = " "
        Private NOVALUE As String = ""     
Query = "INSERT INTO Contacts VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
            SQL1.ExecNonQuery2(Query, Array As String(NOVALUE, edtLastName.Text, edtFirstName.Text, EMPTY, edtStreet.Text, edtCity.Text, EMPTY, edtProvState.Text, edtCountry.Text, edtPhone.Text, EMPTY, EMPTY, EMPTY, EMPTY, EMPTY, EMPTY, EMPTY, EMPTY, False, EMPTY))
            ToastMessageShow("Contact added", True)
            ResultSet1.Close 'close the ResultSet, we don't it anymore

There are a total of 20 fields in the table so I use 20 ?'s
I have tried using both the EMPTY and NOVALUE variables as well as NULL in the Array but I still get a type mismatch error
I have also tried using only 19 ?'s and no entry in the Array for the first field but then I get a field count error.
Any help here will be appreciated. All the empty fields will be entered as an update later in the program.
Thank you all
Richard S
Ontario Canada

 

[Erel]

One being when SQLite gets to the rowID limit it uses old rowID's that are available

It will take some time until you reach the limit, which is: 9223372036854775807.

Two options:

1. List the columns before the values and skip the auto-increment column: https://www.w3schools.com/sql/sql_insert.asp
2. Change the relevant column question mark with Null.

 

[pliroforikos]

I think it is better to do this to put so many ?

Dim flds As Int = 20
Dim sql As String = "INSERT INTO <table name> VALUES("
For i = 1 To flds
   sql = sql & "?"
   If i < flds Then
      sql = sql & ","
   End If
Next
sql = sql & ")"

 

[Mahares]

I have tried using both the EMPTY and NOVALUE variables as well as NULL in the Array but I still get a type mismatch error

Since you are not inserting in all columns, you can shorten the query as Erel alluded to, by including only the relevant columns like this:

Dim Query As String
    Query = "INSERT INTO Contacts (LastName, FirstName, Street, City, State, Country, Phone) VALUES (?, ?, ?, ?, ?, ?, ?)"
    Dim myarr() As Object =Array(edtLastName.Text, edtFirstName.Text, edtStreet.Text, edtCity.Text, edtProvState.Text, edtCountry.Text, edtPhone.Text)
    SQL1.ExecNonQuery2(Query, myarr)

Use your column names of course. You can even make it a little easier to see the forest for the trees, by using smart string literals:

Query = $"INSERT INTO Contacts
 (LastName, FirstName, Street, City, State, Country, Phone) 
VALUES (?, ?, ?, ?, ?, ?, ?)"$

@pliroforikos solution offered to you is also a good one especially if you are filling all columns with data.
Also, remove this line: ResultSet1.Close When you insert records, there is no resultset or cursor involved.

 

[RB Smissaert]

Since you are not inserting in all columns, you can shorten the query as Erel alluded to, by including only the relevant columns like this:

Dim Query As String
    Query = "INSERT INTO Contacts (LastName, FirstName, Street, City, State, Country, Phone) VALUES (?, ?, ?, ?, ?, ?, ?)"
    Dim myarr() As Object =Array(edtLastName.Text, edtFirstName.Text, edtStreet.Text, edtCity.Text, edtProvState.Text, edtCountry.Text, edtPhone.Text)
    SQL1.ExecNonQuery2(Query, myarr)

Use your column names of course. You can even make it a little easier to see the forest for the trees, by using smart string literals:

Query = $"INSERT INTO Contacts
(LastName, FirstName, Street, City, State, Country, Phone)
VALUES (?, ?, ?, ?, ?, ?, ?)"$

@pliroforikos solution offered to you is also a good one especially if you are filling all columns with data.
Also, remove this line: ResultSet1.Close When you insert records, there is no resultset or cursor involved.

> Since you are not inserting in all columns, you <can> shorten the query

To avoid any confusion this should read:
As you can't insert into an autoincrement column, you have to omit the autoincrement column name from the insert SQL.

RBS

 

[RB Smissaert]

> Since you are not inserting in all columns, you <can> shorten the query

To avoid any confusion this should read:
As you can't insert into an autoincrement column, you have to omit the autoincrement column name from the insert SQL.

RBS

Actually (just checked this) and you can provided the value was not used before. If you pass null it should do a normal autoincrement value, so one higher than
the highest of the previously used values. You can pass a value lower than the highest of the previously used values, but the row will be inserted according to ranking
of the passed value, so if you had ID's 1,2,3 and you pass a zero it will appear at position zero.

RBS

 

[Geezer]

Can you show your table structure

 

[Mahares]

To avoid any confusion this should read:

Your post#5 adds more confusion, perhaps you should delete it in order not to further confuse everyone and the OP in particular.

 

[RB Smissaert]

Your post#5 adds more confusion, perhaps you should delete it in order not to further confuse everyone and the OP in particular.

Maybe, but in the OP's case I think he should not use the autoincrement column in the insert SQL.

RBS

 

[Richard S]

Thank you to all for the detailed responses. I guess I really am having a "Senior's Moment". When I create programs in MS Visual Basic.net I always list the table's fields as Erel is suggesting. In any B4X tutorial that I saw, the table's fields are not spelled out in the examples so I followed that logic. I know using the autoincrement is frowned upon by many but as I mentioned I use that field as a pointer to data in other tables. This project has a total of 6 tables and the Contacts table is the least complicated of them all. Geezer asked me to post the table structure so I have attached it as a file. Having said that I now have a pretty good idea of how to solve the issue thanks to all your responses.
Richard
Ontario Canada

 

[Mahares]

I think he should not use the autoincrement column in the insert SQL.

I did not include it in the code snippet in my post #4 either. You should read posts carefully before reacting to them.
Having said that, there are cases where you can put the 'id' in the insert query if you want to in one of these cases:
1. You use a lower number than the highest id number if that value is available because perhaps a record was deleted.
2. Or, you can enter an 'id' higher than the largest number if you know it.
The best way is to let the database handle it.

autoincrement is frowned upon by many

There is nothing wrong with using a column type: RECNO INTEGER PRIMARY KEY AUTOINCREMENT like you did. It is not bad practice.

 

[Richard S]

Thank you for your comments. I have created a string variable that holds all of the table field names. and use that variable in the insert query like this
Query = "INSERT INTO Contacts (" & Contact_Fields & ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
I actually copied and pasted that variable from the PC version of this application.
As an aside:
Many many years ago I bought a product called Palm Pilot. There were two parts to this product; The software and a hardware device that was small enough to fit in my shirt pocket. Eventually the hardware died and was no longer available to purchase. The original software had a Calendar, Contacts and Memo functions which I used extensively but without the hardware it was not as useful as it used to be.
Bottom line is I made a vb.net application to replace the Palm Pilot software but it was only available on my local server which I refuse to connect to the internet.
Many years have passed since I wrote PalmTwo and only last week did I find B4A. Once I found B4A I decided to reproduce PalmTwo on my android especially since B4A uses basic as the programming language. What I am learning is that B4A basic has some significant differences from VB.net but I am having fun. This app will only be on my wife's android and mine so will not be available Google Play.
Thanks again to all who are willing to help me learn a new hobby.
Richard

 

[Geezer]

The auto increment is perfectly fine. It's how I do all mine. Although I would use NULL instead of ""

The second last field you insert as a BOOLEAN and you have an INTEGER field. Try inserting a 1 for True or 0 for False

 

[Richard S]

You are correct Geezer I should have entered an integer but I guess the compiler converts the Boolean to an integer because after listing the field names as Erel suggested it is working the way I expected. I will change it to and integer though because I prefer to do things correctly!
Thanks for that.
Richard

 

[mangojack]

The auto increment is perfectly fine. It's how I do all mine. Although I would use NULL instead of ""

All my DB's Include Column .. ID INTEGER PRIMARY KEY AUTOINCREMENT . In all Inserts I pass NULL.
Never had any issues / problems

 

[KMatle]

Only NULL or a real value are correct:

NULL = Increases the last inserted ID by 1 for a new row automatically (as intended)
Real value = When you rollback a backup and need the "old" values as you have referenced this primary key in other tables like an order table with an item table which contains items belonging to a unique order identified by the primary key (e.g. order id which was auto incremented before).

 

[Albert Kallal]

Interesting enough, i just wrote an app in B4A that has its original roots from my Palm pilot oh so many years ago. (it was a time + billing applicaiton). I used the Palm for about 9 years. Then in 2000 I switched over to a windows phone. And used a web based app I wrote. When I stumbled upon B4A, I decided to re-write the application and now I am back to how "very" much the original Palm setup with a sync data option.

So, changes made on desktop part go to Android, and changes made android make their way up to the Desktop.

It works VERY well.

How did I do the sync? Well, I needed "some" service running on the desktop. There are more ways to do this then flavors of ice cream, but given that I am VERY good with MS-Access, and I am VERY good with SQL server? Well, that "service" i connect to on my desktop is a copy of SQLEXPRESS running. So, for the database front end I still use Access, but I simply migrated the data tables to SQL server. So, now, to sync data from Andriod, it simply DIRECT connects to the SQL server running on my desktop. This means I don't need a web site, don't need the cloud, and everything is 100% local.

I also built a data forms binder. So, now i can in B4A drop 10 or say even 15 controls onto a view. Then my code shuffles the data from the table to the form, and back again (I don't even have to generate the form (view) members - the code simply loops the controls in the view and does all of the sql updates for me. So, I can make a form like this - drag + drop - go crazy - takes 5 minutes:

Say this:

With 4 lines of code, it loads up and becomes this:

So, most intersting:
I wrote this to work like my Palm.
I wrote this to sync with my desktop.
I use sqllite on Android, but did use sql server on the laptop..

I am somewhat hesitant to share my "auto matic data binder" code. (the code that automatic fills out the forms I create in B4A, since it based on views and not the newer B4A pages.

However, if you are interested. I will post my data binder and sync code. (both sync code, and data binder code uses many of the same routines).

It not clear which database you are using for your desktop side of things. But, given the memory and ram on computers today, then running SQL express is not all that heavy of a application. And if you using vb.net to write stuff, you often will have setup + installed SQL express edition, or perhaps you use ms-access (JET/ACE) as the database.

But regardless, the sync routines would be of interest, and I am willing to post the routines. The sync system requires you to add ONE extra column to the sqlLite database (two columns if you have a PK + FK). You have to add the two columns to EACH table you have. This approach thus keeps the autonumbers 100% independent between desktop and Android. Each table also needs a LastUpdate column. I did in fact setup sqlIte triggers to always update the LastUpdate column. Interesting enough, next time i write such a sync routine, I will use a transaction table, but for my first "go" at a sqlLite sync routine to my desktop, what I did was "ok" of a design - not perfect, but the results were VERY good indeed. I had searched the internet for 10+ years looking for a approach to sync of data that would "mostly" seamless handle the autonumbers (PK) and foreign key (FK) issues when syncing data using surrogate keys (autonumbers). I was a post here in B4A and suggestion that finally cinched this deal. (a brilliant idea - one that was the result of 10 years of looking for a solution on the internet - and it was a simple idea).
Unfortantly, I can't remember the name of who suggested this idea - but it was a nice idea.

So, the data binder routines - no doubt you could use.
The sync routines? Well, they are only much of use if you wiling to run + adopt SQL EXPRESS on your desktop.

But the resulting setup does not need the cloud or any web connection to run.

I quite busy, but I can pull out the code I have for above to work - I think I can do that in less of 30 minutes of my time - so I will do so if you are interested and willing to run SQL express on your desktop. But then again, I not waste the 30 minutes of time to do this if you not interested in running SQL express here.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada

 

[Richard S]

Good morning Albert. First of all let me thank you for your generous offer. I would really appreciate anything you have to offer. As you may have read in previous posts I only started to learn B4A a little over a week ago and although it is "a form" of basic there is a lot to learn. My setup is a little different than yours so I will tell you what I have and you can let me know if you think it will work and if not what I will have to do to make it work. I really do not want to expose my data to a cloud or the internet in any way at all. If I had no choice I was going to omit one table that does have sensitive data but if we / I can make your system work it will be wonderful.
I have a QNAP NAS and store my local SQLite database on it (along with TB of other data). I was given this NAS about three years ago and find it great to share our data between our computers. Believe it or not I originally used MS SQL then changed to SQLite as it was easy to copy the database to my laptop when away on vacation. If I need to go back to MS SQL then I can do that. My program is resident on all our computers, My wife's PC, my PC, and my laptop and all connect to the NAS via the network.
If you think it better I can give you my email address and you can email me the code.
Thank you again. looking forward to your help/
Richard.
BTW if I didn't know better I would have thought you were sending a pic of my phone screen.

 

[Albert Kallal]

No, I'll post the sync and data binder routines here - I have to run. Also the data binder routines are for standard views like above, and I will eventually convert the routines to work with B4A pages. The adopting of SQL express kills two birds with one stone so to speak. If one does not run sqlexpress, then what server/system/software will you run on some desktop for the data sync to occur? So, as long as your computers, and your phones are on the same wi-fi network, then those phones can sync with sql server running.

I actually think the data binder routines i built are of the most use - they simply load up the view with one row, and takes care of the update of that form data to sqlIte. It just made no sense to have to "generate" 15+ controls, and then write a bucket of code to read from the database, push to the form, and then write code to do the reverse and update the database. So that is a 'general' setup I now have. It thus lets you concentrate on building forms - and the data base stuff is all done for you. And of course the sync routines seamless manage the PK/FK issues between sqlite and sql server.

I'll post here later today here (this evening).

R
Albert

 

[Richard S]

Thank you again Albert. The NAS server that I have has a built in SQL server. I have never turned it on as I have not had a need for it but I'm assuming it will do what needs to be done. If all else fails I will rewrite the PC software and use SQLExpress. I do have a legal copy of MS SQL that I used to use before I converted everything over to SQLite. I'm sure one or the other of these options will work. Looking forward to yet another challenge.
Richard