[thelist] C# Stored Procedure Problem
Dawson Costelloe
costelloe at gmail.com
Mon Jan 8 04:03:40 CST 2007
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