MSSQL Return Identity after Insert
Snippet Menu
2
Thu. Oct. 5th, 2006 8:48 PM
Often when inserting information in a database we need to know the value of the autonumber/identity field so that we can insert related data in another table. MSSQL Server provides a SCOPE_IDENTITY() function for just such a purpose (PHP/MySQL users have the mysql_insert_id() function -- lucky).
*This is in the generic section because there is no MSSQL Server or Database section.
*This is in the generic section because there is no MSSQL Server or Database section.
/* -- The following snippet writes the value of the latest INSERT statement's identity field to the browser -- */
int nInsertId;
String sSql = "INSERT INTO my_table (my_field) VALUES (@Value); SELECT SCOPE_IDENTITY();";
SqlConnection oConn = new SqlConnection(sConnString);
SqlCommand oCommand = new SqlCommand(sSql, oConn);
oCommand.Parameters.Add("@Value", SqlDbType.VarChar, 255).Value = sMyValue;
try {
oConn.Open();
nInsertId = Convert.ToInt32(oCommand.ExecuteScalar());
} catch (Exception exc) {
// Handle error
} finally {
if (oConn.State == ConnectionState.Open) {
oConn.Close();
}
}
Response.Write(nInsertId.ToString());
int nInsertId;
String sSql = "INSERT INTO my_table (my_field) VALUES (@Value); SELECT SCOPE_IDENTITY();";
SqlConnection oConn = new SqlConnection(sConnString);
SqlCommand oCommand = new SqlCommand(sSql, oConn);
oCommand.Parameters.Add("@Value", SqlDbType.VarChar, 255).Value = sMyValue;
try {
oConn.Open();
nInsertId = Convert.ToInt32(oCommand.ExecuteScalar());
} catch (Exception exc) {
// Handle error
} finally {
if (oConn.State == ConnectionState.Open) {
oConn.Close();
}
}
Response.Write(nInsertId.ToString());
' The following snippet writes the value of the latest INSERT statement's identity field to the browser
Dim nInsertId As Integer
Dim sSql As String = "INSERT INTO my_table (my_field) VALUES (@Value); SELECT SCOPE_IDENTITY();"
Dim oConn As New SqlConnection(sConnString)
Dim oCommand As New SqlCommand(sSql, oConn)
oCommand.Parameters.Add("@Value", SqlDbType.VarChar, 255).Value = sMyValue
Try
oConn.Open()
nInsertId = Convert.ToInt32(oCommand.ExecuteScalar())
Catch Exception exc
' Handle error
Finally
If oConn.State = ConnectionState.Open Then
oConn.Close()
End If
End Try
Response.Write(nInsertId.ToString())
Dim nInsertId As Integer
Dim sSql As String = "INSERT INTO my_table (my_field) VALUES (@Value); SELECT SCOPE_IDENTITY();"
Dim oConn As New SqlConnection(sConnString)
Dim oCommand As New SqlCommand(sSql, oConn)
oCommand.Parameters.Add("@Value", SqlDbType.VarChar, 255).Value = sMyValue
Try
oConn.Open()
nInsertId = Convert.ToInt32(oCommand.ExecuteScalar())
Catch Exception exc
' Handle error
Finally
If oConn.State = ConnectionState.Open Then
oConn.Close()
End If
End Try
Response.Write(nInsertId.ToString())
Comments
Voting
Votes Up
Votes Down
There are currently no comments for this snippet.