ADO Recordset to Msgbox

[Guest]

How do I take the contents of a ADO recordset and show them in a messagebox.

thanks for any help
Paul

 

[Bob Phillips]

' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
Else
MsgBox "No records returned.", vbCritical
End If



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks Bob,
But having trouble with this as I never know how many records could
potentially be returned in the recordset so I can't define the line

MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)

yet my attempts to cycle through the array are failing miserably, so far
looking like this

If Not rs.EOF Then
Ary = rs.GetRows
For varX = LBound(Ary) To UBound(Ary)
strMsg = strMsg & Ary(varX, 0) & ", "
Next varX
strMsg = Mid(strMsg, 1, Len(strMsg) - 2)
MsgBox (strMsg)
Else
MsgBox "No records returned.", vbCritical
End If

But this isn't going through the array and working either!

Paul

 

[RB Smissaert]

Try this:

Sub test()

Dim i As Long
Dim Ary
Dim strMsg As String

If Not rs.EOF Then
Ary = rs.GetRows
For i = 0 To UBound(Ary, 2)
If i = 0 Then
strMsg = Ary(0, i)
Else
strMsg = strMsg & ", " & Ary(0, i)
End If
Next i
MsgBox strMsg, , UBound(Ary, 2) + 1 & " records"
Else
MsgBox "No records returned.", vbCritical
End If

End Sub


The essential thing is that the array obtained from rs.GetRows has the
dimensions oppposite to what you would expect.


RBS

 

[Guest]

Thank you, that works perfectly

cheers,
Paul

Try this:

Sub test()

Dim i As Long
Dim Ary
Dim strMsg As String

If Not rs.EOF Then
Ary = rs.GetRows
For i = 0 To UBound(Ary, 2)
If i = 0 Then
strMsg = Ary(0, i)
Else
strMsg = strMsg & ", " & Ary(0, i)
End If
Next i
MsgBox strMsg, , UBound(Ary, 2) + 1 & " records"
Else
MsgBox "No records returned.", vbCritical
End If

End Sub


The essential thing is that the array obtained from rs.GetRows has the
dimensions oppposite to what you would expect.


RBS