Getting the record count

[lotus18]

Hello World

I know how to display or manipulate (add,delete,upd ate,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.recor dcount. I've just tried using DataTable and OleDbDataAdapte r 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.....i t was just an example.

[lotus18]

[CODE=vb .net]
Private adDataReader As OleDbDataReader
Private adCommand As New OleDbCommand

Friend Function GetTotalItem() As Integer
Call Main.SetConnect ion()

adCommand = New OleDbCommand("S elect Count(ScheduleI D) As myCount, * From Schedules", dbConnection)
adCommand.Comma ndType = CommandType.Tex t
adDataReader = adCommand.Execu teReader
adDataReader.Re ad()


GetTotalItem = adDataReader("m yCount")

adDataReader.Cl ose()
Main.CloseConne ction()
End Function[/CODE]

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,Dat aSet,DataTable. .
Check this code:

[code=vb]
Dim OConn As New OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0; Data Source=C:\MyDb. mdb;")
OConn.Open()
Dim sSQL As String = "Select * From MyTable"
Dim ODa As New OleDbDataAdapte r(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
[/code]

To jeffstl,

adCommand = New OleDbCommand("S elect Count(ScheduleI D) 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