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

Thanks James.  Your response did not provide a link or anything but knowing that I have to do this, 
I will go a Googling.

Thanks,

John W. Colby
www.ColbyConsulting.com


James Barash wrote:
> John:
> 
> You do, indeed, need to define a Parameter for the return value. From the Microsoft documentation: 
> 
> -----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
> 
> 
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
>