How to get the autoincremented ID inserted row using SQLiteCommand object

Question

I have a SQLite Connection Object and a command object. I insert a row in my table using the ExecuteNonQuery function. How do I get the value of the autoincrement column (ID) from this?

Code for creating database:

creationQuery = "CREATE TABLE IF NOT EXISTS MyTable ( ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,MyCol1 NVARCHAR, MyCol2 NVARCHAR)";

My code for inserting values in the DB:


public void InsertIntoDB(string[] vals){

    // Global connection objects (This is in an API so every time a new instance of these are created)
    connObj = CreateConnection();
    cmdObj = connObj.CreateCommand();

    cmdObj.CommandText = "INSERT INTO MyTable ('MyCol1',MyCol2) VALUES( '" + vals[0] + "','" + vals[1] + "')";

    int id = -1;
    try{

        cmdObj.ExecuteNonQuery(); 
        id = (int)cmdObj.Parameters["id"].Value; // tried "@id" as well


    }catch(Exception ex){
        throw ex;
    } 
}

This code is inserting correctly. But throws an exception ( System.ArgumentOutOfRangeException) in the line where I'm trying to get the ID. Whats going on/ How do i solve this?

EDIT 1: Inside the try block, I added code to just run another query "Select max(ID) from MyTable":

    try
            {
                cmdObj.ExecuteNonQuery();

                cmdObj.CommandText = "Select Max(id) from MyTable";
                SQLiteDataReader myReader = cmdObj.ExecuteReader();
                while (myReader.Read())
                {
                    id = (int)myReader["id"];
                }

                Console.WriteLine(id);
            }

This code throws the same Exception.

That will involve running another query to do get the ID. Cant it be done with the same obejct? — Abhijeet Sridhar, Oct 29 '19 at 04:34
if you do this after insert, use 'SELECT last_insert_rowid() ' — Arphile, Oct 29 '19 at 04:38
@Arphile u mean do ExecuteNonQuery() this first, then insert data? where will i get the ID then? — Abhijeet Sridhar, Oct 29 '19 at 04:38
insert query doesn't returns row id. it just returns how many action it does. if you use update, it'll returns 2. insert will returns 1 — Arphile, Oct 29 '19 at 04:39
because of this, you need to view the table. so that's the reason why you need to use select query for your task. — Arphile, Oct 29 '19 at 04:40
before call the Autoincrements, you'll get which id will have if insert. after call the max value of row_num(id), you'll get which id already have. — Arphile, Oct 29 '19 at 04:41
if you wish to increase accuracy, use where clause with values which you inserted. — Arphile, Oct 29 '19 at 04:42
Use parameters for vals before query to prevent sql injection, please. — , Oct 29 '19 at 04:50
Uodate- I tried @Arphile first idea to get the max ID. I cant seem to ge that working. will update my try in the question — Abhijeet Sridhar, Oct 29 '19 at 05:09
Is this not the same question as: https://stackoverflow.com/questions/4341178/getting-the-last-insert-id-with-sqlite-net-in-c-sharp — Ernie Thomason, Oct 29 '19 at 05:22
please use Console.WriteLine(ex.Message); / Console.WriteLine(ex.StackTrace); to print out which causes error and post it — Arphile, Oct 29 '19 at 05:25
https://stackoverflow.com/a/35324308/7149109 This seemed to do the trick. Actually @ErnieThomason was right. This was the same question. Should I delete this question? — Abhijeet Sridhar, Oct 29 '19 at 05:32

score 1 · Accepted Answer · answered Oct 29 '19 at 08:27

1

select last_insert_rowid();

And you will need to execute it as a scalar query.

string sql = @"select last_insert_rowid()";
long lastId = (long)command.ExecuteScalar(sql); // Need to type-cast since `ExecuteScalar` returns an object.

answered Oct 29 '19 at 08:27

Jimmy

864
13
24

How to get the autoincremented ID inserted row using SQLiteCommand object

1 Answers1