D
Daniel Di Vita
I have created an ASP.NET page that allows the user to page through a result
set. I need to expand on this. On that same page I a filed where the user can
type in a search string. When they click a button ALL the results will be
returned and the closest match to the search string will be highlighted. The
approach I am taking to page the data is to put the keys/indexes into an
array then create another data reader based on those results to display the
actual data. There may be a better way, if there are any suggestions.
Bottom line I need to find what page the search string is on so I can
highlight it. I guess I would have to calculate what page that record is on,
but I can’t wrap my head around it. Here is some code that I use for the
paging:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Handles Me.Load
Dim Conn As SqlConnection
Dim Query As String
Dim SqlComm As SqlCommand
Dim myDataReader As SqlDataReader
' Define connection object
Conn = New SqlConnection(ConnString)
' Define query to retrieve primary key values
Query = "SELECT " & PrimaryKeyColumn & " FROM " & TableName & "
WHERE (Categories.CategoryName <= 'Confections') ORDER BY " & SetSorting()
' Define command object
SqlComm = New SqlCommand(Query, Conn)
' Open connection to database
Conn.Open()
' Create DataReader
myDataReader = SqlComm.ExecuteReader()
' Iterate through records and add to array list
While myDataReader.Read()
IDList.Add(myDataReader(PrimaryKeyColumn))
End While
' Close DataReader and connection objects
myDataReader.Close()
myDataReader = Nothing
Conn.Close()
Conn = Nothing
' If page has not been posted back, retrieve first page of records
If Not Page.IsPostBack Then
Paging()
End If
End Sub
Sub Paging(Optional ByVal WhichPage As Integer = 1, Optional ByVal
RecordsPerPage As Integer = 10)
' Determine total number of records
Dim NumItems As Integer = IDList.Count
' Set number of records per page
Dim PageSize As Integer = RecordsPerPage
' Determine number of pages minus any leftover records
Dim Pages As Long = NumItems \ PageSize
' Save this number for future reference
Dim WholePages As Long = NumItems \ PageSize
' Determine number of leftover records
Dim Leftover As Integer = NumItems Mod PageSize
' If there are leftover records, increase page count by one
If Leftover > 0 Then
Pages += 1
End If
Dim i As Integer
Dim CurrentSelection As String
Dim StartOfPage As Integer
Dim EndOfPage As Integer
' Set current page
Dim CurrentPage As Integer = WhichPage
' If current page does not fall within the valid range of pages
If CurrentPage > Pages Or CurrentPage < 0 Then
' Call paging subroutine and reset to first page
Paging(1, RecordsPerPage)
' If current page does fall within valid range of pages
Else
' If current page is the last page, hide the "next" and "last"
navigation links
If CurrentPage = Pages Then
NextLink.ImageUrl = "images/Nav_Next_Disabled.jpg"
NextLink.Enabled = False
LastLink.ImageUrl = "images/Nav_LastPage_Disabled.jpg"
LastLink.Enabled = False
' Otherwise, show the "next" and "last" navigation links and
set the page index each will pass when clicked
Else
NextLink.ImageUrl = "images/Nav_Next.jpg"
NextLink.Enabled = True
LastLink.ImageUrl = "images/Nav_LastPage.jpg"
LastLink.Enabled = True
NextLink.CommandArgument = CurrentPage + 1
LastLink.CommandArgument = Pages
End If
' If current page is the first page, hide the "first" and
"previous" navigation links
If CurrentPage = 1 Then
PreviousLink.ImageUrl = "images/Nav_Previous_Disabled.jpg"
PreviousLink.Enabled = False
FirstLink.ImageUrl = "images/Nav_Firstpage_Disabled.jpg"
FirstLink.Enabled = False
' Otherwise, show the "first" and "previous" navigation
links and set the page index each will pass when clicked
Else
PreviousLink.ImageUrl = "images/Nav_Previous.jpg"
PreviousLink.Enabled = True
FirstLink.ImageUrl = "images/Nav_FirstPage.jpg"
FirstLink.Enabled = True
PreviousLink.CommandArgument = CurrentPage - 1
FirstLink.CommandArgument = 1
End If
' Create ArrayList to store range of valid pages
Dim JumpPageList = New ArrayList
Dim x As Integer
' Iterate through range of valid pages and add to ArrayList
For x = 1 To Pages
JumpPageList.Add(x)
Next
' Use this ArrayList to populate page navigation drop-down menu
JumpPage.DataSource = JumpPageList
JumpPage.DataBind()
' Select current page in drop-down menu
JumpPage.SelectedIndex = CurrentPage - 1
' Set the record count and page count text
RecordCountLabel.Text = NumItems
PageCountLabel.Text = Pages
' Determine the starting and ending index in the IDList
ArrayList given the current page
StartOfPage = PageSize * (CurrentPage - 1)
EndOfPage = Min((PageSize * (CurrentPage - 1)) + (PageSize - 1),
((WholePages * PageSize) + Leftover - 1))
' Retrieve the subset of primary key values that belong on the
current page
Dim CurrentSubset As String = Join(IDList.GetRange(StartOfPage,
(EndOfPage - StartOfPage + 1)).ToArray, ",")
Dim Conn As SqlConnection
Dim Query As String
Dim SqlComm As SqlCommand
' Define connection object
Conn = New SqlConnection(ConnString)
' Define query to retrieve current page's records
Query = "SELECT " & ColumnsToRetrieve & " FROM " & TableName & "
WHERE " & PrimaryKeyColumn & " IN ('" & CurrentSubset.Replace(",", "','") &
"') ORDER BY " & SetSorting()
' Define command object
SqlComm = New SqlCommand(Query, Conn)
' Open connection
Conn.Open()
' Databind records to repeater
myRepeater.DataSource = SqlComm.ExecuteReader()
myRepeater.DataBind()
' Close connection
Conn.Close()
Conn = Nothing
End If
End Sub
set. I need to expand on this. On that same page I a filed where the user can
type in a search string. When they click a button ALL the results will be
returned and the closest match to the search string will be highlighted. The
approach I am taking to page the data is to put the keys/indexes into an
array then create another data reader based on those results to display the
actual data. There may be a better way, if there are any suggestions.
Bottom line I need to find what page the search string is on so I can
highlight it. I guess I would have to calculate what page that record is on,
but I can’t wrap my head around it. Here is some code that I use for the
paging:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Handles Me.Load
Dim Conn As SqlConnection
Dim Query As String
Dim SqlComm As SqlCommand
Dim myDataReader As SqlDataReader
' Define connection object
Conn = New SqlConnection(ConnString)
' Define query to retrieve primary key values
Query = "SELECT " & PrimaryKeyColumn & " FROM " & TableName & "
WHERE (Categories.CategoryName <= 'Confections') ORDER BY " & SetSorting()
' Define command object
SqlComm = New SqlCommand(Query, Conn)
' Open connection to database
Conn.Open()
' Create DataReader
myDataReader = SqlComm.ExecuteReader()
' Iterate through records and add to array list
While myDataReader.Read()
IDList.Add(myDataReader(PrimaryKeyColumn))
End While
' Close DataReader and connection objects
myDataReader.Close()
myDataReader = Nothing
Conn.Close()
Conn = Nothing
' If page has not been posted back, retrieve first page of records
If Not Page.IsPostBack Then
Paging()
End If
End Sub
Sub Paging(Optional ByVal WhichPage As Integer = 1, Optional ByVal
RecordsPerPage As Integer = 10)
' Determine total number of records
Dim NumItems As Integer = IDList.Count
' Set number of records per page
Dim PageSize As Integer = RecordsPerPage
' Determine number of pages minus any leftover records
Dim Pages As Long = NumItems \ PageSize
' Save this number for future reference
Dim WholePages As Long = NumItems \ PageSize
' Determine number of leftover records
Dim Leftover As Integer = NumItems Mod PageSize
' If there are leftover records, increase page count by one
If Leftover > 0 Then
Pages += 1
End If
Dim i As Integer
Dim CurrentSelection As String
Dim StartOfPage As Integer
Dim EndOfPage As Integer
' Set current page
Dim CurrentPage As Integer = WhichPage
' If current page does not fall within the valid range of pages
If CurrentPage > Pages Or CurrentPage < 0 Then
' Call paging subroutine and reset to first page
Paging(1, RecordsPerPage)
' If current page does fall within valid range of pages
Else
' If current page is the last page, hide the "next" and "last"
navigation links
If CurrentPage = Pages Then
NextLink.ImageUrl = "images/Nav_Next_Disabled.jpg"
NextLink.Enabled = False
LastLink.ImageUrl = "images/Nav_LastPage_Disabled.jpg"
LastLink.Enabled = False
' Otherwise, show the "next" and "last" navigation links and
set the page index each will pass when clicked
Else
NextLink.ImageUrl = "images/Nav_Next.jpg"
NextLink.Enabled = True
LastLink.ImageUrl = "images/Nav_LastPage.jpg"
LastLink.Enabled = True
NextLink.CommandArgument = CurrentPage + 1
LastLink.CommandArgument = Pages
End If
' If current page is the first page, hide the "first" and
"previous" navigation links
If CurrentPage = 1 Then
PreviousLink.ImageUrl = "images/Nav_Previous_Disabled.jpg"
PreviousLink.Enabled = False
FirstLink.ImageUrl = "images/Nav_Firstpage_Disabled.jpg"
FirstLink.Enabled = False
' Otherwise, show the "first" and "previous" navigation
links and set the page index each will pass when clicked
Else
PreviousLink.ImageUrl = "images/Nav_Previous.jpg"
PreviousLink.Enabled = True
FirstLink.ImageUrl = "images/Nav_FirstPage.jpg"
FirstLink.Enabled = True
PreviousLink.CommandArgument = CurrentPage - 1
FirstLink.CommandArgument = 1
End If
' Create ArrayList to store range of valid pages
Dim JumpPageList = New ArrayList
Dim x As Integer
' Iterate through range of valid pages and add to ArrayList
For x = 1 To Pages
JumpPageList.Add(x)
Next
' Use this ArrayList to populate page navigation drop-down menu
JumpPage.DataSource = JumpPageList
JumpPage.DataBind()
' Select current page in drop-down menu
JumpPage.SelectedIndex = CurrentPage - 1
' Set the record count and page count text
RecordCountLabel.Text = NumItems
PageCountLabel.Text = Pages
' Determine the starting and ending index in the IDList
ArrayList given the current page
StartOfPage = PageSize * (CurrentPage - 1)
EndOfPage = Min((PageSize * (CurrentPage - 1)) + (PageSize - 1),
((WholePages * PageSize) + Leftover - 1))
' Retrieve the subset of primary key values that belong on the
current page
Dim CurrentSubset As String = Join(IDList.GetRange(StartOfPage,
(EndOfPage - StartOfPage + 1)).ToArray, ",")
Dim Conn As SqlConnection
Dim Query As String
Dim SqlComm As SqlCommand
' Define connection object
Conn = New SqlConnection(ConnString)
' Define query to retrieve current page's records
Query = "SELECT " & ColumnsToRetrieve & " FROM " & TableName & "
WHERE " & PrimaryKeyColumn & " IN ('" & CurrentSubset.Replace(",", "','") &
"') ORDER BY " & SetSorting()
' Define command object
SqlComm = New SqlCommand(Query, Conn)
' Open connection
Conn.Open()
' Databind records to repeater
myRepeater.DataSource = SqlComm.ExecuteReader()
myRepeater.DataBind()
' Close connection
Conn.Close()
Conn = Nothing
End If
End Sub