Print selected value from SQL to textbox VB6

Admin 1
Viewed 90

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!

vb6 sql
2 Answers

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
user_136428030

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

user_66261830
Related