• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

Record count from a recordset

Hi - I am sure this is a very simple question, if you know what the terminology is - but I don't and it has me stuck so any help would be very much appreciated.

I am querying a fairly simple table and just need to know how many records are returned from the query. If I use the recordcount function of the recordset it constantly states it is -1, but in debug mode I can see that a number of records have been returned.

The code for my query is shown below,

Many thanks
...Steve

-----------------------

 Dim conDB As New ADODB.Connection
        Dim cmdDB As New ADODB.Command
        Dim rsDB As New ADODB.Recordset

        conDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\revolution\Optom.mdb"
        conDB.Open()
        cmdDB.ActiveConnection = conDB

        cmdDB.CommandText = "SELECT count(*) FROM Exception_days where business_date between #" & Trim(Str(cmbMonth.SelectedIndex + 1)) & "/01/" & Trim(cmbYear.SelectedItem) & "# and #" & Trim(Str(cmbMonth.SelectedIndex + 1)) & "/" & Trim(Str(iDaysinMonth)) & "/" & Trim(cmbYear.SelectedItem) & "#"

 rsDB = cmdDB.Execute
0
swtiley
Asked:
swtiley
1 Solution
 
cyberdevil67Commented:
Hi swtiley,

 You should be able to go to the last record and then use rcordcount

Cheers!
0
 
bman9111Commented:
if this is vb.net that u are using I would use ado.net anyways, but since u are not just use

rsDB.RecordCount
this will display the records retrieved from the database u are querying...
0
 
jcoutureCommented:
Steve,

One other thing to keep in mind is that if you use a "server side" cursor for the recordset, you will always get a -1 for recordcount.  If you use a "Client side" cursor, thent he .RecordCount property should return a valid number.  The servier side cursor is faster in that it doesn't return all the data to the user PC since it will be doing most of the processing, etc on the server.  The client side cursor brings all the data down to the users PC and does all the processing there.  Since all the data is retrieved to the users PC, the recordset knows how many records there are.  

Good luck.

JC
0
 
Arthur_WoodCommented:
in the case of the SQL that you show in your code:

cmdDB.CommandText = "SELECT count(*) FROM Exception_days where business_date between #" & Trim(Str(cmbMonth.SelectedIndex + 1)) & "/01/" & Trim(cmbYear.SelectedItem) & "# and #" & Trim(Str(cmbMonth.SelectedIndex + 1)) & "/" & Trim(Str(iDaysinMonth)) & "/" & Trim(cmbYear.SelectedItem) & "#"


you are absolutely GUARANTEED to get EXACTLY 1 record, every time you execute this SQL.  Is that the record count you are refering to, or are you asking about how to get the VALUE of the Count(*) that has been generated BY this SQL - that is the count of the number of rrecords in the table that satisfy the parameters of the query.  If that is what you are after then your code should read:

conDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\revolution\Optom.mdb"
        conDB.Open()
        cmdDB.ActiveConnection = conDB

        cmdDB.CommandText = "SELECT count(*) FROM Exception_days where business_date between #" & Trim(Str(cmbMonth.SelectedIndex + 1)) & "/01/" & Trim(cmbYear.SelectedItem) & "# and #" & Trim(Str(cmbMonth.SelectedIndex + 1)) & "/" & Trim(Str(iDaysinMonth)) & "/" & Trim(cmbYear.SelectedItem) & "#"

 rsDB = cmdDB.Execute
 MessageBox.Show("the number of Exception days between the supplied dates is " & rsDB.Fields(0).Value)

you DO NOT want to know the value of rsDB.RecordCount, but rather the contents of the single field in the SINGLE record that is returned (as I said, this SQL will ALWAYS return EXACTLY 1 record).

AW
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now