SQL Database Integration

Re: Querying Secondary Data Connection into a Repeating Table using SQL Server Stored Proc.

rlstrommen — Mon, 11 Jul 2011 14:01:39 GMT

OK, here is how we solved this problem.

Stored Procedure

The stored procedure takes in a search filter parameter, along with the first and last page. The result searches the synonym table and returns only those rows that are requested.

CREATE FUNCTION [dbo].[SP_COA_LkpMaterialSynonym]
(	
	@MsynFilter VARCHAR(2000)
   ,@FirstRow INTEGER
   ,@LastRow INTEGER
)
RETURNS TABLE 
AS
RETURN 
(
	WITH SearchResult AS
	(
		 SELECT TOP 100 PERCENT 
				ROW_NUMBER() OVER (ORDER BY results.SR_PREFERRED_NAME ASC) AS RowNumber
			   ,results.SR_MAT_ID          AS MAT_ID
			   ,results.SR_PREFERRED_NAME  AS PREFERRED_NAME
			   ,results.SR_IUPAC_NAME      AS IUPAC_NAME
		 FROM (
			  SELECT DISTINCT
					 MAT_ID          AS SR_MAT_ID
					,PREFERRED_NAME  AS SR_PREFERRED_NAME
					,IUPAC_NAME      AS SR_IUPAC_NAME
			  FROM   dbo.SAGE_SP_COA_MATERIAL_SEARCH_RESULT
			  WHERE  UPPER(SYNONYM_NAME) LIKE UPPER(@MsynFilter)
			  ) results
	)
	SELECT	 TOP 100 PERCENT
	         MAT_ID         AS MAT_ID
			,PREFERRED_NAME AS PREFERRED_NAME
			,IUPAC_NAME     AS IUPAC_NAME
		    ,(SELECT COUNT(*)
		  			 FROM SearchResult 
			 )              AS TotalCount
	FROM     SearchResult
	WHERE	 RowNumber BETWEEN @FirstRow AND @LastRow
	ORDER BY RowNumber
)

Data Connector Query

The data connector is built using a base command. I'm going to change the whole thing anyway, so the only thing I need to make sure of if that I have the same columns in the query that I'm going to have when I tailor it.

SELECT MAT_ID,PREFERRED_NAME,IUPAC_NAME,"TotalCount" FROM dbo.SP_COA_LkpMaterialSynonym('%', 1, 20) as SAGE_SP_COA_MATERIAL_SEARCH_RESULT ORDER BY PREFERRED_NAME

VB Code

The VB Code is fixed as indicated below:

        Public Sub Do_Material_Search(ByVal FirstRow As Integer, ByVal LastRow As Integer)
            '
            ' Update /my:main/my:Material_Section/my:Material_Search_Group/my:SearchResultCount
            ' with the number of hits on the field
            '
            '  Repeating Table declarations
            '
            Dim rootXMLPath As String = "/my:main/my:Material_Section/my:Material_Search_Group"
            Dim mainNavigator As XPathNavigator
            Dim msynRows As XPathNodeIterator
            Dim msynRow As XPathNavigator
            Dim errorMessageXMLPath As String = "/my:main/my:Material_Section/my:Material_Search_Group/my:UseSelectedMaterialErrorMsg"
            '
            '  Search Result declarations
            '
            Dim resultNavigator As XPathNavigator
            Dim msynResults As XPathNodeIterator
            Dim I As Integer
            Dim writer As XmlWriter
            Dim MatID As String
            Dim PreferredName As String
            Dim IUPACName As String
            Dim recordCountSet As Boolean
            Dim hitCount As Boolean
            '
            Dim resultSetPath As String = "/dfs:myFields/dfs:dataFields/d:MATERIAL_SYNONYM"
            Dim sageSearchMaterialPath As String = "/my:main/my:Material_Section/my:Material_Search_Group/my:Search_Material"
            '
            Dim queryConnection As AdoQueryConnection
            Dim origCommand As String
            Dim sqlClause As String
            '
            Dim MsynDataSource As DataSource
            Dim myNamespace As String
            Dim dfsNamespace As String
            '
            Dim SearchMatlSynFilter As String
            Dim NewValue As String
            '
            '  Run a query to get the information based on the Search_Material
            '  /my:main/my:Material_Section/my:Material_Search_Group/my:Search_Material
            '
            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")
                '
                '  This is a new search, reset the error message.
                '
                mainNavigator.SelectSingleNode(errorMessageXMLPath, NamespaceManager).SetValue("")
                '
                DebugLine = 30
                SearchMatlSynFilter = mainNavigator.SelectSingleNode(sageSearchMaterialPath, NamespaceManager).Value
                '
                '  Save this search in the Prev filter for the results display
                '
                mainNavigator.SelectSingleNode(rootXMLPath & "/my:PrevSearchFilter", NamespaceManager).SetValue(SearchMatlSynFilter)
                DebugLine = 40
                '
                '      SELECT MAT_ID          AS MAT_ID
                '            ,PREFERRED_NAME  AS PREFERRED_NAME
                '            ,IUPAC_NAME      AS IUPAC_NAME
                '            ,TotalCount      AS TotalCount
                '      FROM dbo.SP_COA_LkpMaterialSynonym('ch%'
                '            ,1
                '            ,20)
                '
                sqlClause = ""
                sqlClause = sqlClause & "SELECT MAT_ID          AS MAT_ID" & vbCrLf
                sqlClause = sqlClause & "      ,PREFERRED_NAME  AS PREFERRED_NAME" & vbCrLf
                sqlClause = sqlClause & "      ,IUPAC_NAME      AS IUPAC_NAME" & vbCrLf
                sqlClause = sqlClause & "      ,TotalCount      AS TotalCount" & vbCrLf
                sqlClause = sqlClause & "FROM dbo.SP_COA_LkpMaterialSynonym(" & vbCrLf
                sqlClause = sqlClause & "       '" & SearchMatlSynFilter.Replace("'", "''") & "'" & vbCrLf
                sqlClause = sqlClause & "      ," & FirstRow.ToString & vbCrLf
                sqlClause = sqlClause & "      ," & LastRow.ToString & ")"
                '
                DebugLine = 60
                MsynDataSource = Me.DataSources("SAGE_SP_COA_MATERIAL_SEARCH_RESULT")
                queryConnection = MsynDataSource.QueryConnection
                DebugLine = 70
                origCommand = queryConnection.Command
                '
                '  modify the query to use the custom clause
                '
                DebugLine = 80
                queryConnection.Command = sqlClause
                '
                '  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 = MsynDataSource.CreateNavigator()
                '
                hitCount = 0
                recordCountSet = False
                '
                If resultNavigator IsNot Nothing Then
                    '
                    DebugLine = 1300
                    '
                    '  Get the resultset rows
                    '
                    msynResults = resultNavigator.Select(resultSetPath, NamespaceManager)
                    '  
                    ' Here is the example code to move data from a secondary data source into a repeating table.
                    ' Listing 17.5. Fully Changed Method 
                    '
                    '  Repeating Group: /my:main/my:Material_Section/my:Material_Search_Group/my:SynonymSearchResults/my:MsynRow
                    '  First task is to remove results from any previous search.
                    '  For whatever reason, the example says to delete from the bottom up
                    '  Note: Be sure to check if the xml array is zero relative or not
                    '  
                    DebugLine = 1400
                    msynRows = mainNavigator.Select(rootXMLPath & "/my:SynonymSearchResults/my:MsynRow", NamespaceManager)
                    If (msynRows.Count > 0) Then
                        For I = msynRows.Count To 1 Step -1
                            DebugLine = 1415 + I
                            msynRow = mainNavigator.SelectSingleNode(rootXMLPath & "/my:SynonymSearchResults/my:MsynRow[" & I & "]", NamespaceManager)
                            msynRow.DeleteSelf()
                        Next
                    End If
                    '
                    '  Populate return data
                    '
                    DebugLine = 1500
                    For I = 1 To msynResults.Count
                        DebugLine = 1510

                        If Not recordCountSet Then
                            DebugLine = 1520
                            NewValue = resultNavigator.SelectSingleNode(resultSetPath & "[" & I & "]/@TotalCount", NamespaceManager).Value
                            '
                            '  If the LastRow is greater than the total number of rows, reset it
                            '  (displaying 1 to 20 of 15 records becomes displaying 1 to 15 of 15.
                            '
                            If LastRow > Integer.Parse(NewValue) Then
                                LastRow = Integer.Parse(NewValue)
                            End If
                            DebugLine = 1530
                            mainNavigator.SelectSingleNode(rootXMLPath & "/my:SearchResultCount", NamespaceManager).SetValue(NewValue)
                            recordCountSet = True
                        End If
                        '
                        DebugLine = 1540
                        MatID = resultNavigator.SelectSingleNode(resultSetPath & "[" & I & "]/@MAT_ID", NamespaceManager).Value
                        DebugLine = 1550
                        PreferredName = resultNavigator.SelectSingleNode(resultSetPath & "[" & I & "]/@PREFERRED_NAME", NamespaceManager).Value
                        DebugLine = 1560
                        IUPACName = resultNavigator.SelectSingleNode(resultSetPath & "[" & I & "]/@IUPAC_NAME", NamespaceManager).Value
                        '
                        '  Populate the repeating table 
                        '
                        DebugLine = 157000 + I
                        writer = mainNavigator.SelectSingleNode(rootXMLPath & "/my:SynonymSearchResults", NamespaceManager).AppendChild()
                        DebugLine = 157100 + I
                        writer.WriteStartElement("MsynRow", myNamespace)
                        DebugLine = 157200 + I
                        writer.WriteElementString("SelectMe", myNamespace, "false")
                        DebugLine = 157300 + I
                        writer.WriteElementString("PreferredName", myNamespace, PreferredName)
                        DebugLine = 157400 + I
                        writer.WriteElementString("IUPACName", myNamespace, IUPACName)
                        DebugLine = 157500 + I
                        writer.WriteElementString("MatId", myNamespace, MatID)
                        DebugLine = 157600 + I
                        writer.WriteEndElement()
                        DebugLine = 157700 + I
                        writer.Close()
                        '
                    Next
                    '
                    '  Now all the results are listed
                    '
                End If
                '
                DebugLine = 1600
                If Not recordCountSet Then
                    NewValue = "0"
                    mainNavigator.SelectSingleNode(rootXMLPath & "/my:SearchResultCount", NamespaceManager).SetValue(NewValue)
                    FirstRow = 0
                    LastRow = 0
                End If
                '
                mainNavigator.SelectSingleNode(rootXMLPath & "/my:FirstRow", NamespaceManager).SetValue(FirstRow.ToString)
                mainNavigator.SelectSingleNode(rootXMLPath & "/my:LastRow", NamespaceManager).SetValue(LastRow.ToString)
                '
            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 & ", MatId = " & MatID & ", PrefName = " & PreferredName & ", Error = " & ex.ToString)
                End If
            End Try
        End Sub

Re: Querying Secondary Data Connection into a Repeating Table using SQL Server Stored Proc.

Hilary Stoupa — Thu, 07 Jul 2011 00:17:54 GMT

It has been a while since I've used code to return a stored procedure - but you should be able to just return a results table.

In fact, you can modify your data connection to execute a stored procedure - maybe pass in a dummy value for your parameter? And then you will be assured that the schema IP creates will be correct for the returned results of your procedure?

Querying Secondary Data Connection into a Repeating Table using SQL Server Stored Proc.

rlstrommen — Wed, 29 Jun 2011 19:03:18 GMT

OK, from my previous post, I've now implemented another button that does a search on our material database. When I went to implement paging, however, I found that the ADO query processor doesn't understand the SQL Server WITH verb.

Functional Requirement

User enters a search criteria, and clicks search
1-20 rows are returned
User clicks on Next and Prev to get to the page of results that he wants to see.
User selects a material, and then proceeds with the rest of the form.

Constraints

We don't have a website yet, so writing this as a webservice isn't available to us yet.

What I want to do is to write a stored procedure that takes as parameters the SearchFilter, the first row and the last row of the result query. I'm a little confused, though, as to what the stored procedure should return.

Question: Do I simply return the results of my query in the stored procedure? I'm kind of thinking yes, but I'd like confirmation on that.

Here is my current code. The SQL Command will ultimately be replaced with EXEC dbo.SearchMaterialTables inputSearchFilter, FirstRow, LastRow

        Public Sub Do_Material_Search(ByVal FirstRow As Integer, ByVal LastRow As Integer)
            ' Write your code here.
            ' Write your code here.
            '
            ' Write your code here to change the main data source.
            ' Update /my:main/my:Material_Section/my:Material_Search_Group/my:SearchResultCount
            ' with the number of hits on the field
            '
            '  Repeating Table declarations
            '
            Dim rootXMLPath As String = "/my:main/my:Material_Section/my:Material_Search_Group"
            Dim mainNavigator As XPathNavigator
            Dim msynRows As XPathNodeIterator
            Dim msynRow As XPathNavigator
            '
            '  Search Result declarations
            '
            Dim resultNavigator As XPathNavigator
            Dim msynResults As XPathNodeIterator
            Dim I As Integer
            Dim writer As XmlWriter
            Dim MatID As String
            Dim PreferredName As String
            Dim IUPACName As String
            Dim recordCountSet As Boolean
            Dim hitCount As Boolean
            '
            Dim resultSetPath As String = "/dfs:myFields/dfs:dataFields/d:SAGE_SP_COA_MATERIAL_SEARCH_BASE"
            Dim sageSearchMaterialPath As String = "/my:main/my:Material_Section/my:Material_Search_Group/my:Search_Material"
            '
            Dim queryConnection As AdoQueryConnection
            Dim origCommand As String
            Dim sqlClause As String
            '
            Dim MsynDataSource As DataSource
            Dim myNamespace As String
            Dim dfsNamespace As String
            '
            Dim DebugLine As Integer
            Dim SearchMatlSynFilter 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 "MAT_ID","SYNONYM_NAME" 
            '  FROM   "dbo"."SAGE_SP_COA_MATERIAL_SEARCH_BASE" as "SAGE_SP_COA_MATERIAL_SEARCH_BASE" 
            '  WHERE SYNONYM_NAME = /my:main/my:Material_Section/my:Material_Search_Group/my:Search_Material
            '
            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
                SearchMatlSynFilter = mainNavigator.SelectSingleNode(sageSearchMaterialPath, NamespaceManager).Value
                '
                '  Save this search in the Prev filter for the results display
                '
                mainNavigator.SelectSingleNode(rootXMLPath & "/my:PrevSearchFilter", NamespaceManager).SetValue(SearchMatlSynFilter)
                DebugLine = 40
                sqlClause = ""
                sqlClause = sqlClause & "WITH SearchResult AS" & vbCrLf
                sqlClause = sqlClause & "(" & vbCrLf
                sqlClause = sqlClause & "     SELECT TOP 100 PERCENT " & vbCrLf
                sqlClause = sqlClause & "            ROW_NUMBER() OVER (ORDER BY results.SR_PREFERRED_NAME ASC) AS RowNumber" & vbCrLf
                sqlClause = sqlClause & "           ,results.SR_MAT_ID          AS MAT_ID" & vbCrLf
                sqlClause = sqlClause & "           ,results.SR_PREFERRED_NAME  AS PREFERRED_NAME" & vbCrLf
                sqlClause = sqlClause & "           ,results.SR_IUPAC_NAME      AS IUPAC_NAME" & vbCrLf
                sqlClause = sqlClause & "     FROM (" & vbCrLf
                sqlClause = sqlClause & "          SELECT DISTINCT" & vbCrLf
                sqlClause = sqlClause & "                 MAT_ID          AS SR_MAT_ID" & vbCrLf
                sqlClause = sqlClause & "                  ,PREFERRED_NAME  AS SR_PREFERRED_NAME" & vbCrLf
                sqlClause = sqlClause & "                  ,IUPAC_NAME      AS SR_IUPAC_NAME" & vbCrLf
                sqlClause = sqlClause & "          FROM(dbo.SAGE_SP_COA_MATERIAL_SEARCH_RESULT)" & vbCrLf
                sqlClause = sqlClause & "          WHERE UPPER(SYNONYM_NAME) LIKE UPPER'" + SearchMatlSynFilter.Replace("'", "''") + "')" & vbCrLf
                sqlClause = sqlClause & "          ) results" & vbCrLf
                sqlClause = sqlClause & ")" & vbCrLf
                sqlClause = sqlClause & "SELECT	MAT_ID         AS MAT_ID" & vbCrLf
                sqlClause = sqlClause & "        ,PREFERRED_NAME AS PREFERRED_NAME" & vbCrLf
                sqlClause = sqlClause & "        ,IUPAC_NAME     AS IUPAC_NAME" & vbCrLf
                sqlClause = sqlClause & "       ,(SELECT COUNT(*)" & vbCrLf
                sqlClause = sqlClause & "                FROM(SearchResult)" & vbCrLf
                sqlClause = sqlClause & "        ) AS TotalCount" & vbCrLf
                sqlClause = sqlClause & "FROM (SearchResult)" & vbCrLf
                sqlClause = sqlClause & "WHERE	 RowNumber BETWEEN " & FirstRow.ToString & " AND " & LastRow.ToString & vbCrLf
                sqlClause = sqlClause & "ORDER BY RowNumber"
                '
                '  Paging query - Need first and last row number, and the search value
                '  WITH SearchResult AS
                '  (
                '       SELECT TOP 100 PERCENT 
                '              ROW_NUMBER() OVER (ORDER BY results.SR_PREFERRED_NAME ASC) AS RowNumber
                '             ,results.SR_MAT_ID          AS MAT_ID
                '             ,results.SR_PREFERRED_NAME  AS PREFERRED_NAME
                '             ,results.SR_IUPAC_NAME      AS IUPAC_NAME
                '       FROM (
                '            SELECT DISTINCT
                '                   MAT_ID          AS SR_MAT_ID
                '                    ,PREFERRED_NAME  AS SR_PREFERRED_NAME
                '                    ,IUPAC_NAME      AS SR_IUPAC_NAME
                '            FROM(dbo.SAGE_SP_COA_MATERIAL_SEARCH_RESULT)
                '            WHERE UPPER(SYNONYM_NAME) LIKE UPPER('%clop%')
                '            ) results
                '  )
                '  SELECT	MAT_ID         AS MAT_ID
                '          ,PREFERRED_NAME AS PREFERRED_NAME
                '          ,IUPAC_NAME     AS IUPAC_NAME
                '  FROM (SearchResult)
                '  WHERE	 RowNumber BETWEEN 1 AND 20
                '  ORDER BY RowNumber
                '  
                DebugLine = 60
                MsynDataSource = Me.DataSources("SAGE_SP_COA_MATERIAL_SEARCH_BASE")
                queryConnection = MsynDataSource.QueryConnection
                DebugLine = 70
                origCommand = queryConnection.Command
                '
                '  modify the query to use the where clause
                '
                DebugLine = 80
                queryConnection.Command = sqlClause
                '
                '  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 = MsynDataSource.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
                '
                '  Did we get any query results?
                '
                '
                DebugLine = 1201
                '
                '  Return the record count in this iteration.
                '
                hitCount = 0
                recordCountSet = False
                '
                If resultNavigator IsNot Nothing Then
                    '
                    DebugLine = 1300
                    msynResults = resultNavigator.Select(resultSetPath, NamespaceManager)
                    '  
                    ' Here is the example code to move data from a secondary data source into a repeating table.
                    ' Listing 17.5. Fully Changed Method 
                    '
                    '  Repeating Group: /my:main/my:Material_Section/my:Material_Search_Group/my:SynonymSearchResults/my:MsynRow
                    '  First task is to remove results from any previous search.
                    '  For whatever reason, the example says to delete from the bottom up
                    '  Note: Be sure to check if the xml array is zero relative or not
                    '  
                    DebugLine = 1400
                    msynRows = mainNavigator.Select(rootXMLPath & "/my:SynonymSearchResults/my:MsynRow", NamespaceManager)
                    If (msynRows.Count > 0) Then
                        For I = msynRows.Count To 1 Step -1
                            msynRow = mainNavigator.SelectSingleNode(rootXMLPath & "/my:SynonymSearchResults/my:MsynRow[" + I + "]", NamespaceManager)
                            msynRow.DeleteSelf()
                        Next
                    End If
                    '
                    '  Populate return data
                    '
                    DebugLine = 1500
                    While (msynResults.MoveNext())

                        If Not recordCountSet Then
                            NewValue = msynResults.Current.SelectSingleNode("d:TotalCount", NamespaceManager).Value.ToString()
                            mainNavigator.SelectSingleNode(rootXMLPath & "/my:SearchResultCount", NamespaceManager).SetValue(NewValue)
                            recordCountSet = True
                        End If
                        MatID = msynResults.Current.SelectSingleNode("d:MAT_ID", NamespaceManager).Value.ToString()
                        PreferredName = msynResults.Current.SelectSingleNode("d:PREFERRED_NAME", NamespaceManager).Value.ToString()
                        IUPACName = msynResults.Current.SelectSingleNode("d:IUPAC_NAME", NamespaceManager).Value.ToString()
                        '
                        '  Populate the repeating table 
                        '
                        writer = mainNavigator.SelectSingleNode(rootXMLPath & "/my:SynonymSearchResults", NamespaceManager).AppendChild()
                        writer.WriteStartElement("MsynRow", myNamespace)
                        writer.WriteElementString("SelectMe", myNamespace, MatID)
                        writer.WriteElementString("PreferredName", myNamespace, PreferredName)
                        writer.WriteElementString("IUPACName", myNamespace, IUPACName)
                        writer.WriteEndElement()
                        writer.Close()
                    End While
                    '
                    '  Now all the results are listed
                    '
                End If
                '
                DebugLine = 1600
                If Not recordCountSet Then
                    NewValue = "0"
                    mainNavigator.SelectSingleNode(rootXMLPath & "/my:SearchResultCount", NamespaceManager).SetValue(NewValue)
                End If
                '
                mainNavigator.SelectSingleNode(rootXMLPath & "/my:FirstRow", NamespaceManager).SetValue(FirstRow.ToString)
                mainNavigator.SelectSingleNode(rootXMLPath & "/my:LastRow", NamespaceManager).SetValue(LastRow.ToString)
                '
            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

        Public Sub SearchMaterialButton_Clicked(ByVal sender As Object, ByVal e As ClickedEventArgs)
            Do_Material_Search(1, 20)
        End Sub

        Public Sub NextSearchButton_Clicked(ByVal sender As Object, ByVal e As ClickedEventArgs)
            Dim rootXMLPath As String = "/my:main/my:Material_Section/my:Material_Search_Group"
            Dim mainNavigator As XPathNavigator
            Dim FirstRow, LastRow, TotalRows As Integer
            Dim thisSearch, prevSearch As String
            '
            '  Get the current search state
            '
            mainNavigator = Me.CreateNavigator()
            '
            '  Get the current search state
            '
            mainNavigator = Me.CreateNavigator()
            thisSearch = mainNavigator.SelectSingleNode(rootXMLPath & "/my:Search_Material", NamespaceManager).Value
            prevSearch = mainNavigator.SelectSingleNode(rootXMLPath & "/my:PrevSearchFilter", NamespaceManager).Value
            If thisSearch <> prevSearch Then

                '
                '  If they changed the search, then reset the page counters
                '
                FirstRow = 1
                LastRow = 20
            Else
                TotalRows = Integer.Parse(mainNavigator.SelectSingleNode(rootXMLPath & "/my:SearchResultCount", NamespaceManager).Value)
                FirstRow = Integer.Parse(mainNavigator.SelectSingleNode(rootXMLPath & "/my:FirstRow", NamespaceManager).Value)
                LastRow = Integer.Parse(mainNavigator.SelectSingleNode(rootXMLPath & "/my:LastRow", NamespaceManager).Value)
                '
                '  Adjust the page window
                '
                FirstRow = FirstRow + 20
                If FirstRow < 1 Then
                    FirstRow = 1
                End If
                '
                '  If the FirstRow goes past the total number of hits,
                '  then set it to the last multiple of 20 less than the total
                '  If we have 0 rows, then display 0 to 0 instead of 1 to 0.
                '
                If FirstRow > TotalRows Then
                    FirstRow = Math.Floor(TotalRows / 20) * 20
                    If TotalRows > 0 Then
                        FirstRow = FirstRow + 1
                    End If
                End If
                '
                LastRow = FirstRow + 20
                If LastRow > TotalRows Then
                    LastRow = TotalRows
                End If
            End If
            '
            '  Execute the search
            '
            Do_Material_Search(FirstRow, LastRow)
        End Sub

        Public Sub PrevSearchButton_Clicked(ByVal sender As Object, ByVal e As ClickedEventArgs)
            Dim rootXMLPath As String = "/my:main/my:Material_Section/my:Material_Search_Group"
            Dim mainNavigator As XPathNavigator
            Dim FirstRow, LastRow, TotalRows As Integer
            Dim thisSearch, prevSearch As String
            '
            '  Get the current search state
            '
            mainNavigator = Me.CreateNavigator()
            thisSearch = mainNavigator.SelectSingleNode(rootXMLPath & "/my:Search_Material", NamespaceManager).Value
            prevSearch = mainNavigator.SelectSingleNode(rootXMLPath & "/my:PrevSearchFilter", NamespaceManager).Value
            If thisSearch <> prevSearch Then

                '
                '  If they changed the search, then reset the page counters
                '
                FirstRow = 1
                LastRow = 20
            Else
                TotalRows = Integer.Parse(mainNavigator.SelectSingleNode(rootXMLPath & "/my:SearchResultCount", NamespaceManager).Value)
                FirstRow = Integer.Parse(mainNavigator.SelectSingleNode(rootXMLPath & "/my:FirstRow", NamespaceManager).Value)
                LastRow = Integer.Parse(mainNavigator.SelectSingleNode(rootXMLPath & "/my:LastRow", NamespaceManager).Value)
                '
                '  Adjust the page window
                '
                FirstRow = FirstRow - 20
                If FirstRow < 1 Then
                    FirstRow = 1
                End If
                If FirstRow > TotalRows Then
                    FirstRow = TotalRows
                End If
                '
                '  If the FirstRow goes past the total number of hits,
                '  then set it to the last multiple of 20 less than the total
                '  If we have 0 rows, then display 0 to 0 instead of 1 to 0.
                '
                If FirstRow > TotalRows Then
                    FirstRow = Math.Floor(TotalRows / 20) * 20
                    If TotalRows > 0 Then
                        FirstRow = FirstRow + 1
                    End If
                End If
                '
                LastRow = FirstRow + 20
                If LastRow > TotalRows Then
                    LastRow = TotalRows
                End If
            End If
            '
            '  Execute the search
            '
            Do_Material_Search(FirstRow, LastRow)
        End Sub