[thelist] C# Stored Procedure Problem

Peter Brunone (EasyListBox.com) peter at easylistbox.com
Mon Jan 8 09:36:57 CST 2007



   I'd be willing to be five bucks that your parameters aren't being passed the way you think they are.  Run through the debugger, stopping at each parameter assignment to check the value of email and password.

HTH,

Peter

				From: "Dawson Costelloe" costelloe at gmail.com

Maybe not quite relevant to the list but I'm desperate! Reference:
http://tinyurl.com/yjt3ma

Hello, when I execute the following stored procedure in MSVS 2005, it
request the three paramaters and returns the correct value/row from the
database as expected:

CREATE PROCEDURE dbo.clientLogin

(
@user_email varchar(50),
@user_password varchar(50),
@user_id int OUTPUT
)

AS

SET NOCOUNT ON
SELECT
@user_id = user_id
FROM
project_user
WHERE
user_email = @user_email
AND
user_password = @user_password
RETURN @user_id

However, if I try and execute it from my class, it runs correctly,
connects to the DB fine; but is returning zero rows! I can't for the
life of me figure out why. I have included the code below, if anyone
could help I would really appreciate it. Thank you

public int validate(string email, string password)
{
try
{
int user_id = 3;
SqlConnection conn = null;
SqlDataReader rdr = null;

// create and open a connection object
conn = new
SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["­projectcontrolConnectionString"].ConnectionString);

conn.Open();

// 1. create a command object identifying
// the stored procedure
SqlCommand cmd = new SqlCommand(
"dbo.clientLogin", conn);

// 2. set the command object so it knows
// to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;

// 3. add parameter to command, which
// will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@user_email",
email));
cmd.Parameters.Add(new SqlParameter("@user_password",
password));
//cmd.Parameters.Add(new SqlParameter("@user_id",
user_id));

// execute the command
rdr = cmd.ExecuteReader();

while (rdr.Read())
{
user_id = Convert.ToInt32(rdr["user_id"]);
}
// close connection and rdr
conn.Close();
rdr.Close();

return user_id;
}
catch
{
return 0;
}
}



More information about the thelist mailing list