ASP Paging on a searched memo field

[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?

 

[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.

 

[Michael D. Kersey]

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