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]

)
AS
BEGIN
SET NOCOUNT ON

begin

end
SELECT
[CustomerID],
[CompanyName],
[ContactName],
[ContactTitle],
[Address],
[City],
[Region],
[PostalCode],
[Country],
[Phone],
[Fax]
FROM [Customers]
WHERE

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.Value = 0;

Param.Direction = ParameterDirection.Input;
Param.Value = "a";

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

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

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

.NET Development14

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.

'Specify input parameters

'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

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.Value = 0;

Param.Direction = ParameterDirection.Input;
Param.Value = "a";

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

com.ExecuteNonQuery();

MessageBox.Show(returnValue.ToString());

con.Close();

what happens now

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.