|
Home > Archive > IIS ASP > April 2006 > Dispaly Results, then highlight closest matched row ASP.NET/ADO.NE
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Dispaly Results, then highlight closest matched row ASP.NET/ADO.NE
|
|
| Daniel Di Vita 2006-04-27, 7:52 am |
| 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] 2006-04-27, 7:52 am |
| 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 2006-04-27, 7:52 am |
|
"Daniel Di Vita" <DanielDiVita@discussions.microsoft.com> wrote in message
news:50FEE7CB-34C2-4C16-BA30-903A49A34513@microsoft.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
>
|
|
|
|
|