Joining and Calculating using ArcObjects with vb.net

Question

I'm trying to join an excel table to a shapefile and then calculating those fields using vb.net. The code I have written has no errors, but won't work when I try it in ArcGIS 10.1 and I'm out of ideas.

Can anyone find an error in my code or direct me to other sources?

ModuleQ3 Code:

Public Sub JoinCalc(ByVal excelXLS As String, ByVal fromExcelTable As String, ByVal toFeatureClass As String, ByVal toField As String, ByVal fromField As String, ByVal calculateField As String, ByVal calculatorExpression As String)
    Dim pMxDoc As IMxDocument
    Dim pMap As IMap
    Dim pFeatureLayer As IFeatureLayer
    Dim pFeatureClass As IFeatureClass
    Dim featureIndex As Integer

    pMxDoc = My.ArcMap.Application.Document
    pMap = pMxDoc.FocusMap

    featureIndex = FindLayer(toFeatureClass)

    If featureIndex = -1 Then
        MsgBox("The join feature class is not in the current map document.")
        Exit Sub
    End If

    pFeatureLayer = pMap.Layer(featureIndex)
    pFeatureClass = pFeatureLayer.FeatureClass

    Dim pWorkspaceFactory As IWorkspaceFactory
    pWorkspaceFactory = New ExcelWorkspaceFactory

    'open excel workbook 
    Dim pFworkspace As IFeatureWorkspace
    pFworkspace = pWorkspaceFactory.OpenFromFile(excelXLS, 0)

    'open excel table 
    Dim pFromTable As ITable
    pFromTable = pFworkspace.OpenTable(fromExcelTable)

    'Join the table to the feature class. 
    Dim pMemRelFact As IMemoryRelationshipClassFactory
    Dim pRelClass As IRelationshipClass
    Dim pDispRC As IDisplayRelationshipClass

    ' Create a memory relationship class. 
    pMemRelFact = New MemoryRelationshipClassFactory
    pRelClass = pMemRelFact.Open("Table-Layer", pFromTable, fromField, pFeatureClass, toField, "FeatureClassName", "TableName", ESRI.ArcGIS.Geodatabase.esriRelCardinality.esriRelCardinalityOneToOne)

    ' Perform a join. 
    pDispRC = pFeatureLayer
    pDispRC.DisplayRelationshipClass(pRelClass, ESRI.ArcGIS.Geodatabase.esriJoinType.esriLeftInnerJoin)

    MsgBox("The join is complete")

    Dim pDisplayTable As IDisplayTable
    pDisplayTable = pFeatureLayer

    Dim pFields As IFields
    pFields = pFeatureClass.Fields

    Dim fieldIndex As Integer
    fieldIndex = pFields.FindField(calculateField)

    If fieldIndex <> -1 Then
        Dim pCursor As ICursor
        pCursor = pDisplayTable.SearchDisplayTable(Nothing, True)

        Dim pCalculator As ICalculator
        pCalculator = New Calculator
        With pCalculator
            .Cursor = pCursor
            .Expression = calculatorExpression
            .Field = calculateField
        End With

        pCalculator.Calculate()

        MsgBox("It should be calculated.")

    Else
        MsgBox("The field does not exist")
        Exit Sub
    End If
End Sub

These are the parameters that I am passing through:

ModuleQ3.JoinCalc("ONT_Climate_Normals.xlsx", "ONT_Annual", "ONT_Stations_Shape", "CLIMATE_ID", "CLIMATE_ID", "TotalAnnua", "NORMAL_52 + NORMAL_54")

Answer 1

I think the error maybe your calculateField. When you join tables you get a qualified field name this is:

Tablename.fieldname

So in your case it will be the FeatureClass name and the field name you are calculating into and in your code I think this would be:

ONT_Stations_Shape.TotalAnnua

So you need to be passing in as the CalculateField parameter "ONT_Stations_Shape.TotalAnnua"