Checking whether DataReader is empty

Question

My code does not run when the DataReader is empty. Below is my code.

My work is about Date Scheduling. And my problem is about a holiday constraint. When the user enters the dates (start date and end date), the program will check whether the entered dates have any holidays in between. If the DataReader doesn't have any data, the entered dates should be saved, or if the DataReader has data, then the entered dates are not saved and the program gives an error message.

try
{
    econ = new SqlConnection();
    econ.ConnectionString = emp_con;
    econ.Open();
    ecmd = new SqlCommand("SELECT CD_Date FROM CONS_DATES where CD_Date between '" + Convert.ToDateTime(dtpStart.Text) + "' and '" + Convert.ToDateTime(dtpEnd.Text) + "'", econ);
    ecmd.CommandType = CommandType.Text;
    ecmd.Connection = econ;
    dr = ecmd.ExecuteReader();
    while (dr.Read())
    {
        DateTime cdname = (DateTime)dr["CD_Date"];

        //This code is working
        if (Convert.ToDateTime(cdname) >= Convert.ToDateTime(dtpStart.Text) || Convert.ToDateTime(cdname) <= Convert.ToDateTime(dtpEnd.Text))
        {
            MessageBox.Show("Holiday Constraint. Creating Record Denied.");
        } //if

        //This code is not working. When the program fetch with no record, it should be continue to add the record but it's not working
        else
        if (dr == null || !dr.HasRows)
        {
            //In this area is my code for inserting the entered data.
            MessageBox.Show("Add na|!!!. Creating Record Denied.");
        }//if else
    }//while
}//try
catch (Exception x)
{
    MessageBox.Show(x.GetBaseException().ToString(), "Connection Status", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

And how do you want to add records using a DataReader, an object that reads data? Is this all the code you have? — Marcote, Oct 22 '12 at 03:18
since your condition for the while loop is `dr.Read()`, `dr` cannot be `null` when inside the loop. If it is `null`, `while` loop will not be executed at all. What do you want to achieve through checking `dr` for `null`? — manas, Oct 22 '12 at 03:30
@Markust, I'm not using the DataReader to save my record, I'm only using the DataReader for fetching my data. My code above are not complete bcoz some of those are not part of this problem and they are all working. — Kevin James, Oct 22 '12 at 03:45
@silent_warrior, now I understand that dr.Read() cannot be null, I really want to check for the values. — Kevin James, Oct 22 '12 at 03:46
Possible duplicate of [how to check if a datareader is null or empty](https://stackoverflow.com/questions/762861/how-to-check-if-a-datareader-is-null-or-empty) — Liam, Jun 30 '17 at 10:11

manas · Answer 1 · 2012-10-22T04:16:27.470

10

Your problem is, the while loop runs only if dr has one or more records. But, if the dr is null then the while loop will never run.

Better solution is to have a System.Data.SqlClient.SqlDataReader.

And check,

if (!dr.HasRows)
{
    // Your code to save the records, if no holidays found
}
else
{
    // Your code to show the error message, if there is one or more holidays
}

edited Oct 22 '12 at 04:16

answered Oct 22 '12 at 03:40

manas

397
6
25

where should I add the System.Data.SqlClient.SqlDataReader? Thanks – Kevin James Oct 22 '12 at 03:53
`dr` should be type of `System.Data.SqlClient.SqlDataReader`, I assume you are using `Microsoft.VisualStudio.Data.DataReader`. If not no need to define anything, just use the code above. – manas Oct 22 '12 at 03:55
what is not working? Please be specific. and what do you want to achieve? – manas Oct 22 '12 at 04:00
thanks for helping me out an idea. I just pass the data to be shown in datagridview then create a condition if the datagridview has value, the record will not be saved. then if the record doesn't have any record, the entered data will be saved. – Kevin James Oct 22 '12 at 04:09

score 0 · Answer 2 · edited Jun 30 '17 at 10:11

0

while(dr.Read()) means dr has at least 1 Row.

so else will never execute

can you do else condition before while?

edited Jun 30 '17 at 10:11

Liam

27,717
28
128
190

answered Oct 22 '12 at 03:18

yukaizhao

681
5
17

score 0 · Accepted Answer · answered Oct 22 '12 at 04:07

0

I tried my idea and it answered my question. What I did was to show the fetched data in datagridview then I created a condition if the datagridview has a record then my records will not be saved and if the datagridview doesn't have any record, then the record will be saved.

Thanks Everyone!

answered Oct 22 '12 at 04:07

Kevin James

87
3
4
10

if you don't need to show the datagridview to the user, then just use the if condition on my answer to save the records. Adding and checking the datagridview can be an unecessary overhead – manas Oct 22 '12 at 04:14

score 0 · Answer 4 · edited Mar 13 '13 at 01:34

0

Try this:

while (sqlreader.Read())
{
    tbSubscriptionInfo.Text = sqlreader["nr_ore"].ToString();
}

if (!sqlreader.HasRows)
{
    tbSubscriptionInfo.Text = "";
}

edited Mar 13 '13 at 01:34

rhughes

9,257
11
59
87

answered Mar 13 '13 at 01:13

ValyB

1

score 0 · Answer 5 · edited Jun 02 '14 at 11:58

0

if (dr == null || !dr.HasRows)
{
    // Your code to show the error message, if there is one or more holidays       
}
else
{
    // Your code to save the records, if no holidays found
}

edited Jun 02 '14 at 11:58

slavoo

5,798
64
37
39

answered Jun 02 '14 at 11:36

Sinsil Mathew

498
6
20

Checking whether DataReader is empty

5 Answers5