Read one value from the last row of a SQL Server CE database

10,872

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();
Share:
10,872

Related videos on Youtube

Marco Cadei
Author by

Marco Cadei

Updated on July 17, 2022

Comments

  • Marco Cadei
    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 the ID 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) because ID 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 second ID = 1 etc.

    I need to know the last id because when I create a new customer I want to set his ID to previous customer ID+1

    Errors:

    Exception of type 'System.Data.SqlServerCe.SqlCeException' in System.Data.SqlServerCe.dll unhandled in user code

    • Sriram Sakthivel
      Sriram Sakthivel over 10 years
      Why not just use auto increment?
    • Sriram Sakthivel
      Sriram Sakthivel over 10 years
      I hope you need to refer GetInt32(5) if it is 6th column since column starts with 0 based index
    • Aron
      Aron over 10 years
      Your strategy would be thread unsafe, the auto increment is the way to go.
  • Marco Cadei
    Marco Cadei over 10 years
    Thanks a lot !! this worked perfectly ! Can you tell me how to use this autoincrement ? it seems a really better solution :)
  • Steve
    Steve over 10 years
    You 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
    Marco Cadei over 10 years
    Thanks you a lot ! i'm a newbie to sql :)
  • Steve
    Steve over 10 years
    Glad to be of help, and don't worry for the newbie thing. At the beggining we were all beginners. See you again on SO

Related