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
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