[dba-VB] C# - return value for stored procedures

John:

You do, indeed, need to define a Parameter for the return value. From the Microsoft documentation: 

SqlCommand.ExecuteNonQuery Method

Executes a Transact-SQL statement against the connection and returns the number of rows affected.

To add a Return Value parameter:

SqlParameter ret = new SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int); 
ret.Direction = ParameterDirection.ReturnValue; 
cmd.Parameters.Add(ret);

Hope this helps.

James Barash 

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Monday, November 30, 2009 12:45 PM
To: VBA
Subject: [dba-VB] C# - return value for stored procedures

While I am getting back values in my "output" parameters for the SPs, I am getting a constant -1 for the return value.

In the stored procedure I have tried just using

RETURN 1234

and also creating an int variable, setting that to 1234 and returning that.

declare @Ret int	
	select @ret = 1234
     	RETURN @ret

In all cases I get a -1 value which leads me to believe that the problem is on the C# side of things.

My c# code looks like:

try
{
	if (sCmd.Connection.State != ConnectionState.Open)
         	sCmd.Connection.Open();
	int retValue = sCmd.ExecuteNonQuery();
	return retValue;
}
catch (SqlException sqlEx)
{
	throw sqlEx;
}

I am not throwing an error, the SP executes, but retValue is always -1.

Any ideas?  Do I need to add a SqlParameter to the Parameters collection for the return value?

--
John W. Colby
www.ColbyConsulting.com
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com