Getting the record count

[lotus18]

Hello World

I know how to display or manipulate (add,delete,update,search) records using the OleDbDataReader and OleDbCommand class. But I'm clueless in getting the total record on a query.

In vb6, I know how to do it by using recordset.recordcount. I've just tried using DataTable and OleDbDataAdapter class but I can't make it. Please guide me. BTW, I'm using vb2005.

Rey Sean

[jeffstl]

A quick answer would be to modify your query slightly to contain a count of the records.

 
sql = "SELECT COUNT(column) as MyCount, * FROM MyTable"
 
DBReader.Read()
 
NumberOfReceords = DBReader("MyCount")
 
DBReader.Close
 

I am probably missing a better way, but I too am accustomed to using ADODB recordsets in classic asp.

That said though, this will work.

[lotus18]

A quick answer would be to modify your query slightly to contain a count of the records.

 
sql = "SELECT COUNT(column) as MyCount, * FROM MyTable"
 
DBReader.Read()
 
NumberOfReceords = DBReader("MyCount")
 
DBReader.Close
 

I am probably missing a better way, but I too am accustomed to using ADODB recordsets in classic asp.

That said though, this will work.

It gives me an error result .

[jeffstl]

OK.

Can I see what your code looks like ?

You wouldnt be able to copy and paste mine directly and expect it to work obviously.....it was just an example.

[lotus18]

 Private adDataReader As OleDbDataReader
Private adCommand As New OleDbCommand
 
Friend Function GetTotalItem() As Integer
        Call Main.SetConnection()
 
        adCommand = New OleDbCommand("Select Count(ScheduleID) As myCount, * From Schedules", dbConnection)
        adCommand.CommandType = CommandType.Text
        adDataReader = adCommand.ExecuteReader
        adDataReader.Read()
 
 
        GetTotalItem = adDataReader("myCount")
 
        adDataReader.Close()
        Main.CloseConnection()
    End Function

Rey Sean

[jeffstl]

Ok. It would also help to know what the actual error is.

Off hand you could try this in case your query is not pulling anything

 
Private adDataReader As OleDbDataReader
Private adCommand As New OleDbCommand
 
Friend Function GetTotalItem() As Integer
        Call Main.SetConnection()
 
        adCommand = New OleDbCommand("Select Count(ScheduleID) As myCount, * From Schedules", dbConnection)
        adCommand.CommandType = CommandType.Text
        adDataReader = adCommand.ExecuteReader
 
 
    if adDataReader.Read() then
         GetTotalItem = adDataReader("myCount")
     else
        GetTotalItem = 0
    end if
        adDataReader.Close()
        Main.CloseConnection()
End Function
 
 
 

[lotus18]

Sorry for the late reply. Even with or without if-else statement, it always returns 0.

Rey Sean

[QVeen72]

Hi Ray,

Instead of DataReader , Use a Combination of DataAdapter,DataSet,DataTable..
Check this code:

Dim OConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\MyDb.mdb;")
OConn.Open()
Dim sSQL As String = "Select * From MyTable"
Dim ODa As New OleDbDataAdapter(sSQL, OConn)
Dim DS As New DataSet
Oda.Fill(DS)
Dim DT As DataTable = DS.Tables(0)
Dim RecCnt As Integer = DT.Rows.Count
OConn.Close
 

To jeffstl,

adCommand = New OleDbCommand("Select Count(ScheduleID) As myCount, * From Schedules", dbConnection)

Problem with the SQL Statement, using aggregrate function Count without Grouping Results in an Error..

Regards
Veena

[lotus18]

Thanks veena.. I'll try this later : )

Rey Sean