check Datareader is null or not

Question

Please see my below code, I am trying to read data from a table, if there are any rows in table which matches the condition then execute some lines of code and if there are no rows then execute other set of code.

when I execute this piece of code, its throwing an exception. "Invalid attempt to read when no data is present" any suggestion as to where exactly am going wrong.

public void Check_Last_RegNo()
    {
        try
        {

            con = new SqlConnection(cs.DBConn);
            con.Open();
            string ct = "SELECT Max(RegistrationNo) from tbl_StudentRegNo where (RegistrationNo Like'%" + label3.Text + "%')";
            cmd = new SqlCommand(ct);
            cmd.Connection = con;
            rdr = cmd.ExecuteReader();
            if (rdr[0]!=null && rdr[0]!=DBNull.Value)
            {

                int ii = 1;
                for (int max = 0; max < dataGridView1.Rows.Count; max++)
                {

                    label4.Text = ii.ToString("0000");
                    string concati = reg_no + label3.Text + label4.Text;
                    dataGridView1.Rows[max].Cells[2].Value = concati;
                    ii++;
                }


            }
            else
                {
                    while (rdr.HasRows)
                    {
                        label5.Text = (rdr[0]).ToString();
                        string lastregno = label5.Text;
                        string digits = lastregno.Substring(lastregno.Length - 4, 4);
                        label4.Text = digits.ToString();
                        int i = Convert.ToInt32(digits) + 1;
                        for (int max = 0; max < dataGridView1.Rows.Count; max++)
                        {

                            label4.Text = i.ToString("0000");
                            string concati = reg_no + label3.Text + label4.Text;
                            dataGridView1.Rows[max].Cells[2].Value = concati;
                            i++;
                        }
                    }
                }


        con.Close();

        }

        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

Answer 1

Finally I had to change my code a bit, and the exception was gone, change seems bit complicated but it worked. Here is the code.

try
        {

            con = new SqlConnection(cs.DBConn);
            con.Open();
            string ct = "SELECT Max(RegistrationNo) from tbl_StudentRegNo where (RegistrationNo Like'%" + lblshortcode.Text + "%')";
            cmd = new SqlCommand(ct);
            cmd.Connection = con;
            rdr = cmd.ExecuteReader();
            if (rdr.HasRows)
            {
                while (rdr.Read())
                {
                    lbllastregno.Text = (rdr[0]).ToString();
                    string lastregno = lbllastregno.Text;
                    if(rdr[0]!=DBNull.Value)
                    {
                        string current_serial_no = lastregno.Substring(lastregno.Length - 4, 4);
                        lblcurrentsno.Text = current_serial_no;
                        int next_serial_no = Convert.ToInt32(current_serial_no) + 1;
                        lblnextsno.Text = next_serial_no.ToString("0000");
                    }
                    else
                    {
                        lbllastregno.Text = reg_no + lblshortcode.Text + lbldigitformat.Text;
                    }

                }
            }
            else
            {
                lbllastregno.Text = reg_no + lblshortcode.Text + lbldigitformat.Text;
            }
            con.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

Thank you for your valuable suggestions.

Answer 2

When you build an SqlDataReader with ExecuteReader the object returned is not positioned on the first record of the returned set of data (if any) so, before trying to access any field in the reader, you need to call Read() or HasRows The latter in case you just want to know if there are records, the first, in case you need to actually read the dataset returned. Your error is triggered by the fact that you try to use the Reader accessing rdr[0] before moving the Reader on the first record.

But you are complicating things, because, given this query:

string ct = "SELECT Max(RegistrationNo) ....";

then you have a null or just one line with one column. So the best method to call here is ExecuteScalar

 cmd = new SqlCommand(ct);
 cmd.Connection = con;
 object result = cmd.ExecuteScalar();
 if(result != null)
 {
    // Now you have here your registration value as a string
    string maxRegistrationNo = result.ToString();

    // You could apply your logic to extract the required parts
    ....
    ....
 }
 else
 {
     // Your logic for a null return from your query
     ......
 }

As you can read from MSDN

Use the ExecuteScalar method to retrieve a single value (for example, an aggregate value) from a database. This requires less code than using the ExecuteReader method, and then performing the operations that you need to generate the single value using the data returned by a SqlDataReader.

Answer 3

Your first if statement don't makes sense. you can use if condition to check if any row found and read rows one by one, and you have to check if rdr[0] is not null then do operation on it. :

if (rdr.HasRows)  // check if any row found
        {
            while(rdr.Read())  // read all matched rows one by one
            {
                label5.Text = rdr[0] != DbNull.Value ? (rdr[0]).ToString() : string.Empty;
                string lastregno = label5.Text;
                string digits = lastregno.Substring(lastregno.Length - 4, 4);
                label4.Text = digits.ToString();
                int i = Convert.ToInt32(digits) + 1;
                for (int max = 0; max < dataGridView1.Rows.Count; max++)
                {

                    label4.Text = i.ToString("0000");
                    string concati = reg_no + label3.Text + label4.Text;
                    dataGridView1.Rows[max].Cells[2].Value = concati;
                    i++;
                }
            }
        }

SqlDataReader fetches row in a sequence one by one so for reading a row we have to call rdr.Read(), when we call it, it fetches one row, so by writing while(rdr.Read()) we are saying that read records one by one until all records are processed.

When you call rdr[0] first without calling rdr.Read() it will obviously fail because there is no row being read which rdr hold.

Answer 4

You are attempting to check the reader values without verifying if it has any rows by using the if statement. This causes the Exception.

You can use reader.Read() to check and move the reader to the next row in one action.

If you need to check if the reader has Rows to execute an alternate flow, check for rdr.HasRows as the first condition.

if (rdr.HasRows) 
{
            while(rdr.Read())
            {
                label5.Text = (rdr[0]).ToString();
                string lastregno = label5.Text;
                string digits = lastregno.Substring(lastregno.Length - 4, 4);
                label4.Text = digits.ToString();
                int i = Convert.ToInt32(digits) + 1;
                for (int max = 0; max < dataGridView1.Rows.Count; max++)
                {

                    label4.Text = i.ToString("0000");
                    string concati = reg_no + label3.Text + label4.Text;
                    dataGridView1.Rows[max].Cells[2].Value = concati;
                    i++;
                }
            }
}
else
            {
                int ii = 1;
                for (int max = 0; max < dataGridView1.Rows.Count; max++)
                {

                    label4.Text = ii.ToString("0000");
                    string concati = reg_no + label3.Text + label4.Text;
                    dataGridView1.Rows[max].Cells[2].Value = concati;
                    ii++;
                }
            }