ASP Paging on a searched memo field

Discussion in 'ASP General' started by Hate Spam, Aug 5, 2003.

  1. Hate Spam

    Hate Spam Guest

    I need advice on how to integrate the paging capbility to my search
    script. The program I built requires me to make word search on several
    db fields including one that's a memo type field, which doesn't work
    with just a LIKE statement.
    I was doing OK up till the part where I needed to page the results.
    Here the code that I used (note that the code doesn't have paging
    yet):

    ===== start code =====

    ' generate sql for search
    if request.form("btnSubmit") = "Search" then
    strTitle = request.form("fldTitle") 'subject field
    strContent = lcase(request.form("fldContent")) 'news content field

    'generate sql
    strSqlsearch = "select * from News "
    if strTitle <> "" then
    strSqlsearch = strSqlsearch & "where Subject like '%" &
    strTitle & "%' "
    else
    strSqlsearch = strSqlsearch & "and Subject like '%" &
    strTitle & "%' "
    end if
    strSqlsearch = strSqlsearch & "order by id desc"

    ' run sql search
    SQLstatement = strSqlsearch
    set rs = conn.Execute(SQLstatement)

    ' get ready to display
    if rs.eof then
    ' display not found msg
    response.write "<p>No info matches your search.</p>"
    else
    ' write out results
    do while not rs.eof
    ' chg memo field to string
    strContentrs = lcase(rs("Content"))
    ' check if contains keyword for content
    if instr(strContentrs, strContent) > 0 then
    response.write "some stuff to be written"
    end if
    rs.movenext
    loop
    end if
    end if

    ===== end code =====

    Again, could someone advise me how to go about paging this?
    Hate Spam, Aug 5, 2003
    #1
    1. Advertising

  2. Hate Spam

    Hate Spam Guest

    > > Again, could someone advise me how to go about paging this?
    >
    > Here's an article on paging that might help you get started...
    > http://www.aspfaq.com/2120


    Thanks for the link, but, well... that still doesn't answer how I can
    page something that can't be a part of the sql statement.

    I DO know how to page it, IF the constraints are added inside the sql
    statement. My problem is that the contraint I have (the memo field)
    can't be searched using SQL.

    If you take a look at the code I posted earlier, the memo search
    filtering only began inside the "DO WHILE not rs.EOF" statement. When
    this happens, even tho the recordset says it has 8 results, the actual
    result might be less because of the memo filtering done afterwards.

    BTW, I forgot to mention that I'm using Access 2000 for my db backend.
    Appreciate any tips or links you may have.
    Hate Spam, Aug 6, 2003
    #2
    1. Advertising

  3. Hate Spam wrote:
    >
    > I need advice on how to integrate the paging capbility to my search
    > script. The program I built requires me to make word search on several
    > db fields including one that's a memo type field, which doesn't work
    > with just a LIKE statement.
    > I was doing OK up till the part where I needed to page the results.
    > Here the code that I used (note that the code doesn't have paging
    > yet):
    >
    > ===== start code =====
    >
    > ' generate sql for search
    > if request.form("btnSubmit") = "Search" then
    > strTitle = request.form("fldTitle") 'subject field
    > strContent = lcase(request.form("fldContent")) 'news content field
    >
    > 'generate sql
    > strSqlsearch = "select * from News "
    > if strTitle <> "" then
    > strSqlsearch = strSqlsearch & "where Subject like '%" &
    > strTitle & "%' "
    > else
    > strSqlsearch = strSqlsearch & "and Subject like '%" &
    > strTitle & "%' "
    > end if
    > strSqlsearch = strSqlsearch & "order by id desc"
    >
    > ' run sql search
    > SQLstatement = strSqlsearch
    > set rs = conn.Execute(SQLstatement)
    >
    > ' get ready to display
    > if rs.eof then
    > ' display not found msg
    > response.write "<p>No info matches your search.</p>"
    > else
    > ' write out results
    > do while not rs.eof
    > ' chg memo field to string
    > strContentrs = lcase(rs("Content"))
    > ' check if contains keyword for content
    > if instr(strContentrs, strContent) > 0 then
    > response.write "some stuff to be written"
    > end if
    > rs.movenext
    > loop
    > end if
    > end if
    >
    > ===== end code =====
    >
    > Again, could someone advise me how to go about paging this?


    Since you want a page that displays N records at a time, add a counter
    that exits the loop after N records have displayed.

    When first executed the ASP page displays N records fitting the criteria
    and then exits. Prior to exiting, save the id value (we'll call it
    PreviousID) of the last displayed record in a Session variable, a hidden
    <FORM> field, or a querystring variable. On subsequent page executions
    (all executions except the first) retrieve PreviousID and add the
    following criteria to the SQL statement:
    WHERE ID < PreviousID

    This ensures that each page after the first picks up where the previous
    one left off.

    Also add code to handle end of data: when you display the last record
    (rs.EOF is true) set the PreviousID to an appropriate value (e.g. 0, -1,
    "").

    This will work because you have ordered your information by ID. If you
    change the retrieval order you will have to correspondingly change the
    "pointer" that you are saving between pages.

    Good Luck,
    Michael D. Kersey
    Michael D. Kersey, Aug 9, 2003
    #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. =?Utf-8?B?TWF0dA==?=

    Update Access Memo field from ASP.NET

    =?Utf-8?B?TWF0dA==?=, Apr 11, 2005, in forum: ASP .Net
    Replies:
    6
    Views:
    5,980
    =?Utf-8?B?TWF0dA==?=
    Apr 11, 2005
  2. Travis

    Searched Datagrid with Paging?

    Travis, Mar 14, 2005, in forum: ASP .Net Datagrid Control
    Replies:
    11
    Views:
    251
    Jason Bentley
    Mar 24, 2005
  3. Hanno

    Display an Access Memo field in a GridView or DetailsView

    Hanno, Aug 4, 2006, in forum: ASP .Net Datagrid Control
    Replies:
    3
    Views:
    262
    Ken Cox [Microsoft MVP]
    Aug 6, 2006
  4. Jim Plante

    MS Access Memo Field and ASP

    Jim Plante, Aug 6, 2003, in forum: ASP General
    Replies:
    2
    Views:
    157
    MePadre
    Aug 6, 2003
  5. Lakshmi Narayanan.r

    How to handle Memo/text field in Mysql with Asp

    Lakshmi Narayanan.r, Dec 22, 2003, in forum: ASP General
    Replies:
    1
    Views:
    258
    Ray at
    Dec 22, 2003
Loading...

Share This Page