fedeteka fedeteka - 3 months ago 33
Vb.net Question

SQLite how to read the value of a field from the last ExecuteNonQuery() command

I need to manage a relation between 2 tables like:

Dim mainTable = "CREATE TABLE IF NOT EXISTS users (userID INTEGER PRIMARY KEY, name VARCHAR(20))"

Dim typeTable = "CREATE TABLE IF NOT EXISTS userType (type VARCHAR(20), user INTEGER, FOREIGN KEY(user) REFERENCES users (userID))"


Then I accept the data from the user from combobox for the "name" field and a listbox for the "type" field, so I can have something like:

Mike > Rock - Pop - Metal
Tom > Pop - Tecno


and so on...

Whith the next code I write the info on the users table

Dim connSave = New SQLiteConnection("Data Source=DataBaseUserType.sqlite;Version=3")
Try
Using (connSave)
connSave.Open()
Dim sql = "INSERT INTO users (name) VALUES (@paramName)"
Dim cmdSave As SQLiteCommand = New SQLiteCommand(sql, connSave)
'Tomando parĂ¡metros de https://www.devart.com/dotconnect/sqlite/docs/Devart.Data.SQLite~Devart.Data.SQLite.SQLiteCommand_members.html'
cmdSave.CommandText = sql
cmdSave.Connection = connSave
cmdSave.Parameters.AddWithValue("@paramName", cboName.Text)
cmdSave.ExecuteNonQuery()

End Using

Catch ex As Exception
MsgBox(ex.ToString())
End Try


But I need to know the value of the autogenated field userID so I can save the first record on typeTable something like:

48 - Rock (Where 48 is the userID of Mike)

Is there a way to read the userID value just after the cmdSave.ExecuteNonQuery()?
or do I need to open the connection again and read the last record to load the userID with something like
SELECT * FROM mainTable WHERE ROWID IN ( SELECT max( ROWID ) FROM mainTable)
??

Answer

Well, you don't need to open the connection again. According to docs you should call this function

last_insert_rowid()

so, just after the ExecuteNonQuery add these lines

    .....
    cmdSave.ExecuteNonQuery()
    cmdSave.Parameters.Clear()
    cmdSave.CommandText = "SELECT last_insert_rowid()"
    Dim lastID = cmdSave.ExecuteScalar()
    .....
End Using

In Sql Server there is the possibility to join the two commands in a single string separating them with a semicolon. I am not able to test it now on SQLite but you could try to run them together with

Dim sql = "INSERT INTO users (name) VALUES (@paramName);
           SELECT last_row_id()"

and still using ExecuteScalar to get the result of the Select

Comments