Administrator

Administrator

  • Tech Writer
  • 2.2k
  • 1.5m

Stored Proc Return Value

Feb 27 2003 7:24 AM
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); }

Answers (3)