+ Start a Discussion
dgrissendgrissen 

Need help running an update call in VB

Hi,

I am writing a sub that queries data (specifically, an aggregate sql query that returns the sum of total bookings grouped by the salesforce user id).  This query returns data from a LOCAL machine.

I can't figure out how to put existing arrays of data with the sf.com ID already known into a data dictionary and then update into salesforce.com  I have plenty of experience downloading data from sf.com, but am having difficulties with the the update call.  The code is below, any help is greatly appreciated.

Thanks,

Darrell

I am getting an error message that says 'Type Mismatch' on this line:

Set sobjects(i) = dicObjects.Items(i)

 

Public Sub Update()

Dim cnxOperations As ADODB.Connection

Call ConnectOperations(cnxOperations)
Call LoginSalesforce

Dim rstMTD As ADODB.Recordset
Dim fldObject As ADODB.Fields
Dim cmdMTD As ADODB.Command
Dim strMTDsql As String

Dim qr As QueryResultSet
Dim sobj As SObject

strMTDsql = "SELECT tblBookings.SalesRepID, Sum(tblBookings.TotalDeal) AS Total FROM tblBookings INNER JOIN tblBusinessDayCalendar ON tblBookings.BookingDate = tblBusinessDayCalendar.DateID WHERE (((tblBusinessDayCalendar.MonthNumber) = " & Format(Date, "m") & ") And ((tblBusinessDayCalendar.YearNumber) = " & Format(Date, "yyyy") & "))GROUP BY tblBookings.SalesRepID;"
Debug.Print strMTDsql

Set cmdMTD = New ADODB.Command
Set cmdMTD.ActiveConnection = cnxOperations
cmdMTD.CommandText = strMTDsql
cmdMTD.CommandTimeout = 10
        
'Executing command to retrieve recordset
Set rstMTD = New ADODB.Recordset
Set rstMTD = cmdMTD.Execute
Set fldObject = rstMTD.Fields

Dim Count As Integer
Dim y As Integer
Dim id() As String
Dim dicObjects As New Dictionary

With rstMTD
Do While Not .EOF
dicObjects.Add rstMTD.Fields("SalesRepID").Value, fldObject
.MoveNext
Loop
End With

'updating via sforce API
Dim sobjects() As SObject
Dim i As Integer
ReDim sobjects(dicObjects.Count) As SObject
For i = LBound(sobjects) To UBound(sobjects)
    Set sobjects(i) = dicObjects.Items(i)
Next


sfdc.Update sobjects, False

MsgBox "Done"


End Sub

SuperfellSuperfell
You seem to be trying to assign an ADO.Fields collection object into an SObject. Not sure what you're intent was, but they are not interchangable.
dgrissendgrissen

Any advice on how to transform a ADO recordset into a format which can be used to update records in salesforce.com?  Code samples?

 

Thanks!

dgrissendgrissen

Second attempt at using an update call.  This time I basically just copied the update code from the foghorn_test.xls example.  The only modification I made was the source of the data for updating.

The line in red is where it keeps tripping up, the error says 'Run time error 424 - object required'

I'd appreciate any advice...

Thanks,

Darrell


'Update
Public Sub RunUpdate()


Dim cnxOperations As ADODB.Connection

Call ConnectOperations(cnxOperations)
Call LoginSalesforce

Dim rstMTD As ADODB.Recordset
Dim cmdMTD As ADODB.Command
Dim strMTDsql As String

Dim qr As QueryResultSet

strMTDsql = "SELECT tblBookings.SalesRepID, Sum(tblBookings.TotalDeal) AS Total FROM tblBookings INNER JOIN tblBusinessDayCalendar ON tblBookings.BookingDate = tblBusinessDayCalendar.DateID WHERE (((tblBusinessDayCalendar.MonthNumber) = " & Format(Date, "m") & ") And ((tblBusinessDayCalendar.YearNumber) = " & Format(Date, "yyyy") & "))GROUP BY tblBookings.SalesRepID;"
Debug.Print strMTDsql

Set cmdMTD = New ADODB.Command
Set cmdMTD.ActiveConnection = cnxOperations
cmdMTD.CommandText = strMTDsql
cmdMTD.CommandTimeout = 10
        
'Executing command to retrieve recordset
Set rstMTD = New ADODB.Recordset
Set rstMTD = cmdMTD.Execute

Dim Count As Integer
Dim idList() As String
Dim sngNetBookingsValue() As Single
Dim x As Integer

With rstMTD
Do While Not .EOF
Count = Count + 1
.MoveNext
Loop

.MoveFirst
ReDim idList(1 To Count) As String
ReDim sngNetBookingsValue(1 To Count) As Single

'Get the ids of the objects to update
For x = 1 To Count
idList(x) = !SalesRepID
sngNetBookingsValue(x) = !Total
Debug.Print idList(x)
Debug.Print sngNetBookingsValue(x)

.MoveNext
Next x

End With

    'sf.com code
    Dim sobj As SObject
    Dim i As Integer
    Dim j As Integer
    Dim fieldList As String
   
    'Get the field list for the retrieve call
    fieldList = "Id, MTDNetBookings__c"
   
    'Retrieve the server edition of the list
    Dim qrs As QueryResultSet
    Set qrs = sfdc.Retrieve(fieldList, "User", idList, False)
      
    'Put the list into a dictionary keyed on id for easy random access
    Dim objects As New Dictionary
    For Each sobj In qrs
        objects.Add sobj.Item("Id").Value, sobj
    Next
   
    'Loop through the values updating the dictionary
    For i = 1 To Count
        If idList(i) <> "" Then
            Set sobj = objects(idList(i))
                If sobj.Item("MTDNetBookings__c").Updateable Then
                      sobj.Item("MTDNetBookings__c") = sngNetBookingsValue(i)
                End If
        End If
        'sObj.Update
        DoEvents
    Next
   
    Dim sobjects() As SObject
    ReDim sobjects(objects.Count - 1) As SObject
    For i = LBound(sobjects) To UBound(sobjects)
        Set sobjects(i) = objects.Items(i)
    Next
   
    sfdc.Update sobjects, False
   
MsgBox "Done"

End Sub