0

I'm querying a SQL Server database and filling a datatable. But even when my sql returns no values, the datatable row count seems to be one. Here's my code:

strSQL = "My sql string"
cmd.CommandText = strSQL;
cmd.Parameters.AddWithValue("@wsNo", wsNo);
cmd.Parameters.AddWithValue("@reachNo", reachNo);
using (DataTable dt = new DataTable())
    {
        dt.Load(cmd.ExecuteReader());
        MyTextbox.Text = dt.Rows.Count.ToString();
        myGridview.DataSource = dt;
        myGridview.DataBind();
    }

The gridview displays the data correctly, and doesn't display at all if the sql returns no records. But I want to display the record count in a textbox, and that's always 1 when there are no records. A couple of places I've checked imply that ExecuteReader might be the wrong tool for this job, but if so, it isn't clear to me what I should be usi

EDIT: I rebuilt this code using a data adapter, and it seems to be working now. I don't see why, shouldn't make a difference, but I guess rewriting the code fixed whatever I was doing wrong. No further comments necessary. Thanks to people who replied.

buckshot
  • 315
  • 4
  • 15
  • I think you have a blank row in the DGV so you can add new rows to the table. So your count is always one more than the actual rows. Why not use the dt row count instead of the DGV count? – jdweng Apr 03 '20 at 19:19

1 Answers1

1

Typically you will want to fill your DataSet with DataAdapters. You don't need a DataTable to bring back one record with a DataReader. But here's how you can do it:

DataTable dt = new DataTable("TableName");

using (connection)
{
    SqlCommand command = new SqlCommand(sSQL, connection);
    connection.Open();

    using (SqlDataReader reader = command.ExecuteReader())
    {
        dt.Load(reader);
    }
}

YourControl.Text = dt.Rows.Count;
kgw
  • 81
  • 9