Login check

[brian71289]

Hi, I have a simple question
On my page I have two text boxes, a button and a label.
I have a database DB_rental which contains a table TB_credentials (empID, username, password)

User enters the username and password in the textboxes and clicks the button. The application should check the database if the username and password match. and if they do, the 'empID' should be displayed in the label.

Following is the code I have written and its not working. Please point out where I am wrong and if possible provide me with the code so that i can test myself too! Thanks :)

protected void Button1_Click(object sender, EventArgs e)
        {
            AuthenticateUser(TextBox1.Text, TextBox2.Text);
        }

public string AuthenticateUser(string Username, string Password)
        {
            Username = TextBox1.Text;
            Password = TextBox2.Text;

            using (SqlConnection cn = new SqlConnection("Data Source = .\\SQLEXPRESS; Initial Catalog=DB_Rental; Integrated Security=True;"))
            {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = cn;
                    cmd.CommandText = "select empID from TB_credentials WHERE @Username = userNameTXT and @Password=passwordTXT";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@userNameTXT", Username);
                    cmd.Parameters.AddWithValue("@passwordTXT", Password);
                    SqlDataReader reader;

                    try
                    {
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        while (reader.Read())
                        {
                            string ID = Convert.ToString(reader);
                            return ID ;
                        }

                    }
                    catch
                    {
                        cn.Close();
                        throw;
                    }
                }
            }
        }

[gusano79]

Following is the code I have written and its not working.

"Not working" isn't a very useful description of the problem. When posting questions about broken code, please provide some details, e.g., what you think should happen that isn't happening.

Meanwhile, this is wrong:

string ID = Convert.ToString(reader);

If you want to get values from the reader, you need to tell it which column to retrieve:

string ID = reader["Username"];

...or whatever column name you want.

Also, your query looks suspicious:

select empID from TB_credentials WHERE @Username = userNameTXT and @Password=passwordTXT

Normally column names don't start with @, and the named parameters do:

select empID from TB_credentials WHERE Username = @userNameTXT and Password=@passwordTXT

Recommended reading: Retrieving Data Using a DataReader

Some other observations:

string ID = reader["Username"];
return ID ;

This is a little verbose--no need to declare a variable if all you're going to do is return it; the following does the same thing, but is shorter and more readable:

return reader["Username"];

Also, what happens when there is no data returned? In this section:

while (reader.Read())
{
    return reader["Username"];
}

If reader is empty (i.e., the requested user doesn't exist), the entire while loop is skipped, and you have no code to deal with this possibility. Also, using a while loop here is somewhat inappropriate, as you only care about the first record returned.

Consider this alternative:

if(reader.Read())
{
    return reader["Username"];
}
else // Do something about the missing user?
{
    return null;
}

This is clearer about your intent with reader, and provides an opportunity to handle missing users.

Returning null is just a simple way to indicate "no user"--but any time someone hands you a solution, take at least a few moments to consider how it fits with the rest of your code, and how else you might do the same thing.

[brian71289]

g about the missing user?

Thanks a lot Gusano79. It works.
I was not giving the name of the column from where to retrieve the value. Stupid me!
and I apologize for not being specific. But, this works great. Thanks :)