Query via VBS returns -1 records 2

[benmillus]

Attempting to:
>Query Informix via VBScript

Problem:
>Query always returns -1 records

Analysis:
>Manually queried using MS Query and the same DSN file. Query successfully returns 1 record.
>Changed the DSN and query to Oracle DB and AS400 DB, both work fine.

My code:

"CONST adOpenStatic = 3, adLockReadOnly = 1, adCmdText = &H0001
CONN_STR_01 = "FILEDSN=" & "C:\...\DSN\" & "xxx.dsn;"
uid_01 = "xxx"
pwd_01 = "xxx"
sql_01 = "SELECT * FROM informix.guest t1 WHERE t1.guest_id = 100;"

Function OpenRecordSet(cn_str, uid, pwd, sql)
Dim cn, rs, dbCollection(1)
Set cn = CreateObject("ADODB.Connection")
cn.Open cn_str, uid, pwd
Set dbCollection(0) = cn
Set rs = CreateObject("ADODB.Recordset")
rs.Open sql, cn, 3, 1, 1
Set dbCollection(1) = rs
OpenRecordSet = dbCollection
Set rs = nothing
Set cn = nothing
End Function

dbCollection = OpenRecordSet(CONN_STR_01, uid_01, pwd_01, sql_01)
Set Conn = dbCollection(0)
Set Rs = dbCollection(1)

maxRecord = Rs.RecordCount - 1
maxField = Rs.Fields.Count - 1

msgbox "Record count SQL: " & Rs.RecordCount

' debug: begin
For ii = 0 to maxRecord
For yy = 0 to maxField
msgbox Rs_00.Fields(yy).Name & " (" & yy & "): " & Rs_00.Fields(yy).Value
Next
Next
' debug: end

 

[dm4ever]

I've found RecordCount to be unreliable.

instead for your loop do something like

do until = Rs.EOF
code to display values or whatever you want
loop

if you want to know the number of records

i = 0

do until = Rs.EOF
your code
i = i + 1
loop

wscript.echo "Record Count: " & i

 

[Golom]

The default for most ADO recordsets is a server side cursor and often a server side cursor doesn't properly return a recordcount. Try

Set rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
rs.Open sql, cn, 3, 1, 1

 

[benmillus]

Thanks so much for your responses. The later worked great. Just needed to add one line of code, "rs.CursorLocation = 3", to get it working.

To be honest I did not try dm4ever's suggest. But it is appreciated. Especially since it helps us with best practices.