Reputation: 13844

VBScript: how to set values from recordset to string

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?

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.

Upvotes: 2

Answers (7)

Tester101

Reputation: 8172

Try This:


getOffice = objRS.getString

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

Upvotes: 4

Tester101

Reputation: 8172

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

Upvotes: 2

AnonJr

Reputation: 2757

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.

Upvotes: 0

phill

Reputation: 13844

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

Upvotes: 0

BQ.

Reputation: 9413

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.

Upvotes: 0