VB.NET • Make Secondary Data Sources Do Double or Triple Duty

Listing 3. It's common practice to populate dropdown and ordinary listboxes with values and names from a secondary data source (database query or Web service method) when the form opens, but declarative methods fall short when you need to populate additional textboxes based on the listbox selection. The NWOrdersWSProject's ProductID_OnAfterChange event handler inserts the UnitPrice value from a third column or element of the data source (see Figure 4). You can use a fourth (QuantityPerUnit) data source column or element to populate a SKU expression box with the appropriate text. An expression box is the only control that displays values not included in the main data source.

<InfoPathEventHandler(MatchPath:= _
     "/dfs:myFields/dfs:dataFields/" & _
     "s0:GetOrderSPResponse/ " & _
     "s0:GetOrderSPResult/s0:OrderDetails/ " & _
     "s0:OrderDetail/s0:ProductID", _
     EventType:=InfoPathEventType.OnAfterChange)> _
     Public Sub ProductID_OnAfterChange(ByVal e _
     As DataDOMEvent)
     If e.IsUndoRedo Or e.Operation = "Delete" Then
          Return
     End If
     Try
          'Add product's UnitPrice to Price textbox
          Dim strProdID As String = _
               e.NewValue.ToString
          Dim objProds As DataObject
          objProds = thisXDocument.DataObjects _
               ("GetProductsDetailed")
          Dim domProds As IXMLDOMDocument = _
               objProds.DOM
          'Form-scope the following three objects and
          'initialize/load them in the single 
          'OnSwitchView event.
          'This approach is for readability only
          Dim strXML As String = domProds.xml
          Dim docProds As New XmlDocument
          docProds.LoadXml(strXML)
          strXML = ""
          Dim lstProdID As XmlNodeList = _
               docProds.GetElementsByTagName("prodID")
          Dim lstPrice As XmlNodeList = _
               docProds.GetElementsByTagName( _
               "prodPrice")
          Dim intProds As Integer = lstProdID.Count
          Dim intCtr As Integer
          Dim nodProdID As XmlNode
          Dim blnFoundID As Boolean
          For intCtr = 0 To lstProdID.Count - 1
               'Find the element with the prodID value
               nodProdID = lstProdID.Item(intCtr)
               If nodProdID.InnerText = strProdID Then
                    blnFoundID = True
                    Exit For
               End If
          Next intCtr
          If blnFoundID Then
               Dim strPrice As String = _
                    lstPrice.Item(intCtr).InnerText
               'Get the grandparent of Source
               Dim nodRow As IXMLDOMNodeList = _
                    e.Source.parentNode.parentNode. _
                    selectNodes("*")
               If Not nodRow.Item(2) Is Nothing Then
                    'Insert the UnitPrice
                    nodRow.Item(2).text = strPrice
               End If
          End If
     Catch excProd As Exception
          thisXDocument.UI.Alert(excProd.Message + _
               vbCrLf + excProd.StackTrace)
     End Try
End Sub