none
Retrieving Data From Database RRS feed

  • Question

  •  

    How do I go about retrieving data from a database and comparing them with what an user entered?

     

    To be detailed, I have a userDB which consists of their UserID and UserPw.

    And of course in my UI, there will be 2 txtboxes. One of them is to enter their UserID and the other is for them to enter their UserPw. And last but not least, a login button.

     

    In my code behind, I wish to validate what they have entered against the data in my database upon clicking the login button.

    How should I go about doing it?

     

    Currently, this is what I have tried but it does not work. Even if I entered the correct username, the lblMessage still display that it is an error. Please advice! Thanks.

    string strConnectionString = ConfigurationManager.ConnectionStrings["ProductConnectionString"].ConnectionString;

    SqlConnection myConnect = new SqlConnection(strConnectionString);

     

    string strCommandText = "SELECT * FROM CustomerDB";

    SqlCommand myCommand = new SqlCommand(strCommandText, myConnect);

    myConnect.Open();

    SqlDataReader myReader = myCommand.ExecuteReader();

     

    while (myReader.Read())

    {

    //successful login

    if (strUserID == (string)myReader["cUserID"])

    {

    lblMessage.Text = "Welcome!" + (string)myReader["cUserID"];

    break;

    }

    //unsuccessful login

    else

    {

    lblMessage.Text = "Error";

    }

    }

    myReader.Close();

    }

    myConnect.Close();

    Thursday, January 24, 2008 3:34 AM

Answers

  • Hi,

     

    The problem is in your SELECT statement. Why do you have to do SELECT *? The reader keeps looping for the no. of records in CustomerDB.

     

    Code Snippet

    Int16 cid = ValidateUser(txtUser.Text.Trim(), txtPass.Text.Trim());

    if(cid >= 1)

    {

    // Do something

    }

     

    // Ideally this should be in a seperate class, called as DAL class.

    private Int16 ValidateUser(string usernm, string pwd)

    {

    string strConnectionString = ConfigurationManager.ConnectionStrings["ProductConnectionString"].ConnectionString;

    SqlCommand cmdUser = new SqlCommand("

    SELECT cid AS CustId FROM CustomerDB WHERE custid= @UserName AND pwd=@Password", strConnectionString);
    cmdUser.Parameters.Add("@UserName", SqlDbType.NVarChar, 8).Value = usernm;
    cmdUser.Parameters.Add("@Password", SqlDbType.NVarChar, 8).Value = pwd;
    object obj = cmdUser.ExecuteScalar();
    return Convert.ToInt16(obj);          

    }

     

     

     

    I hope you got the idea. I have not tested the code. You change the SQL statement as per your table

     

    HTH,
    Suprotim Agarwal

    -----
    http://www.dotnetcurry.com
    -----

    Thursday, January 24, 2008 5:44 AM

All replies

  • Hi,

     

    The problem is in your SELECT statement. Why do you have to do SELECT *? The reader keeps looping for the no. of records in CustomerDB.

     

    Code Snippet

    Int16 cid = ValidateUser(txtUser.Text.Trim(), txtPass.Text.Trim());

    if(cid >= 1)

    {

    // Do something

    }

     

    // Ideally this should be in a seperate class, called as DAL class.

    private Int16 ValidateUser(string usernm, string pwd)

    {

    string strConnectionString = ConfigurationManager.ConnectionStrings["ProductConnectionString"].ConnectionString;

    SqlCommand cmdUser = new SqlCommand("

    SELECT cid AS CustId FROM CustomerDB WHERE custid= @UserName AND pwd=@Password", strConnectionString);
    cmdUser.Parameters.Add("@UserName", SqlDbType.NVarChar, 8).Value = usernm;
    cmdUser.Parameters.Add("@Password", SqlDbType.NVarChar, 8).Value = pwd;
    object obj = cmdUser.ExecuteScalar();
    return Convert.ToInt16(obj);          

    }

     

     

     

    I hope you got the idea. I have not tested the code. You change the SQL statement as per your table

     

    HTH,
    Suprotim Agarwal

    -----
    http://www.dotnetcurry.com
    -----

    Thursday, January 24, 2008 5:44 AM
  • Thanks for your help.

    I roughly got the idea of what you're trying to do.

    Thanks alot!

    Thursday, January 24, 2008 10:13 AM