How can i get the last id from the database in C#

Question

I have tried using the SELECT query to get the last id from the database, i.e. SELECT LAST_INSERT_id FROM studentdetails but it is not working for me.

I really need help!

Please what query can I use to get the last id from the database?

string me = dtba.ConnectionString();

SqlConnection connection = new SqlConnection(me);

string selectquery = "SELECT LAST_INSERT_id FROM studentdetails";
SqlCommand selectcmd = new SqlCommand(selectquery, connection);

selectcmd.Parameters.AddWithValue("@id", registrationNo.Text);

try
{
    connection.Open();

    SqlDataReader reader = selectcmd.ExecuteReader();

    if (reader.Read())
    {                    
        registrationNo.Text = reader["id"].ToString();
    }

    if (registrationNo.Text == "")
    {
        MessageBox.Show("SORRY ID HAS NOT BEEN READ");
    }
    else
    {
        MessageBox.Show("REGISTRATION NUMBER IS CORRECT");
    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}
finally
{
    connection.Close();
}

Check out this https://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide and https://stackoverflow.com/questions/10999396/how-do-i-use-an-insert-statements-output-clause-to-get-the-identity-value — Joe C, Dec 24 '19 at 13:47
related - don't use [addwithvalue](http://www.dbdelta.com/addwithvalue-is-evil/) — SMor, Dec 24 '19 at 19:01

Alexander van Oostenrijk · Accepted Answer · 2019-12-24T13:52:05.500

3

MySQL

It's:

SELECT LAST_INSERT_ID() AS id FROM studentdetails

rather than

SELECT LAST_INSERT_id FROM studentdetails

This is because LAST_INSERT_ID is a function, not a column name. This is assuming you are using MySQL.

SQL Server

If you are using SQL Server, use this:

SELECT SCOPE_IDENTITY() AS id FROM studentdetails

Please note that the SCOPE_IDENTITY() function can only be used right after an INSERT, and it will only work if the table that you've inserted a row into has an IDENTITY (auto-incrementing) column.

edited Dec 24 '19 at 13:52

answered Dec 24 '19 at 13:13

Alexander van Oostenrijk

4,644
3
23
37

its kind of working, but it is showing an exception: "'LAST_INSERT_ID' is ot a recongnised built-in function name." – E_A Dec 24 '19 at 13:22
Is this actually MySQL? – Alexander van Oostenrijk Dec 24 '19 at 13:25
1

SQL Server, that is? – Alexander van Oostenrijk Dec 24 '19 at 13:27
I have amended my answer for you. – Alexander van Oostenrijk Dec 24 '19 at 13:29
1

"Sql" is an ambiguous term. Please refer to the products as "MySql" or "Sql Server" or less ambiguous "Microsoft Sql Server". Using un-ambiguous terms will help your path to development tremendously. – granadaCoder Dec 24 '19 at 13:37
@SMARTG.: the `SCOPE_IDENTITY()` function can only be used *right after* an `INSERT`, and it will **only** work if your table that you've inserted a row into has an `IDENTITY` (auto-incrementing) column – marc_s Dec 24 '19 at 13:46
1

The obseration by @marc_s is very important to I'm copying that into the answer. – Alexander van Oostenrijk Dec 24 '19 at 13:52
Just a slight correction; you do not want FROM anything with SCOPE_IDENTITY() or LAST_INSERT_ID(). Neither care what table you are specifying; including FROM just gets you a result set with the same number over and over again, as many times are there are rows in the table specified. – Uueerdo Dec 24 '19 at 17:21
@SMARTG. SCOPE_IDENTITY() and last_insert_id() are specifically for retrieving the identity/auto-increment id of a row code has just inserted; for "any table" MSSQL has `IDENT_CURRENT('table_name')`. – Uueerdo Dec 24 '19 at 17:25

How can i get the last id from the database in C#

1 Answers1