Returning Identifier on a database Insert command

R

Russ

How can I return the indentifier column that is autogenerated by SQL Server
when a new record is created?

I have a form where the top half is used to enter a company name, address,
and contact information, the bottom half is used to add additional business
locations. In order to save additional locations I need the unique
identifier that was created when the top half of my form is saved so that
the relationships can be preserved.

Perhaps this is a question better suited for the sql programming newsgroup,
but I am hoping someone here can help me.
 
S

Stephan Bour

How can I return the indentifier column that is autogenerated by SQL Server
when a new record is created?

I have a form where the top half is used to enter a company name, address,
and contact information, the bottom half is used to add additional business
locations. In order to save additional locations I need the unique
identifier that was created when the top half of my form is saved so that
the relationships can be preserved.

Perhaps this is a question better suited for the sql programming newsgroup,
but I am hoping someone here can help me.

Here is one possible way:

private void SQLEntry () {
SqlCommand sqlCmd = new SqlCommand ( );
sqlCmd.Connection = new SqlConnection ("server =
'XXX';Database = 'Test'; Trusted_Connection=Yes");

sqlCmd.CommandText = "INSERT INTO Test (Firstname, Lastname)" +
"SET @Id = @@Identity"; <-- THAT'S THE KEY PART

//Create a parameter called Id with value set to nothing
sqlCmd.Parameters.Add ("@Id", SqlDbType.Int);
sqlCmd.Parameters ["@Id"].Direction = ParameterDirection.Output;

sqlCmd.Connection.Open ( );
sqlCmd.ExecuteNonQuery ( );

Stephan.
 
M

Morgan

You should use "select scope_identity()" if you're using SQL Server 2000
because if the table has any triggers it could return the wrong value. If
the trigger performs an insert into another table then @@Identity will
return the value for the other table, not the table you just inserted into.

HTH,

Morgan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top