0

I've been using this tutorial to teach my self SQL Server Express using C#. This is pretty much all i know about databases, so when I search for examples or information to build on this project, I can't seem to make it fit with the end result of this tutorial.

For example, at the end of the tutorial, the author creates a primary key for the data table. I would like to be able to return the last key added. I found out that you need to use the select last_insert_id() function to do this, but how exactly is this done? I've read something about query and procedures, but I have no idé how to use them.

Could anyone explain to me how this is done? An example of getting the last created primary key for the example code in the tutorial would be greatly appreciated.

Thanks!

EDIT: Ops, I ment SELECT SCOPE_IDENTITY(), not select last_insert_id()

Q-bertsuit
  • 3,223
  • 6
  • 30
  • 55
  • My last comment on my answer to your previous question (very similar to this one) was not usefull? Using `Execute_Scalar` should help you to get back the value returned by `SCOPE_IDENTITY()`. Take a look here http://stackoverflow.com/questions/9675364/scope-identity-in-batched-sqlclient-commands and here http://stackoverflow.com/questions/8633821/scope-identity-always-returning-0 Those answers I think will be of great help – Yaroslav Aug 03 '12 at 08:54
  • Well, it was sort of useful, but I'm not getting it to be honest. Like I said, I know very little about this stuff, and I couldn't get the examples you posted to work with my code. Thank you for answering me though. Much appreciated! – Q-bertsuit Aug 03 '12 at 09:00

1 Answers1

1

You could use the Scope_Identity function along with the ExecuteScalar method on the command:

using (var conn = new SqlConnection("Server=.\SQLExpress;AttachDbFilename=|DataDirectory|Northwind.mdf;Database=Northwind;Trusted_Connection=Yes;"))
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText = 
    @"
        INSERT INTO Categories (CategoryName) Values (@CategoryName); 
        SELECT Scope_Identity()
    ";
    cmd.Parameters.AddWithValue("@CategoryName", "some category");
    int lastId = (int)cmd.ExecuteScalar();
}
Darin Dimitrov
  • 1,023,142
  • 271
  • 3,287
  • 2,928
  • Hi, and thanks for this. The column in mt database that contains the key is called "Identity". What should I change in your code to mach this column? Only "some category" ? Thanks! – Q-bertsuit Aug 03 '12 at 09:10
  • I'm getting this error An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll Additional information: Invalid object name 'Categories'. – Q-bertsuit Aug 03 '12 at 09:13
  • You probably don't have a table name called `Categories` and a column name called `CategoryName`. So replace your UPDATE statement to match your table structure. As far as the identity column is concerned, you don't need to specify it. The Scope_Identity function will automatically return the last primary key that was generated. – Darin Dimitrov Aug 03 '12 at 09:14
  • Sorry to be so completely helpless with this. My table name is Sessions and the column is Identity. Is this correct? cmd.CommandText = @" INSERT INTO Sessions (Identity) Values (@Identity); SELECT Scope_Identity() "; cmd.Parameters.AddWithValue("@Identity", "some category"); int lastId = (int)cmd.ExecuteScalar(); – Q-bertsuit Aug 03 '12 at 09:19
  • You should not insert in your identity column if this is the autoincrement primary key. It should not appear in your INSERT statement. In your INSERT statement should appear only columns that you want to set values to. – Darin Dimitrov Aug 03 '12 at 09:21
  • Ok, I dont want to insert any values, I just want to get the scope identity. Should I just remove the whole line " INSERT INTO Categories (CategoryName) Values (@CategoryName);" ? – Q-bertsuit Aug 03 '12 at 09:25
  • No, you should not remove the INSERT statement. It doesn't make sense to call Scope_Identity if you don't insert a record in your table first. When you insert a record, the database will generate a value for the autoincrement primary key column that you defined and return this value. – Darin Dimitrov Aug 03 '12 at 09:26
  • Ok, I think I understand. But I would like to return the scope identity of the last row that was created, WITH OUT creating a new row. Is this possible? – Q-bertsuit Aug 03 '12 at 09:30
  • This doesn't make sense. There's no such notion of *last row*. Only when you are inserting a record you could retrieve its autogenerated primary key inside the same database transaction. Remember that a database could potentially handle multiple inserts at the same time. Imagine 2 threads inserting records at exactly the same time. Which one is *the last* for you? – Darin Dimitrov Aug 03 '12 at 09:31
  • Ok, I'm having some trouble getting it to work. Is it possible to do this? DataRow[] returnedRow; returnedRow = sessions.Tables["Sessions"].Select("MAX(Identity)"); That should return the row that contains the maximum primary key, right? By the way, I don't think multiple treads will be a problem. I'm only going to access the database from one point. Thanks again! – Q-bertsuit Aug 03 '12 at 10:57
  • You could indeed use `SELECT MAX(Identity) FROM Sessions` to return the biggest primary key in the table. – Darin Dimitrov Aug 03 '12 at 12:26