I am using a stored procedure to insert a record in SQL Server. The primary key is created at the time of the insert. Can I return the key if the insert succeeds? The initial insert creates a record in an author table. Once that is done I want to redirect the user to a page where they can start inserting rows for the books by that author. I need the primary key for the new author row to create the publications row. I am using C#. Code for the insert:
SqlConnection objConn = null;
SqlCommand objCmd = null;
strDBConn = ConfigurationSettings.AppSettings["ConnectionString"];
objConn = new SqlConnection( strDBConn );
objCmd = new SqlCommand( );
objCmd.Connection = objConn;
DataSet objDataSet = new DataSet( );
SqlDataAdapter objAdptr = new SqlDataAdapter( );
objCmd.CommandText = "p_insertAuthor";
objCmd.CommandType = CommandType.StoredProcedure;
SqlParameter objParam;
objParam = objCmd.Parameters.Add("@name",sAuthor );
objParam = objCmd.Parameters.Add("@desc", sDesc);
objParam = objCmd.Parameters.Add("@country", sCountry);
if( objConn.State != ConnectionState.Open){
objConn.Open( );
}
objAdptr.SelectCommand = objCmd;
SqlDataReader objReader;
try{
objReader = objCmd.ExecuteReader();
objReader.Close( );
objConn.Close( );
}
catch (SqlException objError)
{
objConn.Close( );
Response.Write(objError.Message);
}