Cannot get OUTPUT parameter after running Sql stored procedure

[MS]

Here's my simple stored procedure:

ALTER PROCEDURE GetMemberIDByEm ail
@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 GetMemberIdByEm ail(string email)
{
SqlParameter[] aryParams = new SqlParameter[2];

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

try
{
SqlHelper.Execu teNonQuery(_Con nection, "GetMemberIDByE mail", aryParams);
}
catch (System.Data.Sq lClient.SqlExce ption)
{
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.Sq lClient.SqlExce ption ex)
{
System.Diagnost ics.Trace.Write line(ex.message )
return 0;
}
catch (Exception ex) // catch other errors
{
System.Diagnost ics.Trace.Write line(ex.message )
}
"MS" <tu**********@g mail.com> wrote in message
news:%2******** ********@TK2MSF TNGP11.phx.gbl. ..

Here's my simple stored procedure:

ALTER PROCEDURE GetMemberIDByEm ail
@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 GetMemberIdByEm ail(string email)
{
SqlParameter[] aryParams = new SqlParameter[2];

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

try
{
SqlHelper.Execu teNonQuery(_Con nection, "GetMemberIDByE mail",
aryParams);
}
catch (System.Data.Sq lClient.SqlExce ption)
{
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

[Gabriele]

I think that this code is simply but ok!

public void method()
{
SqlConnection mainConn = new SqlConnection() ;
mainConn.Connec tionString = "user id=sa;password= pippo;initial
catalog=myDB;da ta source=(local)" ;

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

try
{
SqlCommand myCmd = new SqlCommand("sto redProc",mainCo nn);
myCmd.CommandTy pe = CommandType.Sto redProcedure;
SqlParameter ret = new SqlParameter("@ RETURN", SqlDbType.Int);
ret.Direction = ParameterDirect ion.ReturnValue ;
myCmd.Parameter s["@parameter s"].Value = ValueOfParamete rs;
myCmd.ExecuteNo nQuery();

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 GetMemberIDByEm ail
@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 GetMemberIdByEm ail(string email)
{
SqlParameter[] aryParams = new SqlParameter[2];

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

try
{
SqlHelper.Execu teNonQuery(_Con nection, "GetMemberIDByE mail", aryParams);
}
catch (System.Data.Sq lClient.SqlExce ption)
{
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

[Srinivas Kollipara]

Hello,
in code u are missing a statement saying

aryParms[0].value = so*******@abc.c om;

because you are not passing anything into the stored procedure for email. so
sp doesnt return anything here.
try this good luck.
bye

--
Srinivas L.Kollipara
"MS" <tu**********@g mail.com> wrote in message
news:#o******** ******@TK2MSFTN GP11.phx.gbl...

Here's my simple stored procedure:

ALTER PROCEDURE GetMemberIDByEm ail
@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 GetMemberIdByEm ail(string email)
{
SqlParameter[] aryParams = new SqlParameter[2];

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

try
{
SqlHelper.Execu teNonQuery(_Con nection, "GetMemberIDByE mail", aryParams); }
catch (System.Data.Sq lClient.SqlExce ption)
{
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

[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",SqlDbTyp e.Varchar,email .Length).Value = email;

Give that a try.

--Bob

"MS" <tu**********@g mail.com> wrote in message
news:%2******** ********@TK2MSF TNGP11.phx.gbl. ..

Here's my simple stored procedure:

ALTER PROCEDURE GetMemberIDByEm ail
@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 GetMemberIdByEm ail(string email)
{
SqlParameter[] aryParams = new SqlParameter[2];

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

try
{
SqlHelper.Execu teNonQuery(_Con nection, "GetMemberIDByE mail",
aryParams);
}
catch (System.Data.Sq lClient.SqlExce ption)
{
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

[Marc Scheuner [MVP ADSI]]

> public int GetMemberIdByEm ail(string email)

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

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

SqlHelper.Execu teNonQuery(_Con nection, "GetMemberIDByE mail", 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.Execu teNonQuery(_Con nection, CommandType.Sto redProcedure,
"GetMemberIDByE mail", 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