@@identity

i have found loads of topics on this but have yet to find one that gives the answer i need.

I want to display the last insert id into a label/textbox after the INSERT function

how do i do this?

A question back at you -

Which database are you using?

|||

if the value you are using as an id is an idetity column and your database is sql server 2000 or 2005 then you can do something like this to get the last inserted value

strSQL = "";
strSQL += " Set Nocount on ";
strSQL += " Insert TableX(...) VALUES (...) ";
strSQL += " select IdentityInsert = IDENT_CURRENT( 'TableX' ) ";
strSQL += " set nocount off ";

hope this helps

|||

In sqlserver you can also do it with stored procedure:

CREATE procedure [dbo].[textbookop_insert])
as
begin
INSERT INTO [cosolu].[dbo].[cosolu_textbookop]
           ([textbookop_textbookid])
     VALUES
           (@.textbookop_textbookid)
return @.@.identity
end;
GO

You can insert the value using the following code:

 SqlConnection sqlconn = DB.CreateConn(); SqlCommand sqlcmd =new SqlCommand("textbookop_insert", sqlconn); sqlcmd.CommandType = CommandType.StoredProcedure; SqlParameter sqlpar = sqlcmd.Parameters.Add("ReturnValue", SqlDbType.Int); sqlpar.Direction = ParameterDirection.ReturnValue; sqlcmd.Parameters.AddWithValue("@.textbookop_textbookid", textbookop_textbookid); sqlconn.Open(); sqlcmd.ExecuteNonQuery();int camid = Convert.ToInt32(sqlcmd.Parameters["ReturnValue"].Value); sqlconn.Close();

Hope this helps.|||i am using an sqldatasource, dont know if that changes anything. I dont know how the syntax works when the connection is built into an object|||im using sql server 2005|||

mike7510uk:

i am using an sqldatasource, dont know if that changes anything. I dont know how the syntax works when the connection is built into an object

If the connection is built into the object, you have a TableAdapter that is connected to the table you are using. This TableAdapter contains methods that pull data from the table, like Fill() and GetData(). You can easily add another method to this TableAdapter to get the last value that was generated for the identity column of its table.

You can doubleclick on the .xsd file for the sqlDataSource, which opens the DataSet Designer. Go to the table you want to work with (lets call it myTable) and right click on any existing method in there. From this menu, choose Add Query.

This will give you a handy-dandy TableAdapter Query Configuration Wizard. In this wizard:

1. When it says "Choose a Query Type", choose "SELECT which returns a single value".

2. When it says "Specify a SQL SELECT statement, type in

SELECTIDENT_CURRENT ('myTable')FROM myTable

3. When it says "Choose a Function Name", give it a name like "GetLastIdentityValue".

Voila, you're done. So now, in your page, lets say you want to grab the ID of the last record entered and disply it in a label (Label1).

int theID = (int)myTableAdapter.GetLastIdentityValue();Label1.Text = theID.ToString();

Notice there is a cast in the first line, because the value returned by IDENT_CURRENT is of type nullable Decimal.

Hope that helps.

|||

After you do an insert you can return the key for the insert like:

INSERT INTO [dbo].[table] (name, phone)VALUES ("Denny", "555-1234")Return@.@.IDENTITY

Also be sure to read on the differences of SCOPE_IDENTITY() and @.@.IDENTITY - to be sure which one you should use to return the correct id.

|||so after i set Return @.@.IDENTITY how would that display it into a label / textbox?|||

Lee Dumond:

mike7510uk:

i am using an sqldatasource, dont know if that changes anything. I dont know how the syntax works when the connection is built into an object

If the connection is built into the object, you have a TableAdapter that is connected to the table you are using. This TableAdapter contains methods that pull data from the table, like Fill() and GetData(). You can easily add another method to this TableAdapter to get the last value that was generated for the identity column of its table.

You can doubleclick on the .xsd file for the sqlDataSource, which opens the DataSet Designer. Go to the table you want to work with (lets call it myTable) and right click on any existing method in there. From this menu, choose Add Query.

This will give you a handy-dandy TableAdapter Query Configuration Wizard. In this wizard:

1. When it says "Choose a Query Type", choose "SELECT which returns a single value".

2. When it says "Specify a SQL SELECT statement, type in

SELECTIDENT_CURRENT ('myTable')FROM myTable

3. When it says "Choose a Function Name", give it a name like "GetLastIdentityValue".

Voila, you're done. So now, in your page, lets say you want to grab the ID of the last record entered and disply it in a label (Label1).

int theID = (int)myTableAdapter.GetLastIdentityValue();Label1.Text = theID.ToString();

Notice there is a cast in the first line, because the value returned by IDENT_CURRENT is of type nullable Decimal.

Hope that helps.

where is this file? i dont have any xsd files in my folders!!

|||

Sorry, I thought you were using an ObjectDataSource. I should have read more closely. My bad. (With the OnjectDataSource, you can use a strongly-typed Dataset that contains TableAdapters.)

In the case of using an SqlDataSource, you'll can use one of the methods above to return the identity value with the insert command.

|||

Lee Dumond:

If the connection is built into the object, you have a TableAdapter that is connected to the table you are using. This TableAdapter contains methods that pull data from the table, like Fill() and GetData(). You can easily add another method to this TableAdapter to get the last value that was generated for the identity column of its table.

You can doubleclick on the .xsd file for the sqlDataSource, which opens the DataSet Designer. Go to the table you want to work with (lets call it myTable) and right click on any existing method in there. From this menu, choose Add Query.

This will give you a handy-dandy TableAdapter Query Configuration Wizard. In this wizard:

1. When it says "Choose a Query Type", choose "SELECT which returns a single value".

2. When it says "Specify a SQL SELECT statement, type in

SELECTIDENT_CURRENT ('myTable')FROM myTable

3. When it says "Choose a Function Name", give it a name like "GetLastIdentityValue".

Voila, you're done. So now, in your page, lets say you want to grab the ID of the last record entered and disply it in a label (Label1).

int theID = (int)myTableAdapter.GetLastIdentityValue();Label1.Text = theID.ToString();

Notice there is a cast in the first line, because the value returned by IDENT_CURRENT is of type nullable Decimal.

Question: doesn't this risk getting a later insert's identity value? How can I get the scope_identity value through the table adapter like this but from the same database call that actually makes the insert (less risky)? Regardless, thanks for a great solution using table adapters!

|||

Yes, is which why you should call itimmediately, right after you call the Update method. As you correctly point out, it surely doesn't make sense to use it in any other manner.

Here is an example where it could come in handy. Say, for instance, you have two tables: Orders and OrderItems. The OrderItems table is going to contain an OrderID column that is a foreign key back to the Orders table (OrderID would be a primary key column in Orders).

When you place OrderItems in your dataset, you have no idea what value to put in that foreign key OrderID column, because the current order hasn't been saved yet. You have a classic chicken-and-egg situation. What to do?

Easy... when you create the OrderItems, just put in any dummy value you want (it is going to be replaced with the correct value in a minute).

When you've added all the items you want, the current order is finished and you'll want to save it. To do that, do an Update on the OrdersTableAdapter, then immediately call the GetLastIdentityValue() method you've set up:

ordersTableAdapter.Update(myDBDataSet.Orders);int orderId = (int) ordersTableAdapter.GetLastIdentityValue();

This gives you the number to put in that foreign key column of the OrderItems table. So, now you loop through the dataset of the OrderItems, set each OrderID value of each accordingly, then update that:

foreach (myDBDataSet.OrderItemsRow rowin myDBDataSet.OrderItems){ row.OrderID = orderId;}orderItemsTableAdapter.Update(myDBDataSet.OrderItems);

Voila! All the foreign keys match, anf the integrity of the database is maintained.

|||

i did this in the end by using mysql and puttin it into the code behind.

All works good,

cheers for all help

|||

That is exactly the answer I needed... all the other projects here had been done in Deklarit, and after an update the DataRow held the new identity value... not so, apparently, with the xsd dataset in studio. This is exactly what I needed. Thanks so much!

Thomas