sqlite Last_Insert_Rowid() returns 0 always

Question

0.00/5 (No votes)

See more:

Hi..
Im having a problem with sqlite

SQL

Last_Insert_Rowid()

function..

Here's the code Ive tried..

C#

SQLiteConnection con = new SQLiteConnection(conString);
            con.Open();

            string cmdtxt = @"select Last_Insert_Rowid() from Customers";
//            string cmdtxt = @"insert into Customers(Name, TP, Address, Email, Notes, ModifiedTime)
//                                values ('asdasd', 'dgdfg', 'dfg', 'dfg', 'dfg', 'dfg')";


            SQLiteCommand cmd = con.CreateCommand();
            cmd.CommandText = cmdtxt;

            object obj = cmd.ExecuteScalar();
            Console.WriteLine(obj.ToString());

When I run this, it always display the 0 in cmd..
but when I run the same query in the sqliteAdmin IDE it displays the 23..
What's the wrong with my code ?

Posted 17-Oct-14 23:41pm

Mark

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2014-10-17T23:49:00

Solution 1

As the SQLite documentation[^] says:

The last_insert_rowid() function returns the ROWID of the last row insert from the database connection which invoked the function. The last_insert_rowid() SQL function is a wrapper around the sqlite3_last_insert_rowid() C/C++ interface function.

Which means in essence that it only works within the same Connection (And I'd only use it inside the same Command).
Once you open a new connection, you will always get 0 until you perform an insert on that connection.
Me? I'd do this:

SQL

INSERT INTO MyTable (Column1) VALUES(6); SELECT Last_Insert_Rowid();

as my SQLiteCommand...

Posted 17-Oct-14 23:49pm

OriginalGriff

Comments

Mark 18-Oct-14 5:52am

Oh.. Never knew about that.. By the way is there anyway to check what would be the next primary key of the table ? My primary key filed is auto increment integer.. How can I know what would be the next value ?
Cant do that with max value of the column. In case of user deleted a row, it would show a wrong value..
Thanks..

OriginalGriff 18-Oct-14 6:02am

No, not a good idea.
For the simple reason that you don't know which insert will be the "next" to generate a primary key. If multiple connections are possible (and your code allows for it by creating connections as needed, which is good), it's horribly easy to "assume" you will be the next because you are just about to create a row - but if a different task gets in first? You can end up with some very, very confused data!

If you are using automatic ids, then only ever get the new value immediately after creating the record. If you want an ID before you create it for whatever reason, then don't use automatic IDs, use GUIDs instead and assign them yourself from your C# code. (It's what I do all the time now: I hardly use Identity fields at all)