(my original code has 33 Items) I simplified my Sqlite INSERT INTO code. It writes the new record into the database table Ok to the last available space after 4 records. The iRowID = db.LastRowID returns 0 when it should 5. It did return 5 once now it’s not. Is this the correct way to get the last record number for iRowID?
Dim sql As String
Dim iRowID As Integer
sql = "INSERT INTO Table1(ID, Item1, Item2) VALUES(?, ?, ?)"
Dim ps As SQLitePreparedStatement = db.Prepare(sql)
iRowID = db.LastRowID // Returns 0 (?) should be 5 <--
ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER) 'ID
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT) 'Item1
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT) 'Item2
ps.Bind(0, iRowID)
ps.Bind(1, Item1.Text)
ps.Bind(2, Item2.Text)
ps.SQLExecute()
// CHECK FOR DATABASE ERROR
If db.Error Then
MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
Return
Else
MsgBox("Fingering Sucessfully Save To Database")
End If
You should collect the LastRowID right after the insertion. Probably other commands has reset it to zero, or you never inserted anything after the connection and it stills 0.
The Row ID is either determined automatically by SQLite, when no column is defined as a primary key, or by the value of the primary key. Don’t expect it to be a specific value.
The use for .LastRowID is to be able to access the last row added right after it was added. Your code reads this value before inserting a new record.
If your DB definition has AUTOINCREMENT being set on the ID (PK), you could just omit such column on the INSERT and the DB will set one for you. And consulting the LastRowID AFTER the insertion you could known what value was used.
Yes It write the record but in the ID Column it put 0 when it should be 5. I stepped through it with the debug and (db.LastRowID) return 0 to iRowID. Is this the correct way to get the last record number?
If by " last record number" you mean the highest ID value, you should read a “SELECT MAX(ID) AS MAXID FROM TABLE”. But such thing usually is not recommended.
The ID should never be set by a user, never. In your screenshot, there are two records with the same ID value, how did you get that ? Each record has to be uniquely identified. So it is best to rely on a feature that will do it for you. The ID does not need to have a meaning to the user, it’s just some kind of S/N number. More than often, a column defined as a primary key with autonumbering is used, SQLite offers a column that is used for the purpose.
AI keeps the numbering increasing without reuse (that could potentially break things).
If you don’t delete records, IN SQLITE, that can be enough, because SQLITE has a “dumb” kind of always on autoincrement that looks for the highest ID PK value and adds one. Explicit AI maintains a counter separated that survives to deletes and vacuums.
@Jeffery_Cox
are you using older xojo that u use this SQLitePreparedStatement ?
see tims answer.
i remember the latest xojo updated sqlite class so it can used returning id in query statement.
and instead of ExecuteSQL use SelectSQL because it returns a row.
Yes, I’m using an older version for now. I have to make the time to update all the syntax changes for the newer version. Thank you for the information. The answer to my question has been solved too.
