Display number of records in a table

Question

I'm trying to display numbers of records (in table) using C# Windows form . Bud It display "1" as output for every time . Here is the code.

private void button1_Click(object sender, EventArgs e)
{
    string constr = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Visual Studio/database.mdf;Integrated Security=True";
    SqlConnection con = new SqlConnection(constr);
    con.Open();
    string query= "select Count(*) from Student where Name like '%b%' ";
    SqlCommand cmd = new SqlCommand(query1, con);
    SqlDataReader dr = cmd.ExecuteReader();
    int count = 1;
    while (dr.Read())
        {count++;}
    label1.Text ="Following records : "+count+" ";
}

score 0 · Answer 1 · answered May 10 '15 at 17:37

0

selecting count(*) returns one record with the value of the column holding the number of rows in the table. You don't need to count the number of rows in the result, you just need to get it from the first (and only) row:

int count = 0;
if (dr.Read()) {
    count = dr.GetInt32(0);
} else {
    // something went horribly wrong. Throw an exception perhaps?
}

answered May 10 '15 at 17:37

Mureinik

297,002
52
306
350

Still its not work.. all i need is to show number of all records.. Jus only how many records do exist in table. – Bee Lal May 10 '15 at 17:50

score 0 · Answer 2 · edited May 23 '17 at 11:58

If you need to count all of your records, then you need to remove LIKE filter from the query.

You do not have to use SqlDataReader - the ExecuteScalar is enough. For the start, your code should be:

private void button1_Click(object sender, EventArgs e)
{
    string constr = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Visual Studio/database.mdf;Integrated Security=True";
    SqlConnection con = new SqlConnection(constr);
    con.Open();
    string query= "select Count(*) from Student";
    SqlCommand cmd = new SqlCommand(query1, con);
    int count = (int)cmd.ExecuteScalar();
    label1.Text ="Following records : "+count+" ";
}

Also, consider learning about using statement which enforces good practice for releasing and disposing resources.

Very important thing when you work with the database connections, transactions and commands. SqlCommand with using statement

score 0 · Answer 3 · answered May 11 '15 at 05:50

0

i think you should use rownum function it will display the number for each record for more info check this link http://docs.oracle.com/cd/B12037_01/server.101/b10759/pseudocolumns008.htm

answered May 11 '15 at 05:50

lena

89
1
1
12

Display number of records in a table

3 Answers3