I am trying to print the selected value from a query in VB6 this is my code
dim query as string
dim stsstring as string
dim rs as adobd.recordset
query = "select status from x where y='"& randomString &"'"
set rs = mainmodule.dbutils.dbconnection.connection.execute(query)
set rs = nothing
stsstring = rs.fields("status")
msgbox stsstring
I get error here
stsstring = rs.fields("status")
Object variable or with block variable not set
Thanks in advance!
You've set rs to nothing before trying to read the status field from it. Re-order your code to read:
'...
stsstring = rs.fields("status")
set rs = nothing
msgbox stsstring
Both of the below work, but to be completely on the safe side:
set rs = mainmodule.dbutils.dbconnection.connection.execute(query)
if not (rs.BOF And rs.EOF) then
rs.MoveFirst
Do While Not rs.EOF
If Not IsNull(rs("status").value)
stsstring = rs("status").value
Debug.Print stsstring
Else
Debug.Print "Column 'status' is NULL."
End If
rs.MoveNext
Loop
end if
If Not rs Is Nothing Then
If rs.State > adStateClosed Then
Call rs.Close()
End If
Set rs = Nothing
End If
An easy way to circumvent the If Not IsNull() condition for strings is to use stsstring = vbNullString & rs("status").value
