ADO.Net and Return values

I have been playing about with obtaining return values from a stored procedure. the sp looks like so
CREATE PROCEDURE [proc_CustomersLoadByPrimaryKey]
(
@CustomerID nchar(5)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Err int
if @CustomerID = 'a'
begin
set @err=99
return @err
end
SELECT
[CustomerID],
[CompanyName],
[ContactName],
[ContactTitle],
[Address],
[City],
[Region],
[PostalCode],
[Country],
[Phone],
[Fax]
FROM [Customers]
WHERE
([CustomerID] = @CustomerID)

SET @Err =74-- @@Error

RETURN @Err
END

my code to access this via C# looks like so.
private void button1_Click(object sender, EventArgs e)
{

SqlConnection con = new SqlConnection();
con.ConnectionString = connectionString
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "proc_CustomersLoadByPrimaryKey";

SqlParameter retParam = new SqlParameter();
retParam.Direction = ParameterDirection.ReturnValue;
retParam.SqlDbType = SqlDbType.Int;
retParam.ParameterName = "@ret";
retParam.Value = 0;

SqlParameter Param = new SqlParameter("@CustomerID", SqlDbType.NChar, 5);
Param.Direction = ParameterDirection.Input;
Param.Value = "a";

com.Parameters.Add(retParam);
com.Parameters.Add(Param);
con.Open();

SqlDataReader red;
red = com.ExecuteReader(CommandBehavior.CloseConnection);

int returnValue = (int)com.Parameters["@ret"].Value;
MessageBox.Show(returnValue.ToString());

}

Now if I pass 'a' into the sp I get the desired return code of 99 but if i pass any other value such as '"ALFKI" a vaild Client I still wish to see a return code this time of value 74 but I do not receive a return code anyone know what i'm doing wrong

Many thanks

Answer this question

ADO.Net and Return values

John123

VB6 and C# are totally different.

have you tried this

SqlConnection con = new SqlConnection();
con.ConnectionString = connectionString
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "proc_CustomersLoadByPrimaryKey";

SqlParameter retParam = new SqlParameter();
retParam.Direction = ParameterDirection.ReturnValue;
retParam.SqlDbType = SqlDbType.Int;
retParam.ParameterName = "@ret";
retParam.Value = 0;

SqlParameter Param = new SqlParameter("@CustomerID", SqlDbType.NChar, 5);
Param.Direction = ParameterDirection.Input;
Param.Value = "a";

com.Parameters.Add(retParam);
com.Parameters.Add(Param);
con.Open();

com.ExecuteNonQuery();

int returnValue = (int)com.Parameters["@ret"].Value;
MessageBox.Show(returnValue.ToString());

con.Close();

what happens now
arogan
The problem is indeed with how the command is executed. Output parameters and return values are sent by the server at the end of the batch results, so are not populated back into the SqlParameter objects until the reader is completely consumed or closed.
Woyler
Tried the above in VB.COm and it works as expected

[code]

Private Sub Command1_Click()
Dim Conn As ADODB.Connection
Dim oCm As ADODB.Command
Dim rec As ADODB.Recordset
Dim Params As ADODB.Parameters
Dim Param As ADODB.Parameter

Set Conn = New ADODB.Connection
Set oCm = New ADODB.Command

Dim strConnString As String
strConnString =strConnectionString

'Set connection properties and open
Conn.ConnectionString = strConnString
Conn.CursorLocation = adUseClient
Conn.Open

'Set command properties
Set oCm.ActiveConnection = Conn
oCm.CommandText = "proc_CustomersLoadByPrimaryKey"
oCm.CommandType = adCmdStoredProc
Set Params = oCm.Parameters

'Define stored procedure parameters and append to command.
Params.Append oCm.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
Params.Append oCm.CreateParameter("@CustomerID", adVarWChar, adParamInput, 5)

'Specify input parameters
Params("@CustomerID") = "a4"

'Execute the command
Set rec = oCm.Execute

MsgBox Params(0).Value

'Close connection
Conn.Close

End Sub
[/code]

Is there something wrong with C# or the .Net frame work
ADO.Net and Return values

Answer this question