Re: Solution: Button Lookup to a customized SQL Server query to return a block of data

Patrick Halstead — Fri, 17 Jun 2011 00:35:18 GMT

Thanks for sharing!

Solution: Button Lookup to a customized SQL Server query to return a block of data

rlstrommen — Thu, 16 Jun 2011 17:35:56 GMT

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

SQL Database Integration

Re: Solution: Button Lookup to a customized SQL Server query to return a block of data

Solution: Button Lookup to a customized SQL Server query to return a block of data