Dispaly Results, then highlight closest matched row ASP.NET/ADO.NE

[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

[Bob Barrows [MVP]]

Daniel Di Vita wrote:

I have created an ASP.NET page
There was no way for you to know it (except maybe by browsing through some
of the previous questions before posting yours - always a recommended
practice), but this is a classic asp newsgroup.
ASP.Net is a different technology from classic ASP.
While you may be lucky enough to find a dotnet-savvy person here who can
answer your question, you can eliminate the luck factor by posting your
question to a newsgroup where the dotnet-savvy people hang out. I suggest
microsoft.public.dotnet.framework.aspnet.
that allows the user to page through
a result set. I need to expand on this.
There are a couple articles by Scott Mitchell that deal with this topic:
http://aspnet.4guysfromrolla.com/articles/031506-1.aspx
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.

There's a lot to digest there, and frankly, I'm not sure what the problem
is. You may benefit by reading Erland Sommarskog's dynamic search conditions
article: http://www.sommarskog.se/dyn-search.html

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Anthony Jones]

"Daniel Di Vita" <Da**********@discussions.microsoft.com> wrote in message
news:50**********************************@microsof t.com...

I have created an ASP.NET page that allows the user to page through a result

This group is for classic ASP. Direct questions regarding ASP.NET to
microsoft.public.dotnet.framework.aspnet[.*] newsgroups.
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