Invalid attempt to read when no data is present

private void button1_Click(object sender, EventArgs e) { string name; name = textBox5.Text; SqlConnection con10 = new SqlConnection("con strn"); SqlCommand cmd10 = new SqlCommand("select * from sumant where username=@name"); cmd10.Parameters.AddWithValue("@name",name); cmd10.Connection = con10; cmd10.Connection.Open();//line 7 SqlDataReader dr = cmd10.ExecuteReader(); } if ( textBox2.Text == dr[2].ToString()) { //do something; } 

When I debug until line 7, it is OK, but after that dr throws an exception:

Invalid attempt to read when no data is present.

I don't understand why I'm getting that exception, since I do have data in the table with username=sumant.

Please tell me whether the 'if' statement is correct or not. And how do I fix the error?

6 Answers

You have to call DataReader.Read() to fetch the result:

SqlDataReader dr = cmd10.ExecuteReader(); if (dr.Read()) { // read data for single/first record here } 

DataReader.Read() returns a bool indicating if there are more blocks of data to read, so if you have more than 1 result, you can do:

while (dr.Read()) { // read data for each record here } 

You have to call dr.Read() before attempting to read any data. That method will return false if there is nothing to read.

I just had this error, I was calling dr.NextResult() instead of dr.Read().

I would check to see if the SqlDataReader has rows returned first:

SqlDataReader dr = cmd10.ExecuteReader(); if (dr.HasRows) { ... } 

I used the code below and it worked for me.

String email=""; SqlDataReader reader=cmd.ExecuteReader(); if(reader.Read()){ email=reader["Email"].ToString(); } String To=email; 

I was having 2 values which could contain null values.

while(dr.Read()) { Id = dr["Id"] as int? ?? default(int?); Alt = dr["Alt"].ToString() as string ?? default(string); Name = dr["Name"].ToString() } 

resolved the issue