Hi. I’m reposting this from the EI forum:
I cannot get the column values from a result set. I think the result set is not coming back correct. I can connect to the remote SQL Server, because I can get the column names. After executing the query, IsResultSetAvailable is set to True. But when I try to GetValue, it just returns False, instead of the correct column value (it should be returning 0 in the first column and “Work in Progress” for the second column.
Here is the table:
Code Status
0 Work in Progress
1 Pro Bono
2 Completed
3 Cancelled
4 On Hold
10 New Business
11 Lost Business
12 Assignment Awarded
My the code:
Sub Initialize
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Set qry.Connection = con
Set result.Query = qry
Call con.ConnectTo("WIP SQL DB", user, pwd)
Dim fields As Variant
fields = con.ListFields("Status")
Forall fld In fields
Print fld
End Forall
qry.SQL = "SELECT * FROM Status"
Dim numrows As Variant
Call result.Execute
Call result.FirstRow
Print "IsResultSetAvailable: " & result.IsResultSetAvailable
Print "IsBeginOfData: " & result.IsBeginOfData
Print "IsEndOfData: " & result.IsEndOfData
Print result.NumColumns, result.CurrentRow, result.NumRows
Print result.CurrentRow, result.GetValue(1), result.GetValue(2)
exitsub:
Call result.Close
Call con.Disconnect
Exit Sub
errorhandler:
Print con.GetError & ": " & con.GetExtendedErrorMessage(con.GetError)
Resume exitsub
End Sub
Finally, the output:
Code
Status
IsResultSetAvailable: True
IsBeginOfData: True
IsEndOfData: False
result.NumColumns: 2
result.CurrentRow: 1
result.NumRows: -1
result.CurrentRow: 1
result.GetValue(1): False
result.GetValue(2): False
That last line is the problem. It should be: 1 0 Work in Progress.
Why can’t I get the column values? Why is only one row returned when there should be 8? And why is NumRows set to -1.
I don’t get any errors when running this code and I’ve tried different lines instead of Call result.FirstRow (like result.CurrentRow=1), but with the same results.
Thanks for your help.
-Jeff
Subject: ANSWER: ODBC Help: Can’t get column values
There was nothing wrong on the Lotus side (whew!). The problem was that the columns in the SQL Server were defined as some wierd data type. All the problems were fixed once they were changed to variable length text.
One question I have left is what the heck is the NumRows property? It doesn’t tell you the number of rows until after you’ve been through all of them. How do you make it useful.
Here’s my current code
Sub Initialize
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Print "con.GetLsdoMasterRevision: " & con.GetLsdoMasterRevision
Const dsn = "WIP SQL DB"
Const user = "wip"
Const pwd= "I'm not telling you"
If Not con.ConnectTo(dsn, user, pwd) Then
Print "Could not connect to: " & dsn
Print con.GetError & ": " & con.GetExtendedErrorMessage
Goto ExitSub
End If
If Not con.IsConnected Then
Print "Not connected to: " & dsn
Print con.GetError & ": " & con.GetExtendedErrorMessage
Goto ExitSub
End If
Dim intErr As Integer
Print "con.DataSourceName: " & con.DataSourceName
Print "con.IsConnected: " & con.IsConnected
intErr = con.GetError
Print "con.GetError: " & intErr, con.GetExtendedErrorMessage
Const tbl = "Status"
Dim fields As Variant, strQuery As String
strQuery = "SELECT"
fields = con.ListFields(tbl)
Forall fld In fields
strQuery = strQuery & " " & fld & ","
End Forall
strQuery = Left$(strQuery, Len(strQuery)-1) & " FROM " & tbl
Set result.Query = qry
Set qry.Connection = con
qry.SQL = strQuery
If Not result.Execute Then
Print result.GetError & ": " & result.GetExtendedErrorMessage
Print qry.GetError & ": " & qry.GetExtendedErrorMessage
Print "Could not execute strQuery: " & qry.SQL
Goto ExitSub
End If
Print "qry.SQL: " & qry.SQL
intErr = result.GetError
Print "result.GetError: " & intErr, result.GetExtendedErrorMessage
intErr = qry.GetError
Print "qry.GetError: " & intErr, result.GetExtendedErrorMessage
Print "IsResultSetAvailable: " & result.IsResultSetAvailable
Print "IsBeginOfData: " & result.IsBeginOfData
Print "IsEndOfData: " & result.IsEndOfData
Print "result.NumColumns: " & result.NumColumns
Print "result.CurrentRow: " & result.CurrentRow
Print "result.NumRows: " & result.NumRows
If result.IsResultSetAvailable Then
Do
Dim msg As String
result.NextRow
msg = result.CurrentRow & ":" & Chr$(9)
Dim i As Integer
For i = 1 To result.NumColumns
msg = msg & result.GetValue(i) & "," & Chr$(9)
Next i
Print msg
Loop Until result.IsEndOfData
End If
Print "result.NumColumns: " & result.NumColumns
Print "result.CurrentRow: " & result.CurrentRow
Print "result.NumRows: " & result.NumRows
ExitSub:
Stop
Call result.Close
Call con.Disconnect
Exit Sub
errorhandler:
Print con.GetError & ": " & con.GetExtendedErrorMessage(con.GetError)
Resume ExitSub
End Sub
And the output
con.GetLsdoMasterRevision: ODBC_MASTER_REVISION=6.0 - Sep 27 2007
con.DataSourceName: WIP SQL DB
con.IsConnected: True
con.GetError: 500
qry.SQL: SELECT Code, Status FROM Status
result.GetError: 500 NO ERROR
qry.GetError: 500 NO ERROR
IsResultSetAvailable: True
IsBeginOfData: True
IsEndOfData: False
result.NumColumns: 2
result.CurrentRow: 1
result.NumRows: -1
0, Work in Progress,
1, Pro Bono,
2, Completed,
3, Cancelled,
4, On Hold,
10, New Business,
11, Lost Business,
12, Assignment Awarded,
result.NumColumns: 2
result.CurrentRow: 8
result.NumRows: 8
Notice how NumRows changes from -1 to 8. That’s wierd and seems wrong. How do I use that?
Thanks,
-Jeff