I inherited support for an application when the previous develper left the company. I needed to restore the infopath project, because it is stored separately from the .xsn form.
Based on a lot of feedback from this forum, I was able to put together the solution that works for what our button click event was supposed to do. It performs very well, and minimizes the network traffic to the database.
Here is the code for the form. I'm not sure how to attach, so I'm sorry it's so long.
You'll also notice a debugging technique that I used to figure out where the code wasn't working properly.
Public Sub TSN_Lookup_Clicked(ByVal sender As Object, ByVal e As ClickedEventArgs)
Dim mainNavigator As XPathNavigator
Dim resultNavigator As XPathNavigator
Dim searchField As XPathNavigator
'
Dim resultSetPath As String = "/dfs:myFields/dfs:dataFields/d:SAGE_SP_COA_TSN"
Dim rootXMLPath As String = "/my:main/my:Material_Section/my:TSN_Material_Section"
Dim sageTSNPath As String = "/my:main/my:SAGE/my:SAGE_SP_Columns/my:SAGE_Test_Substance_Number"
Dim sageTSNFoundPath As String = "/my:main/my:SAGE/my:SAGE_SP_Columns/my:SAGE_TSN_MATERIAL_FOUND"
Dim sageShowMatlPanelPath As String = "/my:main/my:Show_Hide_Form/my:Show_Material_Panel"
'
Dim queryConnection As AdoQueryConnection
Dim origCommand As String
Dim whereClause As String
'
Dim TSNDataSource As DataSource
Dim myNamespace As String
Dim dfsNamespace As String
'
Dim DebugLine As Integer
Dim SAGE_Test_Substance_Number As String
Dim NewValue As String
'
' Code example taken from
' http://www.infopathdev.com/forums/t/8940.aspx?PageIndex=2
'
' Run a query to get the information based on the SAGE_Test_Substance_Number
' /my:main/my:SAGE/my:SAGE_SP_Columns/my:SAGE_Test_Substance_Number
'
' SELECT CABINET AS TSN_Cabinet
' ,DED AS TSN_DED
' ,F_MAT_GROUP_CLASS AS TSN_DED_GROUP
' ,MAT_NAME AS TSN_Material
' ,MAT_COMP AS TSN_Material_Comp
' ,MAT_FAMILY AS TSN_Material_Family
' ,MAT_ID AS TSN_Material_ID
' ,F_MAT_GROUP AS TSN_Parent
' ,GIFAP_CODE AS TSN_Material_Type
'
' from dbo.SAGE_SP_COA_TSN
' WHERE TSN_ID = /my:main/my:SAGE/my:SAGE_SP_Columns/my:SAGE_Test_Substance_Number
'
Try
DebugLine = 10
mainNavigator = Me.CreateNavigator()
'
' get the value of the Test Substance number for the where clause
'
DebugLine = 20
myNamespace = NamespaceManager.LookupNamespace("my")
dfsNamespace = NamespaceManager.LookupNamespace("dfs")
DebugLine = 30
searchField = mainNavigator.SelectSingleNode(sageTSNPath, NamespaceManager)
DebugLine = 40
SAGE_Test_Substance_Number = searchField.Value
DebugLine = 50
whereClause = " WHERE (TSN_ID = '" + SAGE_Test_Substance_Number.Replace("'", "''") + "')"
'
' get the original command (we'll need to set it back)
'
DebugLine = 60
TSNDataSource = Me.DataSources("SAGE_SP_COA_TSN")
queryConnection = TSNDataSource.QueryConnection
DebugLine = 70
origCommand = queryConnection.Command
'
' modify the query to use the where clause
'
DebugLine = 80
queryConnection.Command = origCommand + whereClause
'
' now we run the query
'
DebugLine = 90
queryConnection.Execute()
'
' Reset the query back to its normal value
'
DebugLine = 100
queryConnection.Command = origCommand
'
' Create the XPathNavigator for the resultset
' resultNavigator.ToString should reveal the entire XML structure.
'
DebugLine = 110
resultNavigator = TSNDataSource.CreateNavigator()
'
' Find the row result node
'
DebugLine = 120
'
' Fill in the following lookup fields
' /my:main/my:Material_Section/my:TSN_Material_Section/my:TSN_Cabinet
' /my:main/my:Material_Section/my:TSN_Material_Section/my:TSN_DED
' /my:main/my:Material_Section/my:TSN_Material_Section/my:TSN_DED_GROUP
' /my:main/my:Material_Section/my:TSN_Material_Section/my:TSN_Material
' /my:main/my:Material_Section/my:TSN_Material_Section/my:TSN_Material_Comp
' /my:main/my:Material_Section/my:TSN_Material_Section/my:TSN_Material_Family
' /my:main/my:Material_Section/my:TSN_Material_Section/my:TSN_Material_ID
' /my:main/my:Material_Section/my:TSN_Material_Section/my:TSN_Material_Type (GIFAP Code)
' /my:main/my:Material_Section/my:TSN_Material_Section/my:TSN_Parent
'
mainNavigator.SelectSingleNode(sageShowMatlPanelPath, NamespaceManager).SetValue("true")
'
' Did we get any query results?
'
If resultNavigator Is Nothing Then
'
DebugLine = 1201
mainNavigator.SelectSingleNode(sageTSNFoundPath, NamespaceManager).SetValue("false")
'
' Did we find the record we wanted?
'
ElseIf resultNavigator.SelectSingleNode(resultSetPath & "/@MAT_ID", NamespaceManager) Is Nothing Then
'
DebugLine = 1201
mainNavigator.SelectSingleNode(sageTSNFoundPath, NamespaceManager).SetValue("false")
'
' Looks like we found it, so lets return the data
'
Else
'
DebugLine = 1202
mainNavigator.SelectSingleNode(sageTSNFoundPath, NamespaceManager).SetValue("true")
'
DebugLine = 130
NewValue = resultNavigator.SelectSingleNode(resultSetPath & "/@CABINET", NamespaceManager).Value
mainNavigator.SelectSingleNode(rootXMLPath & "/my:TSN_Cabinet", NamespaceManager).SetValue(NewValue)
'
DebugLine = 131
NewValue = resultNavigator.SelectSingleNode(resultSetPath & "/@DED", NamespaceManager).Value
mainNavigator.SelectSingleNode(rootXMLPath & "/my:TSN_DED", NamespaceManager).SetValue(NewValue)
'
DebugLine = 132
NewValue = resultNavigator.SelectSingleNode(resultSetPath & "/@F_MAT_GROUP_CLASS", NamespaceManager).Value
mainNavigator.SelectSingleNode(rootXMLPath & "/my:TSN_DED_GROUP", NamespaceManager).SetValue(NewValue)
'
DebugLine = 133
NewValue = resultNavigator.SelectSingleNode(resultSetPath & "/@MAT_NAME", NamespaceManager).Value
mainNavigator.SelectSingleNode(rootXMLPath & "/my:TSN_Material", NamespaceManager).SetValue(NewValue)
'
DebugLine = 134
NewValue = resultNavigator.SelectSingleNode(resultSetPath & "/@MAT_COMP", NamespaceManager).Value
mainNavigator.SelectSingleNode(rootXMLPath & "/my:TSN_Material_Comp", NamespaceManager).SetValue(NewValue)
'
DebugLine = 135
NewValue = resultNavigator.SelectSingleNode(resultSetPath & "/@MAT_FAMILY", NamespaceManager).Value
mainNavigator.SelectSingleNode(rootXMLPath & "/my:TSN_Material_Family", NamespaceManager).SetValue(NewValue)
'
DebugLine = 136
NewValue = resultNavigator.SelectSingleNode(resultSetPath & "/@MAT_ID", NamespaceManager).Value
mainNavigator.SelectSingleNode(rootXMLPath & "/my:TSN_Material_ID", NamespaceManager).SetValue(NewValue)
'
DebugLine = 137
NewValue = resultNavigator.SelectSingleNode(resultSetPath & "/@F_MAT_GROUP", NamespaceManager).Value
mainNavigator.SelectSingleNode(rootXMLPath & "/my:TSN_Parent", NamespaceManager).SetValue(NewValue)
'
DebugLine = 138
NewValue = resultNavigator.SelectSingleNode(resultSetPath & "/@GIFAP_CODE", NamespaceManager).Value
DebugLine = 1381
mainNavigator.SelectSingleNode(rootXMLPath & "/my:TSN_Material_Type", NamespaceManager).SetValue(NewValue)
'
End If
'
Catch ex As Exception
If Not resultNavigator Is Nothing Then
Throw New Exception("Line: " & DebugLine & resultNavigator.InnerXml & ", Error = " & ex.ToString)
Else
Throw New Exception("Line: " & DebugLine & ", Error = " & ex.ToString)
End If
End Try
End Sub
End Class