iRowID = db.LastRowID returns 0 when it's suppose to be 5

Hello

(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.

(Emphasis is mine.)

What do you mean exactly ?

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.

1 Like

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.

Yes Correct the highest ID value.

Why do you want to assign the ID instead of letting the db assign it?

1 Like

Database

So Your saying it’s done automatically and I leave out ID.

As Rick said:

Maybe you need to check how the db is created and use autoincrement.

1 Like

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.

So in my code example above. Omit ID completely (new code below) and it is done automatically.

sql = "INSERT INTO Table1(Item1, Item2) VALUES(?, ?)"

Dim ps As SQLitePreparedStatement = db.Prepare(sql)
              
  ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)   'Item1               
  ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)   'Item2 

  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

Thank You for your help. I’ll rewrite it and see if I can get it to work

Seems to me you could simplify matters anyway:

sql = "INSERT INTO Table1(Item1, Item2) VALUES(?, ?)"
db.ExecuteSQL (sql, Item1.Text, Item2.Text)

All done.

1 Like

ID PK Primary Key Auto Increment

That was it Thank You

PK + AI

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.

1 Like

Oh, that’s a bad implementation. @Jeffery_Cox , you have not check AI on top of PK.

@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.