Trouble when reading an empty table

[AngelicOne]

I've tried reading through datareader from the database.
Tried dataadapter, reading from dataset and datatable.

All of them generates an error when reading empty rows. Yes, I have used try and catch statements but the error still generated, that is transforming only in a messagebox.

What I want is to completely remove the error when reading empty table rows.
I have a hidden textbox that is being incremented for every successful register.

SqlDataAdapter da = new SqlDataAdapter("SELECT MAX(Transaction_No) AS Transaction_No FROM Transactions", con);
            da.Fill(ds);
            DataTable dt = new DataTable();
            dt = ds.Tables[0];

            if (dt.Rows.Count > 0)
            {
                textBox1.Text = (Convert.ToInt32(dt.Rows[0]["Transaction_No"]) + 1).ToString();
            }

It has no try and catch, but I already said that even it is included, the error is still being generated, only transformed into a messagebox. I also tried adding an else statement, but no good.

[Lusiphur]

Ok, quick question here...

Are we talking about attempts to read a table that has 0 rows or are we talking about reading from a table where there may be an empty row returned as part of the query?

[Lusiphur]

On a different note, I generally avoid using DataTable myself... I'd rather iterate through DataSet and DataRow.

For example:

SqlDataAdapter da = new SqlDataAdapter("SELECT MAX(Transaction_No) AS Transaction_No FROM Transactions", con);

da.Fill(ds);

if (ds.Tables[0].Rows.Count > 0)//checks to see that the number of populated rows in table 0 are greater than 0
{
    DataRow dr = ds.Tables[0].Rows[0];//sets datarow "dr" as row 0 of table 0 of the dataset
    textBox1.Text = (Convert.ToInt32(dr["Transaction_No"]) + 1).ToString();
}

[AngelicOne]

I have a datagrid, then what if the user deletes all the rows, then my database will also be empty.

Then when the user opens the form again, which has a form_load event to populate the datagridview, it will be empty and the error will show.

[Lusiphur]

Which is why you do your conditional read based on the number of rows present in the dataset, if no rows exist in the dataset then it will not attempt population. If, on the other hand you set your condition based on a dataTable then it's trying to move information from an empty dataset to a dataTable and generates an error.

[AngelicOne]

I've tried adding an if statement

if (ds.Tables[0].Rows.Count > 0)
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Users", con);
                da.Fill(ds);

                dataGridView1.DataSource = ds.Tables[0];
            }

Cannot find table 0 error

[AngelicOne]

Silly me!

SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Users", con);
                da.Fill(ds);
 if (ds.Tables[0].Rows.Count > 0)
            {
                dataGridView1.DataSource = ds.Tables[0];
            }

I put the select statement inside if, my bad.

[Lusiphur]

And I can immediately see why.
Let's try this again :)

SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Users", con);
da.Fill(ds);

if (ds.Tables[0].Rows.Count > 0)
{
    dataGridView1.DataSource = ds.Tables[0];
}

You need to do the condition check AFTER the dataSet is populated but BEFORE the dataGridView is populated.

The only concern here is, in the event that there is no information in the dataSet, the dataGridView is not populated at all so you may need to find some way to address that as it means that without a dataSource the dataGridView will also be unable to add to your table.

[AngelicOne]

1minute difference. :D

Thanks!

[Lusiphur]

1minute difference. :D

Thanks!

Ya, you snuck in there while I was typing :twisted: Hope it helped though.

[nick.crane]

Try adding a test for the table count.

if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
    dataGridView1.DataSource = ds.Tables[0];
}

[AngelicOne]

Uhm, I know this is a solved thread but I also want to know how to use datareader on this.

I tried adding if(reader.Hasrows) but it doesn't work.

[Lusiphur]

You can always un-solve the thread if you want to continue the discussion :twisted:

On that note, I don't have an answer for you at the moment, sorry.

[AngelicOne]

Ok done! So for datareader, what is the conditional statement?

[Geekitygeek]

SHould just be if(myReader.HasRows)

[AngelicOne]

So I will not include a while(reader.Read)?

I had done this

if(reader.HasRows)
{
while(reader.Read())
{
/do something
}
}

[Geekitygeek]

Sorry, i misunderstood..thought you just wanted the datareader equivalent of if(ds.Tables[0].Rows.Count > 0) .
Your code is correct, you have to check if there are rows before you iterate through them otherwise you get a "Invalid attempt to read when no data is present" error :p

[AngelicOne]

SqlDataAdapter da = new SqlDataAdapter("SELECT MAX(Transaction_No) AS Transaction_No FROM Transactions", con);
 
da.Fill(ds);
 
if (ds.Tables[0].Rows.Count > 0)//checks to see that the number of populated rows in table 0 are greater than 0
{
    DataRow dr = ds.Tables[0].Rows[0];//sets datarow "dr" as row 0 of table 0 of the dataset
    textBox1.Text = (Convert.ToInt32(dr["Transaction_No"]) + 1).ToString();
}

I just notice that this will not work if the table from the database is empty. Obviously, it couldn't read the maximum value because of empty database table. How to fix this?

[AngelicOne]

i've used this.

SqlDataAdapter da = new SqlDataAdapter("SELECT Employee_No FROM Users", con);
            da.Fill(ds);

            if (dt.Rows.Count > 0)
            {
                SqlDataAdapter da1 = new SqlDataAdapter("SELECT MAX (Employee_No) AS Employee_No FROM Users", con);
                da1.Fill(ds);
                dt = ds.Tables[0];
                textBox1.Text = (Convert.ToInt32(dt.Rows[0]["Employee_No"]) + 1).ToString();
            }
            else
            {
                textBox1.Text = "1";
            }

Do you think this is fine?

[Geekitygeek]

Where are you populating dt? You fill your dataset (ds) then check the rows of what i assume is a datatable (dt), but your code doesnt show how you populate the datatable. If your adapter only returns one table then you can use

DataTable dt;
da.Fill(dt);

You're right, if there query returns no results then the datatable will be empty and trying to check Rows.Count will throw an exception.
You can use this to avoid the error when checking:

if(dt!=null && dt.Rows.Count > 0)
{ ...
}

By using an exclusive comparison the second check is not performed if the first returns false.

[AngelicOne]

My bad.

dt = ds.Tables[0]

[Geekitygeek]

You'll still get an error if the query returns no results because ds.Tables[0] will be empty.
Use the original method but add in the exclusive conditional check I showed you :)

[AngelicOne]

That's why there's an if statement

if(dt.Rows.Count>0)

[nick.crane]

No. What Ryshad is saying is that dt may be null if there is no data returned.
This will mean that dt.Rows will throw a null exception.
Do the if like in Ryshads post.

[Lusiphur]

They've got a point AngelicOne and to be honest between nick.crane and Ryshad you've got 2 of the better helpers/posters in this sub-forum helping you which is why I stepped out of this one :twisted: I'd be heedin' their advice if I were you :)