Read one value from the last row of a SQL Server CE database
Given the fact that probably there is no problem with concurrency you could simply get the last ID with an ExecuteScalar call
string query ="SELECT MAX(ID) FROM Customer";
SqlCeCommand comSelect = new SqlCeCommand(query, connection);
int ID = (int)comSelect.ExecuteScalar();
ExecuteScalar returns the first column of the first row in the result set.
The query SELECT MAX(ID)
returns just one row with only one column. So ExecuteScalar fits perfectly in this scenario. No need to use an ExecuteReader
However, the correct way to handle your autoincrement scenario is to use an IDENTITY column that will automatically set the next value for you when you insert a new record. In this situation you have marked the ID column with the IDENTITY property to TRUE and then you insert your data in the table Customer without passing any value for the ID column. After the insert you retrieve immediately the value assigned by the database to your ID column
Pseudocode
string query ="INSERT INTO Customers (Name, Address, .....) VALUES (.....)";
SqlCeCommand comInsert = new SqlCeCommand(query, connection);
comInsert.ExecuteNonQuery();
query ="SELECT @@IDENTITY";
SqlCeCommand comSelect = new SqlCeCommand(query, connection);
int ID = (int)comSelect.ExecuteScalar();
Related videos on Youtube
Marco Cadei
Updated on July 17, 2022Comments
-
Marco Cadei over 1 year
I have a database with some columns and one of that is called
ID
(datatype =int
).
I need to know the value of theID
of the last row (for last row I mean the last created).I tried this, but it gives a runtime error:
string query = "SELECT * FROM Customer WHERE ID = (SELECT MAX(ID) FROM Customer)"; SqlCeCommand comSelect = new SqlCeCommand(query, connection); SqlCeDataReader rdr = comSelect.ExecuteReader(); int ID = rdr.GetInt32(6);
(
GetInt32(6)
becauseID
is the 6th column)Thanks all, and sorry for my english.
P.S.
Customer is my table and ID are set in ascending order. The first row created has
ID = 0
the secondID = 1
etc.I need to know the last id because when I create a new customer I want to set his
ID
to previous customerID+1
Errors:
Exception of type 'System.Data.SqlServerCe.SqlCeException' in System.Data.SqlServerCe.dll unhandled in user code
-
Sriram Sakthivel over 10 yearsWhy not just use auto increment?
-
Sriram Sakthivel over 10 yearsI hope you need to refer
GetInt32(5)
if it is 6th column since column starts with0
based index -
Aron over 10 yearsYour strategy would be thread unsafe, the auto increment is the way to go.
-
-
Marco Cadei over 10 yearsThanks a lot !! this worked perfectly ! Can you tell me how to use this autoincrement ? it seems a really better solution :)
-
Steve over 10 yearsYou set the IDENTITY property to your column and let the database calculate the next number when you insert the record. Remember that you don't need anymore to pass a value for this field. After the insert you could retrieve the last value inserted using the query SELECT @@IDENTITY
-
Marco Cadei over 10 yearsThanks you a lot ! i'm a newbie to sql :)
-
Steve over 10 yearsGlad to be of help, and don't worry for the newbie thing. At the beggining we were all beginners. See you again on SO