Invalid attempt to read when no data is present by SqlDataReader

Question

this is my code

public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        SqlConnection con = new SqlConnection(@"Data Source=.;Initial Catalog=DayanaP;Integrated Security=True");
        
        private void button1_Click(object sender, EventArgs e)
        {
            con.Open();
            SqlCommand test = new SqlCommand("select * from maindata where userid='"+textBox1.Text+ "' and password='" + textBox2.Text + "' ", con);
            SqlDataReader dr = test.ExecuteReader();
            int count = 0;
            while (dr.Read())
            {
                count++;
            }
            if (count == 1)
                MessageBox.Show(dr.GetString(0));
            else
            {
                MessageBox.Show("123");
            }

            con.Close();
        }
    }
}

and that is my database maintable which I used in code

when I enter name and password I hope to see a message box which show the user first name, but I get this error:

An unhandled exception of type 'System.InvalidOperationException' 
occurred in System.Data.dll
Additional information: Invalid attempt to read when no data is present.

How can I fix this?

Answer 1

You have already consumed the reader

    while (dr.Read())
    {
        count++;
    }
    //dr is now empty
    if (count == 1)
        MessageBox.Show(dr.GetString(0));

this is what you need

    while (dr.Read())
    {
        count++;
        if (count == 1)
            MessageBox.Show(dr.GetString(0));
    }

or shorter

    if (dr.Read())
    {
        MessageBox.Show(dr.GetString(0));
    }
    else
    {
        MessageBox.Show("123");
    }

or

    string rdrZero = "123";
    if (dr.Read())
    {
        rdrZero = GetString(0));
    }
    MessageBox.Show(rdrZero);

Answer 2

dr.Read() reads the next available row if there is any. If there are no more rows, dr.Read() returns false.

So in your code:

 while (dr.Read())
 {
      count++;
 }

you call dr.Read() until it returns false meaning there is nothing more to read.
So your call to dr.GetString(0) appears after everything is already read.

One solution could look like this:

string s = null;
if (dr.Read()) s = dr.GetString(0);
if (string.IsNullOrEmpty(s) || dr.Read())
    MessageBox.Show("123");
else
    MessageBox.Show(s);

This does the same as (I guess) your code intended to do: shows the returned string if there was exactly one row in the result set.

---

But there are more problems in your code:

1. Don't insert user input directly into your query! Your code is open to SQL Injection. Please use parameterized queries.
2. Instead of SELECT * I recommend to explicitly name the columns you want to fetch. How do you know the 0th column will be the string you want?
3. Since you only want the first name, it may be better to use SELECT firstname... and use ExecuteScalar() instead of ExecuteReader(). But probably you want more columns later in your project.

Answer 3

You should check first if the datareader has rows like this example. You also want to make sure that you are retrieving the value from the datareader inside the while block.

 var value = string.empty;
 if(dr.HasRows)
 {    
     while (dr.Read())
     {
        count++;
        value = dr.GetString(0);
     }    
 }