[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