By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,949 Members | 890 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,949 IT Pros & Developers. It's quick & easy.

Getting the record count

lotus18
100+
P: 866
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
Jun 13 '08 #1
Share this Question
Share on Google+
8 Replies


jeffstl
Expert 100+
P: 432
A quick answer would be to modify your query slightly to contain a count of the records.

Expand|Select|Wrap|Line Numbers
  1.  
  2. sql = "SELECT COUNT(column) as MyCount, * FROM MyTable"
  3.  
  4. DBReader.Read()
  5.  
  6. NumberOfReceords = DBReader("MyCount")
  7.  
  8. DBReader.Close
  9.  
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.
Jun 13 '08 #2

lotus18
100+
P: 866
A quick answer would be to modify your query slightly to contain a count of the records.

Expand|Select|Wrap|Line Numbers
  1.  
  2. sql = "SELECT COUNT(column) as MyCount, * FROM MyTable"
  3.  
  4. DBReader.Read()
  5.  
  6. NumberOfReceords = DBReader("MyCount")
  7.  
  8. DBReader.Close
  9.  
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 .
Jun 13 '08 #3

jeffstl
Expert 100+
P: 432
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.
Jun 13 '08 #4

lotus18
100+
P: 866
Expand|Select|Wrap|Line Numbers
  1.  Private adDataReader As OleDbDataReader
  2. Private adCommand As New OleDbCommand
  3.  
  4. Friend Function GetTotalItem() As Integer
  5.         Call Main.SetConnection()
  6.  
  7.         adCommand = New OleDbCommand("Select Count(ScheduleID) As myCount, * From Schedules", dbConnection)
  8.         adCommand.CommandType = CommandType.Text
  9.         adDataReader = adCommand.ExecuteReader
  10.         adDataReader.Read()
  11.  
  12.  
  13.         GetTotalItem = adDataReader("myCount")
  14.  
  15.         adDataReader.Close()
  16.         Main.CloseConnection()
  17.     End Function
Rey Sean
Jun 14 '08 #5

jeffstl
Expert 100+
P: 432
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private adDataReader As OleDbDataReader
  3. Private adCommand As New OleDbCommand
  4.  
  5. Friend Function GetTotalItem() As Integer
  6.         Call Main.SetConnection()
  7.  
  8.         adCommand = New OleDbCommand("Select Count(ScheduleID) As myCount, * From Schedules", dbConnection)
  9.         adCommand.CommandType = CommandType.Text
  10.         adDataReader = adCommand.ExecuteReader
  11.  
  12.  
  13.     if adDataReader.Read() then
  14.          GetTotalItem = adDataReader("myCount")
  15.      else
  16.         GetTotalItem = 0
  17.     end if
  18.         adDataReader.Close()
  19.         Main.CloseConnection()
  20. End Function
  21.  
  22.  
  23.  
Jun 14 '08 #6

lotus18
100+
P: 866
Sorry for the late reply. Even with or without if-else statement, it always returns 0.

Rey Sean
Jun 16 '08 #7

QVeen72
Expert 100+
P: 1,445
Hi Ray,

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

Expand|Select|Wrap|Line Numbers
  1. Dim OConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\MyDb.mdb;")
  2. OConn.Open()
  3. Dim sSQL As String = "Select * From MyTable"
  4. Dim ODa As New OleDbDataAdapter(sSQL, OConn)
  5. Dim DS As New DataSet
  6. Oda.Fill(DS)
  7. Dim DT As DataTable = DS.Tables(0)
  8. Dim RecCnt As Integer = DT.Rows.Count
  9. OConn.Close
  10.  
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
Jun 16 '08 #8

lotus18
100+
P: 866
Thanks veena.. I'll try this later : )

Rey Sean
Jun 16 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.