Greg,
Your suggestion works perfectly! Here is how I modified the code:
try
{
sqlCmd.Connection.Open();
sqlCmd.ExecuteNonQuery();
}
catch (Exception objException)
{
...
}
finally
{
sqlCmd.Connection.Close();
}
int nReturnCode = (int)sqlCmd.Parameters["@R_RETURN_CODE"].Value;
Thanks again!
Scott Natwick
"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
news:...
> Yes you can get the return value from sproc.
>
> sqlCmd.Parameters.Add(New SqlParameter("RETURN_VALUE",
> SqlDbType.Int)).Direction = ParameterDirection.ReturnValue
>
> The name of the parameter is not critacal (notice I am not even using the
> @ character).
>
> Pretty sure you cannot read the value back (any parameters for that
> matter) until the datareader is closed!
>
> Dim i as integer = CType(sqlCmd.Parameters("RETURN_VALUE").Value, Integer)
>
> Looking at your sproc, you don't even want to use a datareader here. Try
> this instead:
>
> Dim cn As SqlConnection = New
> SqlConnection(ConfigurationSettings.AppSettings("C onnectionString"))
> Dim cmd As SqlCommand = New SqlCommand("dbo.usp_SomeSproc", cn)
> cmd.CommandType = CommandType.StoredProcedure
>
> cmd.Parameters.Add(New SqlParameter("@SomeID",
> SqlDbType.Int)).Value = someID
> cmd.Parameters.Add(New SqlParameter("RETURN_VALUE",
> SqlDbType.Int)).Direction = ParameterDirection.ReturnValue
>
> Try
> cn.Open()
> cmd.ExecuteNonQuery()
> Catch ex As SqlException
> Throw New Exception("some error message", ex)
> Finally
> cn.Close()
> End Try
>
> Dim SomeReturnValue As Integer =
> CType(cmd.Parameters("RETURN_VALUE").Value, Integer)
>
> HTH,
> Greg
>
> "Scott Natwick" <> wrote in message
> news:dPSdndSde6mbm8LcRVn-...
>> Hi,
>>
>> Is there a way to obtain the return code from a stored procedure?
>>
>> Here is what I have so far. The procedure executes, but I'm not able to
>> find the return code from the procedure.
>>
>> Thanks,
>> Scott
>>
>> SqlConnection sqlConn = new SqlConnection(strDbConn);
>> SqlCommand sqlCmd = new SqlCommand("usp_proc_name", sqlConn);
>> sqlCmd.CommandType = CommandType.StoredProcedure;
>>
>> SqlParameter param1 = new SqlParameter("@PARAM_1", SqlDbType.VarChar,
>> 100);
>> param1.Value = field1.Text;
>> sqlCmd.Parameters.Add(param1);
>>
>> sqlCmd.Connection.Open();
>> SqlDataReader sqlReader =
>> sqlCmd.ExecuteReader(CommandBehavior.CloseConnecti on);
>> if (sqlReader.Read())
>> {
>> if (sqlReader.GetInt32(0) == 0)
>> {
>> // Procedure successul (expected)
>> }
>> else
>> {
>> // Procedure error occured (expected)
>> }
>> }
>> else
>> {
>> // Procedure error occured (expected)
>> ** The debugger always lands here as I'm not doing something right **
>> }
>>
>> CREATE PROCEDURE [dbo].[usp_proc_name]
>> @PARAM_1 varchar(100)
>> AS
>>
>> INSERT INTO [dbo].[table]
>> (
>> ...
>> )
>> VALUES
>> (
>> ...
>> )
>>
>> SELECT @error_num = @@ERROR
>>
>> RETURN @error_num
>> GO
>>
>>
>>
>
>
|
Similar Threads
