VBScript: how to set values from recordset to string

Question

This is probably a beginner question, but how do you set a recordset to a string variable?

Here is my code:

Function getOffice (strname, uname) 

strEmail = uname
WScript.Echo "email: " & strEmail 
Dim objRoot : Set objRoot = GetObject("LDAP://RootDSE")
Dim objDomain : Set objDomain = GetObject("LDAP://" & objRoot.Get("defaultNamingContext"))
Dim cn : Set cn = CreateObject("ADODB.Connection")
Dim cmd : Set cmd = CreateObject("ADODB.Command")
cn.Provider = "ADsDSOObject"
cn.Open "Active Directory Provider"
Set cmd.ActiveConnection = cn

cmd.CommandText = "SELECT physicalDeliveryOfficeName FROM '" & objDomain.ADsPath & "' WHERE mail='" & strEmail & "'"
cmd.Properties("Page Size") = 1
cmd.Properties("Timeout") = 300
cmd.Properties("Searchscope") = ADS_SCOPE_SUBTREE

Dim objRS : Set objRS = cmd.Execute

  WScript.Echo objRS.Fields(0)

Set cmd = Nothing
Set cn = Nothing
Set objDomain = Nothing
Set objRoot = Nothing

Dim arStore 

Set getOffice = objRS.Fields(0)

Set objRS = Nothing

End function 

When I try to run the function, it throws an error "vbscript runtime error: Type mismatch" I presume this means it can't set the string variable with a recordset value.

How do I fix this problem?

Answer 1

Cstr(objRS.Fields(0))

Answer 2

Try changing this:

Set getOffice = objRS.Fields(0)

to this:

getOffice = objRS.Fields(0)

Answer 3

It's been my experience that the various ways of returning data from a DB call are often times very dependent on the method/driver used to access the data (e.g. ODBC, ADO, ADO.NET, ODP.NET, OleDB, etc.) Some need ToString(), GetString(), a cast, or some other variant of that.

Answer 4

I just tried

if IsNull(objRS.Fields(0).Value) = TRUE then getOFfice = "noAD" else getOFfice = objRS.Fields(0).VAlue end if

And that throws a different error ADODB.Field: Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

Answer 5

Just tried the following with .Item(0)

If IsNull(objRS.Fields.Item(0)) = TRUE Then 
 getOffice = "noAD"
Else 
getOffice = objRS.Fields(0).Item(0)
WScript.Echo getOffice 
End If

I got a different error saying Microsoft VBScript runtime error: Object doesn't support this property or method: 'objRS.Fields(...).Item'

I think .Value is more on the right track?

Answer 6

I just ended up adding "On Error Resume Next" to the top and it just skips the null errors.

although I wish there was an easier way to handle NULL values in vbscript.

thanks for all your help

Answer 7

Are you sure your recordset isn't empty? Normally you'd want to check first before you started doing anything. Not knowing anything else about what it needs to do, I might suggest something like this:

Function getOffice (strname, uname) 

strEmail = uname
WScript.Echo "email: " & strEmail 
Dim objRoot : Set objRoot = GetObject("LDAP://RootDSE")
Dim objDomain : Set objDomain = GetObject("LDAP://" & objRoot.Get("defaultNamingContext"))
Dim cn : Set cn = CreateObject("ADODB.Connection")
Dim cmd : Set cmd = CreateObject("ADODB.Command")
cn.Provider = "ADsDSOObject"
cn.Open "Active Directory Provider"
Set cmd.ActiveConnection = cn

cmd.CommandText = "SELECT physicalDeliveryOfficeName FROM '" & objDomain.ADsPath & "' WHERE mail='" & strEmail & "'"
cmd.Properties("Page Size") = 1
cmd.Properties("Timeout") = 300
cmd.Properties("Searchscope") = ADS_SCOPE_SUBTREE

Dim objRS : Set objRS = cmd.Execute

If Not objRS.BOF Then objRS.Move First
If Not objRS.EOF Then 
  If Not IsNull(objRS.Fields(0)) and objRS.Fields(0) <> "" Then  WScript.Echo cStr(objRS.Fields(0))
End If

Set cmd = Nothing
Set cn = Nothing
Set objDomain = Nothing
Set objRoot = Nothing

Dim arStore 

Set getOffice = objRS.Fields(0)

Set objRS = Nothing

End function

Obviously there are many ways of checking for an empty recordset, and checking for nulls, etc. but here's one way that may work.

Answer 8

Set is only used for objects, it cannot be used on simple variables like strings.
Try this: (it also makes sure the recordset is not empty)

If objRS.RecordCount <> 0 Then
  getOffice = CStr(objRS.Fields(0))
Else
  getOffice = ""
End If

Answer 9

Try This:

getOffice = objRS.getString

This will return the entire recordset as a tab delimited string.