CODEWITHSUNDEEP
vb.netsqlite
fedeteka
fedeteka

Reputation: 963

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) ??

Upvotes: 0

Views: 266

Answers (1)

Steve
Steve

Reputation: 216302

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

Upvotes: 1

Related Questions