I keep getting "Invalid attempt to read when no data is present" even though I am sure a record exist

Question

I have been trying to figure out why I am getting this error message and it's driving me up the wall cause I think I have applied all that is needed to get the result I desire. What can I do to accomplish this.

private void btnLogin_Click(object sender, EventArgs e)
    {
        try
        {
            // Receive user input from login screen
            string username = txtUsername.Text;
            string password = txtPassword.Text;

            // Test if user input is null or white space aka empty
            if (string.IsNullOrWhiteSpace(username) || string.IsNullOrWhiteSpace(password))
                MessageBox.Show("Please enter both username and password");
            else
            {
                // Establish connection with database
                SqlConnection cn = new SqlConnection(@"SERVER=KACY-PC\SQLEXPRESS2014;DATABASE=hardwareMgmt;Integrated Security=True");
                cn.Open();

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = cn;

                string strSQL = "SELECT * FROM tbl_user WHERE username = '" + username + "' AND password = '" + password + "'";
                cmd.CommandText = strSQL;

                SqlDataReader dr = cmd.ExecuteReader();

                // Count number of record
                int count = 0;
                while (dr.Read())
                    count += 1; MessageBox.Show(Convert.ToString(count));
                dr.Read();

                // Validate whether user has logged in before and display appropriate 
                if (count == 1 && dr["first_login"].ToString() == "N")
                    MessageBox.Show("Welcome back '" + dr["first_name"].ToString() + "' '" + dr["last_name"].ToString() + "'", "Welcome back", MessageBoxButtons.OK);
                else if (count == 1 && dr["first_login"].ToString() == "Y")
                    MessageBox.Show("Hello " + dr["first_name"].ToString() + "' '" + dr["last_name"].ToString() +
                        "\nIt appears that you are logging in for the first time" +
                        "\nor your password got reset", "Welcome", MessageBoxButtons.OK, MessageBoxIcon.Information);
                else if (count > 1)
                    MessageBox.Show("Duplication in user account \nPlease contact System Administrator", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                else
                    MessageBox.Show("Invalid Username or Password", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

I Keep getting the following result after I enter the credentials in the login window. I am sure that the record is found in the database but it just not reading it.

Answer 1

Your

dr.Read();

line is unnecessary since after your

while (dr.Read())
    count += 1; MessageBox.Show(Convert.ToString(count));

code, there will be no next record to read, that's why you get this error.

As Ivan commented, you can not read any data after your while. That's why, whatever wanna read first_login, first_name, last_name etc.. columns, you have to read while you iterating your reader. That's why consider to change your logic first.

And for myself, I prefer to use GetXXX methods of SqlDataReader when I wanna read the values instead of dr[...] syntax. That makes more readable in my opinion.

A few things more;

• You should always use parameterized queries by the way. This kind of string concatenations are open for SQL Injection attacks.

• Do not store your passwords as a plain text. Read: Best way to store password in database

• Use using statement to dispose your connection, command and reader automatically.