SQL and RecordSets 2

[seema165]

Hi,

I have a error in my code when I run it. In the following code, if the where statement doesn't return anything I get an error.

Here's the code:

while not objRecordSet.EOF
strSQL = "SELECT bhagnb FROM mvxjdta.staghe WHERE bhagnb = ('" & objRecordSet(0) &"'))"
objRS = objConnection.Execute(strSQL)
MsgBox objRecordSet(0), vbOKOnly, "1st"
MsgBox objRS(0), vbOKOnly, "2nd"
if (objRecordSet(0) = objRS(0) )Then
intRecordSet = objRS.Count
MsgBox intRecordSet, vbOKOnly, "Count Record"
End If
objRecordSet.MoveNext
Wend

The error occurs at 'MsgBox objRS(0), vbOKOnly, "2nd" ' and at 'if (objRecordSet(0) = objRS(0) )Then ' lines. What I want to do is, if the select statement doesn't bring back anything then I want to output a message (this is not in the above code)

Please help, this is a problem I have had for a while now.

Thanks.

 

[JerryKlmns]

I think that these

objRS = objConnection.Execute(strSQL)
intRecordSet = objRS.Count

should be

Set objRS = objConnection.Execute(strSQL)
intRecordSet = objRS.RecordCount

Check for empty recordset:
If objRS.EOF And objRS.EOF Then
MsgBox "No Records Found to Display"
End If

 

[seema165]

Hi JerryKlmns,

I've done Set objRS = objConnection.Execute(strSQL)

But I cannot change objRS.Count to objRS.RecordCount as it doesn't work with that, but I'm getting an error on that line: intRecordSet = objRS.Count

saying: Object doesn't support this property or method: 'objRS.Count'

I've tried it with objRS.RecordCount but I still get the error.

 

[JerryKlmns]

seema165,

Both DAO.Recordset and ADODB.Recordset have this property rs.RecordCount, which is a long value.

Dim intRecordSet As Long

 

[seema165]

Ok, I got it working with RecordCount.

Not sure why it didn't work the 1st time I tried it with that. Probably because I had to close my application and restart it.

Thanks for your help JerryKlmns

 

[BobRodes]

The reason that RecordCount didn't work in your first code is that the default cursortype, adForwardOnly, of the recordset doesn't support it. ForwardOnly recordsets ("firehose cursors") are very fast when you just want to run through them from top to bottom. Although you don't show how you got it working, I suspect that you changed the type of the cursor when you open the recordset.

HTH

Bob

 

[tsdragon]

Thanks for the explanation, Bob. It's nice to know WHY things work the way they do.

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[PCLewis]

If you really want to get the count of records then I would recommend splitting up into a count query and a select query. This will be especially noticeable if you have a large database on a remote machine. Whether it's Access, Oracle, MSSQL or anything.

When you use .recordcount, if supported, you will cause your processor to have to do some work and you will potentially cause extra network traffic as your system pulls down records to count them.

strSQL = "SELECT count(bhagnb) FROM mvxjdta.staghe WHERE bhagnb = ('" & objRecordSet(0) &"'))"

Will result in a recordset that always has one record (assuming the table exists of course). You can rely on the first record being a whole number.

Then, in your code, if the count is greater than 0, you can opt to go on and do something else if you need to.

Using this technique will see you future-proofing your code at the start and in my opinion it's a good habit to be in.

I'm, not preaching I hope but if you heed the advice, I think one day you will be thankful

 

[BobRodes]

<potentially cause extra network traffic as your system pulls down records to count them.

Meaning, in the case of a server-side recordset. I think PC's is a strong idea.

Tracy, for more info on cursors, check faq222-3670.

Bob