Cannot get OUTPUT parameter after running Sql stored procedure

[MS]

Here's my simple stored procedure:

ALTER PROCEDURE GetMemberIDByEmail
@Email EmailAddress,
@ID int OUTPUT
AS
SELECT @ID = ID FROM tbl_Member WHERE Email=@Email
RETURN


Here's my C# code (using Microsoft Data


public int GetMemberIdByEmail(string email)
{
SqlParameter[] aryParams = new SqlParameter[2];

aryParams[0] = new SqlParameter("@Email", email);
aryParams[1] = new SqlParameter("@ID", SqlDbType.Int);
aryParams[1].Direction = ParameterDirection.Output;

try
{
SqlHelper.ExecuteNonQuery(_Connection, "GetMemberIDByEmail", aryParams);
}
catch (System.Data.SqlClient.SqlException)
{
return 0;
}

if (aryParams[1].Value == null)
{
Debug.WriteLine("Still NULL!"); <-- ALWAYS GET THIS!!
}
else
{
Debug.WriteLine("Got it!");
}

return 0;
}


The value of the output parameter is ALWAYS null! I've spent hours trying to
fix this but don't see what is wrong. The stored procedure works great in
Query Analyzer. Can anyone point out what I'm missing? Thanks!

Can anyone

 

[Jim Hughes]

Add a Trace.Writeline(ex.message)

catch (System.Data.SqlClient.SqlException ex)
{
System.Diagnostics.Trace.Writeline(ex.message)
return 0;
}
catch (Exception ex) // catch other errors
{
System.Diagnostics.Trace.Writeline(ex.message)
}

 

[Elton Wang]

Hi there,

I think when you use SqlHelper.ExecuteNonQuery for Stored
Procedure, you should explicitly specify Stored Procedure.
I don't remember exact syntax, please check out.

BTW, you don't give type of @Email in stored procedure.

Hope it helps,

Elton Wang
(e-mail address removed)

-----Original Message-----
Here's my simple stored procedure:

ALTER PROCEDURE GetMemberIDByEmail
@Email EmailAddress,
@ID int OUTPUT
AS
SELECT @ID = ID FROM tbl_Member WHERE Email=@Email
RETURN


Here's my C# code (using Microsoft Data


public int GetMemberIdByEmail(string email)
{
SqlParameter[] aryParams = new SqlParameter[2];

aryParams[0] = new SqlParameter("@Email", email);
aryParams[1] = new SqlParameter("@ID", SqlDbType.Int);
aryParams[1].Direction = ParameterDirection.Output;

try
{
SqlHelper.ExecuteNonQuery

(_Connection, "GetMemberIDByEmail", aryParams);

}
catch (System.Data.SqlClient.SqlException)
{
return 0;
}

if (aryParams[1].Value == null)
{
Debug.WriteLine("Still NULL!"); <-- ALWAYS GET THIS!!
}
else
{
Debug.WriteLine("Got it!");
}

return 0;
}


The value of the output parameter is ALWAYS null! I've spent hours trying to
fix this but don't see what is wrong. The stored procedure works great in
Query Analyzer. Can anyone point out what I'm missing? Thanks!

Can anyone


.

 

[Guest]

I think that this code is simply but ok!

public void method()
{
SqlConnection mainConn = new SqlConnection();
mainConn.ConnectionString = "user id=sa;password=pippo;initial
catalog=myDB;data source=(local)";

if (mainConn.State == ConnectionState.Closed)
mainConn.Open();

try
{
SqlCommand myCmd = new SqlCommand("storedProc",mainConn);
myCmd.CommandType = CommandType.StoredProcedure;
SqlParameter ret = new SqlParameter("@RETURN", SqlDbType.Int);
ret.Direction = ParameterDirection.ReturnValue;
myCmd.Parameters["@parameters"].Value = ValueOfParameters;
myCmd.ExecuteNonQuery();

int var = (int)ret.Value;
}
catch (Exception ex)
{
MessageBox.Show("" + ex.Message);
}
finally
{
mainConn.Close();
}
}

I use this statemet when i must use stored procedure!
I hope that this code is well for you!

Good bye

 

[Bob Grommes]

I don't think you're actually passing the email value in that you think you
are.

I don't have any experience passing parameters when the defined SQL type is
a user defined type. I assume that in C# you would have to use the actual
underlying SQL type. If that were, say, varchar, your code would look like
this:

aryParams[0] = new
SqlParameter("@Email",SqlDbType.Varchar,email.Length).Value = email;

Give that a try.

--Bob

 

[Marc Scheuner [MVP ADSI]]

public int GetMemberIdByEmail(string email)

{
SqlParameter[] aryParams = new SqlParameter[2];

aryParams[0] = new SqlParameter("@Email", email);
aryParams[1] = new SqlParameter("@ID", SqlDbType.Int);
aryParams[1].Direction = ParameterDirection.Output;

SqlHelper.ExecuteNonQuery(_Connection, "GetMemberIDByEmail", aryParams);
if (aryParams[1].Value == null)
{
Debug.WriteLine("Still NULL!"); <-- ALWAYS GET THIS!!
}
else
{
Debug.WriteLine("Got it!");
}

You're using the wrong overload for ExecuteNonQuery - the one that
uses a "params object[]......" array (overload no. 4), not the one
using the SqlParameter[] array! (that would be overload no. 5).

In this case, the ExecuteNonQuery call does not know what kind of
parameters you've passed in, and thus cannot update them upon exiting.

Use this call instead:

SqlHelper.ExecuteNonQuery(_Connection, CommandType.StoredProcedure,
"GetMemberIDByEmail", aryParams);

*THEN* you should be able to access your output parameter.

Marc

================================================================
Marc Scheuner May The Source Be With You!
Berne, Switzerland m.scheuner -at- inova.ch