CODEWITHSUNDEEP
c#visual-studio-2010ms-access
Yogita Negi
Yogita Negi

Reputation: 63

How to count total number of rows of an access database table?

string constr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\yogi\Documents\mydb.mdb";
string cmdstr = "select * from quant_level1";
OleDbConnection con = new OleDbConnection(constr);
OleDbCommand com = new OleDbCommand(cmdstr, con);
con.Open();
OleDbDataReader reader = com.ExecuteReader();
reader.Read();
DataSet data = new DataSet();
int i = data.Tables["quant_level1"].Rows.Count;
Label2.Text = i.ToString();

Upvotes: 0

Views: 27119

Answers (3)

BRAHIM Kamel
BRAHIM Kamel

Reputation: 13794

You should do something like this: 

   Select count(*) from  quant_level1

Change the command syntax executescalar to retrieve a single value 

CommandText = "SELECT COUNT(*) FROM region";   
Int32 count = (int32) ExecuteScalar();

Hope this can enlighten you a bit

Upvotes: 0

Damith
Damith

Reputation: 63105

use

string cmdstr = "SELECT COUNT(*) FROM quant_level1";

With com.ExecuteScalar()

using(OleDbConnection conn = new OleDbConnection(constr))
using(OleDbCommand command = new OleDbCommand(cmdstr, conn)) 
{
    conn.Open();
    int count = (int)command.ExecuteScalar();
}

ExecuteScalar returns the first column of the first row in the result set returned by the query, here it give you row count.

you can use OleDbDataReader as you try in your code sample. but need to change the logic bit.

using (OleDbConnection con = new OleDbConnection(constr))
using (OleDbCommand com = new OleDbCommand("select * from quant_level1", con))
{
    con.Open();
    using (OleDbDataReader myReader = com.ExecuteReader())
    {
        DataTable dt = new DataTable();
        dt.Load(myReader);
        int count = dt.Rows.Count;
    }
}

Why you fail!

You have created data set but you haven't load data to dataset using your DataReader. So you will get zero row count at the end.

Upvotes: 3

King King
King King

Reputation: 63387

Looks like that you want to fill your DataSet and count the rows later:

DataSet data = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(com);  
da.Fill(data);
int i = data.Tables[0].Rows.Count;
Label2.Text = i.ToString();

If you just want to count the rows, you can change the query to SELECT COUNT(*) FROM quant_level1 and get the return value like this:

int i = (int) com.ExecuteScalar();
Label2.Text = i.ToString();

Upvotes: 1

Related Questions