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

Discussion in 'ASP General' started by Daniel Di Vita, Apr 21, 2006.

  1. 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
    Daniel Di Vita, Apr 21, 2006
    #1
    1. Advertising

  2. 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.
    Bob Barrows [MVP], Apr 21, 2006
    #2
    1. Advertising

  3. "Daniel Di Vita" <> wrote in message
    news:...
    > 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
    >
    Anthony Jones, Apr 21, 2006
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. nita
    Replies:
    1
    Views:
    856
    Saravana
    Nov 20, 2004
  2. Fernando Lopes
    Replies:
    0
    Views:
    3,185
    Fernando Lopes
    Apr 28, 2005
  3. =?Utf-8?B?RGFuaWVsIERpIFZpdGE=?=

    Paging data and highlight closest match

    =?Utf-8?B?RGFuaWVsIERpIFZpdGE=?=, Apr 21, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    373
    =?Utf-8?B?RGFuaWVsIERpIFZpdGE=?=
    Apr 22, 2006
  4. Replies:
    0
    Views:
    261
  5. Replies:
    13
    Views:
    254
Loading...

Share This Page