return values from stored procedure
-
how can i return 0 (false) for an error to insert data in SQL database. i am using ASP.Net (using vb.net) to call the stored procedure.... my procedure is... CREATE PROCEDURE Insert_Book ( @arg_Title VARCHAR(255), @arg_Price VARCHAR, @arg_PublishDate DATETIME, @arg_BookID INT OUT) AS DECLARE @rc Int BEGIN INSERT INTO Book ( Title, Price, PublishDate ) VALUES ( @arg_Title, @arg_Price, @arg_PublishDate ); /*SELECT @arg_BookID = @@IDENTITY;*/ IF @@ERROR <> 0 BEGIN /*SELECT @rc = 0*/ /*Print convert(Varchar,"An error occurred while adding the new Book information")*/ RETURN (99) END ELSE BEGIN /*SELECT @rc =99*/ /*PRINT Convert(Varchar,"The new author information has been loaded")*/ RETURN (0) END procedure call is... con = New clarion.connection Conn = New SqlClient.SqlConnection Conn.ConnectionString = con.GetConnectionString() Dim cmdtxt As SqlClient.SqlParameter Dim objErr As SqlClient.SqlException Dim txtAbbreviation As String Dim txtReturnCode As String Conn.Open() Dim cmd As New SqlClient.SqlCommand("Insert_Book", Conn) cmd.CommandType = CommandType.StoredProcedure cmdtxt = cmd.Parameters.Add("@arg_Title", "Nisha") cmdtxt.Direction = ParameterDirection.Input cmdtxt = cmd.Parameters.Add("@arg_Price", "") cmdtxt.Direction = ParameterDirection.Input cmdtxt = cmd.Parameters.Add("@arg_PublishDate", "01/05/06") cmdtxt.Direction = ParameterDirection.Input cmdtxt = cmd.Parameters.Add("@arg_BookID", "") cmdtxt.Direction = ParameterDirection.Input cmdtxt = cmd.Parameters.Add("@@Error", SqlDbType.Int) cmdtxt.Direction = ParameterDirection.ReturnValue cmd.ExecuteNonQuery() Conn.Close() If Convert.ToInt32(cmd.Parameters.Item("@@error").Value) <> 0 Then txtAbbreviation = Convert.ToString(cmd.Parameters.Item("RC").Value) txtReturnCode = Convert.ToString(cmd.Parameters.Item("@@error").Value) Else txtAbbreviation = Convert.ToString(cmd.Parameters.Item("@arg_Price").Value) txtReturnCode = Convert.ToString(cmd.Parameters.Item("@@error").Value) End If Dinesh Sharma Software Engineer
-
how can i return 0 (false) for an error to insert data in SQL database. i am using ASP.Net (using vb.net) to call the stored procedure.... my procedure is... CREATE PROCEDURE Insert_Book ( @arg_Title VARCHAR(255), @arg_Price VARCHAR, @arg_PublishDate DATETIME, @arg_BookID INT OUT) AS DECLARE @rc Int BEGIN INSERT INTO Book ( Title, Price, PublishDate ) VALUES ( @arg_Title, @arg_Price, @arg_PublishDate ); /*SELECT @arg_BookID = @@IDENTITY;*/ IF @@ERROR <> 0 BEGIN /*SELECT @rc = 0*/ /*Print convert(Varchar,"An error occurred while adding the new Book information")*/ RETURN (99) END ELSE BEGIN /*SELECT @rc =99*/ /*PRINT Convert(Varchar,"The new author information has been loaded")*/ RETURN (0) END procedure call is... con = New clarion.connection Conn = New SqlClient.SqlConnection Conn.ConnectionString = con.GetConnectionString() Dim cmdtxt As SqlClient.SqlParameter Dim objErr As SqlClient.SqlException Dim txtAbbreviation As String Dim txtReturnCode As String Conn.Open() Dim cmd As New SqlClient.SqlCommand("Insert_Book", Conn) cmd.CommandType = CommandType.StoredProcedure cmdtxt = cmd.Parameters.Add("@arg_Title", "Nisha") cmdtxt.Direction = ParameterDirection.Input cmdtxt = cmd.Parameters.Add("@arg_Price", "") cmdtxt.Direction = ParameterDirection.Input cmdtxt = cmd.Parameters.Add("@arg_PublishDate", "01/05/06") cmdtxt.Direction = ParameterDirection.Input cmdtxt = cmd.Parameters.Add("@arg_BookID", "") cmdtxt.Direction = ParameterDirection.Input cmdtxt = cmd.Parameters.Add("@@Error", SqlDbType.Int) cmdtxt.Direction = ParameterDirection.ReturnValue cmd.ExecuteNonQuery() Conn.Close() If Convert.ToInt32(cmd.Parameters.Item("@@error").Value) <> 0 Then txtAbbreviation = Convert.ToString(cmd.Parameters.Item("RC").Value) txtReturnCode = Convert.ToString(cmd.Parameters.Item("@@error").Value) Else txtAbbreviation = Convert.ToString(cmd.Parameters.Item("@arg_Price").Value) txtReturnCode = Convert.ToString(cmd.Parameters.Item("@@error").Value) End If Dinesh Sharma Software Engineer
look up ExecuteScaler object retValue = [SqlCommand].ExecuteScalar() ;