Get autonumber from new SQL INSERT 1

[mrgulic]

I have the following code to create a new record in SQL but I don't know how to get the autonumber for the new record.

SqlCommand cmd = new SqlCommand("INSERT INTO device (" +
    "device_name,device_serial_num,device_created,device_created_by)" +
    " VALUES (@name, @serialnum, @created, @createdby)", con);
cmd.Parameters.Add("@name", SqlDbType.NChar, 15).Value = txt_deviceName.Text.ToString();
cmd.Parameters.Add("@serialnum", SqlDbType.NChar, 10).Value = txt_deviceSerialNumber.Text.ToString();
cmd.Parameters.Add("@created", SqlDbType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("@createdby", SqlDbType.NChar, 10).Value = strUser;
cmd.ExecuteNonQuery();


GetDeviceDetails( {put newly created ID here} );

I have searched google and here for hours and have tried many different ways but nothing seems to be a good fit for the way my code is.

Please, I don't want to rewrite my code to use a procedure. I just want to get it from what i have, otherwise the only other thing i can think of is to execute another query WHERE device serial number and name = the new record. I havent done that yet case that seems so inefficient.

Thanks in advance for your replies.

 

[phinoppix]

normally you get the new identity value (the autonumber thing) via the SCOPE_IDENTITY() or @@IDENTITY function, depending on your SQLServer version.

try adding "SELECT SCOPE_IDENTITY()" at the end of the insert statement. Then use the cmd.ExecuteScalar() to get the integer ID.

Or, you can do a simple SELECT query to get the id of that record based on the table's other unique column(s).

 

[mrgulic]

Thanks phinoppix.

Here are the modifications i made based on your input. Hopefully it will help someone else too.

SqlCommand cmd = new SqlCommand("INSERT INTO device (" +
"device_name,device_serial_num,device_created,device_created_by,device_details)" +
" VALUES (@name, @serialnum, @created, @createdby, @details) SELECT device_id = SCOPE_IDENTITY()", con);

cmd.Parameters.Add("@name", SqlDbType.NChar, 15).Value = txt_deviceName.Text.ToString().Trim();
cmd.Parameters.Add("@serialnum", SqlDbType.NChar, 10).Value = txt_deviceSerialNumber.Text.ToString().Trim();
cmd.Parameters.Add("@created", SqlDbType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("@createdby", SqlDbType.NChar, 20).Value = strUser;
cmd.Parameters.Add("@details", SqlDbType.NChar, 200).Value = "";

GetDeviceDetails(cmd.ExecuteScalar().ToString()); //add new record, return new ID, pass to method to display it on page
GetDevices(); //refreshes the dropdownlist
break;