+ Start a Discussion
ClintHegneyClintHegney 

What character encoding is used for SObjects in the office toolkit??

I am having an issue when running a query of contacts for an account. When printing the results in my script, it bombs out on one of the contacts. Now this account is in Canada and the contact that it bombs out on looks like it has some non-english characters in the first name field. I was wondering if anyone has any ideas on how I would fix this? I am using the office toolkit in a Windows Script along with a helper Windows Script Component that I wrote.

Here is my code:

Code:
Dim sForce, sessionId, uid, pwd, objFSO, inFile, outFile, arLine, objConn, objRecSet, objContacts, objAccounts, sfHelper, objOwners
Dim idList(1)

Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConn = CreateObject("ADODB.Connection")
Set objRecSet = CreateObject("ADODB.Recordset")

pathTextFileDb = "C:\usr\winscript\TextDb\"

uid = WScript.Arguments.Item(0)
pwd = WScript.Arguments.Item(1)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set outFile = objFSO.OpenTextFile(WScript.Arguments.Item(3), ForWriting, True)
outFile.WriteLine """sfAccountId""," & _
 """sfAccountName""," & _
 """AccountOwnerId""," & _
 """AccountOwnerName""," & _
 """Contact_ID""," & _
 """FirstName""," & _
 """LastName""," & _
 """Email""," & _
 """MailingStreet""," & _
 """MailingCity""," & _
 """MailingState""," & _
 """MailingPostalCode""," & _
 """MailingCountry""," & _
 """Role__c""," & _
 """Contact_Status""," & _
 """Email_Opt_Out"""

Set sForce = CreateObject("SForceOfficeToolkit.SForceSession.1")
Set sfHelper = CreateObject("SfdcHelper.WSC")

sForce.Login uid,pwd,False
sessionId = sForce.SessionId

WScript.Echo sessionId & vbCrLf

objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & pathTextFileDb & ";" & _
 "Extended Properties=""text;HDR=YES;FMT=Delimited"""
 
objRecset.Open "SELECT * FROM " & WScript.Arguments.Item(2), objConn, adOpenStatic, adLockOptimistic, adCmdText

While Not objRecSet.EOF

 If objRecSet.Fields.Item("IsRmCust") <> "FALSE" Then
 
  'idList(0) = objRecSet.Fields.Item("Account_ID")
  'Set objAccounts = sForce.Retrieve("*", "Account", idList, False)
  Set oAcct = sfHelper.GetAccountById(sForce, objRecSet.Fields.Item("Account_ID").Value)
  
  'If objAccounts.Size = 1 Then
  'If oAcct Then
   
   'For Each oAcct In objAccounts
   
    WScript.StdOut.Write oAcct.Item("Name").Name & ": " & oAcct.Item("Name")
    
    Set oOwner = sfHelper.GetUserById(sForce, oAcct.Item("OwnerId").Value)
    'idList(0) = oAcct.Item("OwnerId").Value
    'Set objOwners = sForce.Retrieve("*", "User", idList, False)
    'For Each o In objOwners
    ' Set oOwner = o
    'Next
    
    qryText = "select Id, FirstName, LastName, Email, Role__c, Contact_Status__c, Email_Opt_Out__c " & _
     " from Contact where AccountId = '" & objRecSet.Fields.Item("Account_ID") & "' "
    Set objContacts = sForce.Query(qryText, False)
    
    WScript.StdOut.Write "    Found " & objContacts.Size & " matching contacts." & vbCrLf
    
    If objContacts.Size > 0 Then
    
     For Each oCntct in objContacts
     
      outFile.WriteLine """" & objRecSet.Fields.Item("Account_ID") & """," & _
       """" & oAcct.Item("Name").Value & """," & _
       """" & oAcct.Item("OwnerId").Value & """," & _
       """" & oOwner.Item("FirstName").Value & " " & oOwner.Item("LastName").Value & """," & _
       """" & oCntct.Item("Id").Value & """," & _
       """" & oCntct.Item("FirstName").Value & """," & _
       """" & oCntct.Item("LastName").Value & """," & _
       """" & oCntct.Item("Email").Value & """," & _
       """" & oAcct.Item("ShippingStreet").Value & """," & _
       """" & oAcct.Item("ShippingCity").Value & """," & _
       """" & oAcct.Item("ShippingState").Value & """," & _
       """" & oAcct.Item("ShippingPostalCode").Value & """," & _
       """" & oAcct.Item("ShippingCountry").Value & """," & _
       """" & oCntct.Item("Role__c").Value & """," & _
       """" & oCntct.Item("Contact_Status__c").Value & """," & _
       """" & oCntct.Item("Email_Opt_Out__c").Value & """"
     
     Next
    
    End If    
   
   'Next
   
  'End If
  
 End If
 
 objRecSet.MoveNext

Wend

WScript.StdOut.Write vbCrLf & vbCrLf & "Press [Enter] to continue..."
Wscript.StdIn.ReadLine

'"""Acct_No""," & _
'"""ELAcctName""," & _
'"""" & objRecSet.Fields.Item("Account_Number") & """," & _
'"""" & objRecSet.Fields.Item("Account_Name") & """," & _
 
Here is the error I am getting:
 
Code:
Name: [account name]    Found 8 matching contacts.
C:\usr\winscript\Salesforce.vbs(83, 7) Microsoft VBScript runtime error: Invalid procedure call or argument
 
gsickalgsickal
The problem is in your lines
Set objContacts = sForce.Query(qryText, False)
If objContacts.Size > 0 Then
For Each oCntct in objContacts
^^^^^^^^^^
the query method doesn't create an array of objects you can just iterate using the for...each construct: you need to do this the way that is shown in the vb samples. See the function getContact() in the enterprise vb samples, the code in there that shows how to do this is as follows:

Dim qr As sforce.QueryResult = binding.query("Select Id, FirstName, LastName, AccountId from Contact Where not AccountId = null")
.....
For i As Integer = 0 To qr.records.GetUpperBound(0)
Dim contact As sforce.Contact = CType(qr.records(i), sforce.Contact)
Console.WriteLine((i + 1) & ": " & contact.FirstName & " " & contact.LastName)
Next
ClintHegneyClintHegney
Well I found out to fix the issue. I had to set the Tristate mode on my output TextStream object to TristateTrue (-1). Now the record with the special characters is printed because the TextStream object is able to output UTF-8.

Code:

Set outFile = objFSO.OpenTextFile(WScript.Arguments.Item(3), ForWriting, True, TristateTrue)

 

 

ClintHegneyClintHegney
Actually, in VBScript, the For Each construct does function.
SuperfellSuperfell
The office toolkit does support iterating over queryresults (it even queryMores for you in the background).