SQL and RecordSets 1

[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.

 

[FesterSXS]

try this...

[red]If NOT objRecordSet.EOF Then[/red]
  while not objRecordSet.EOF
    strSQL = "SELECT bhagnb FROM mvxjdta.staghe WHERE bhagnb = ('" & objRecordSet(0) &"'))"
     objRS = objConnection.Execute(strSQL)   

     [red]If NOT objRS.EOF Then[/red]       
       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
     [red]Else
         'display message here[/red]
     [red]End If[/red]
     objRecordSet.MoveNext
  Wend
[red]Else
'display message here
End If[/red]

Tony

Spirax-Sarco - steam traps control valves heat exchangers
Sun Villa - Luxury Florida accommodation

 

[seema165]

Hi Tony,

I'm getting error message saying: Object doesn't support this property or method: 'objRS.EOF'

 

[FesterSXS]

objRS = objConnection.Execute(strSQL)

should be

Set objRS = objConnection.Execute(strSQL)

Tony

Spirax-Sarco - steam traps control valves heat exchangers
Sun Villa - Luxury Florida accommodation

 

[seema165]

Hi Tony,

I'm getting error message saying: Object doesn't support this property or method: 'objRS.Count'

 

[FesterSXS]

try objRS.RecordCount instead

You might also need to specify a different cursortype in order to access that value otherwise you might get a value of -1

Tony

Spirax-Sarco - steam traps control valves heat exchangers
Sun Villa - Luxury Florida accommodation

 

[seema165]

It gives me the same error when I use objRS.RecordCount

 

[seema165]

Tony,

It works now with RecordCount. I think it didn't work before because I didn't restart my application.

Thanks for the help ;-)

 

[FesterSXS]

glad to help

Tony

Spirax-Sarco - steam traps control valves heat exchangers
Sun Villa - Luxury Florida accommodation