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

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)
}


"MS" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>

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

"MS" wrote:

> 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
>
>
>

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

"MS" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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

> 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