By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,934 Members | 1,527 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,934 IT Pros & Developers. It's quick & easy.

Cannot get OUTPUT parameter after running Sql stored procedure

P: n/a
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 19 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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 19 '05 #2

P: n/a
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
el********@hotmail.com
-----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 tofix this but don't see what is wrong. The stored procedure works great inQuery Analyzer. Can anyone point out what I'm missing? Thanks!
Can anyone
.

Nov 19 '05 #3

P: n/a
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 19 '05 #4

P: n/a
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 19 '05 #5

P: n/a
> 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 19 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.