469,282 Members | 1,888 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

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
Nov 16 '05 #1
5 24998
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" <tu**********@gmail.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
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

Nov 16 '05 #2
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

Nov 16 '05 #3
Hello,
in code u are missing a statement saying

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

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**********@gmail.com> wrote in message
news:#o**************@TK2MSFTNGP11.phx.gbl...
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

Nov 16 '05 #4
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.Leng th).Value = email;

Give that a try.

--Bob

"MS" <tu**********@gmail.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
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

Nov 16 '05 #5
> 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
Nov 16 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by laurenq uantrell | last post: by
8 posts views Thread by Yusuf INCEKARA | last post: by
8 posts views Thread by Christopher Weaver | last post: by
1 post views Thread by Mike P | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.