paging through a recordset is being a pain

[storm1976]

would like a bit of help on this one here is my sql query:

"SELECT Contacts_ID, Campaign_ID, Name, Address1, Address2, Town, Contact_name, Telephone " & _
"FROM Contacts " & _
"WHERE " & fld_searchby & " LIKE '%" & txt_searchfor & "%' AND Campaign_ID = " & icampaign & " " & _
"ORDER BY Contacts_ID "

i have tried to page through this recordset but it either tells me i havent put in the variables fld_searchby and txt_searchfor. however when i put them in, through querystrings, each page when moving through the recordset brings back the same page as if it was opening the recordset for the first time.

just a bit confused

cheers in advance
storm

 

[rossno]

Hi storm1976

Apparently, you have defined fld_searchby, etc. previous to the generation of yer select stmt.

I think you gotta build yer select stmt string with yer fle_searchby similar to:

...&quot;WHERE&quot; & &quot;<%=fld_searchby%>&quot; & ... etc.


Are you marching thru the recordset with something like this?

<% while not resultSet.EOF %>

... do your stuff on the current row

<% resultSet.MoveNext
wend %>
...

Lemme know what happens, Norm

 

[JamesLean]

rossno, you don't need the ASP tags there as you are already in an ASP code section! storm's code looks OK for building the SQL.

You are right though about using rs.MoveNext. Have you got this in your loop, storm? --James

 

[BDC2]

Do you need to add fld_searchby to your SELECT statement?

&quot;SELECT fld_searchby, Contacts_ID, ...
BDC.

 

[storm1976]

variables declared:

fld_searchby = Trim(Request(&quot;lst_searchby&quot)
txt_searchfor = Trim(Request(&quot;txt_searchfor&quot)
icampaign = CInt(request(&quot;campaign&quot)

looping through the recordset mentioned previously:
iRecordsShown = 0

do while iRecordsShown < iPageSize AND NOT rs.eof

response.write Rs(&quot;Contacts_ID&quot & &quot;<br>Establishment Name =&quot; & Rs(&quot;Name&quot & &quot;,<br>Address1 = &quot; & Rs(&quot;Address1&quot & &quot;,<br>Address2 = &quot; & Rs(&quot;Address2&quot & &quot;,<br>Town = &quot; & Rs(&quot;Town&quot & &quot;<br><br>&quot;

iRecordsShown = iRecordsShown + 1
if not rs.eof then rs.movenext
loop

the reason for it being like this is its just in the test stage at present.

im trying to use ado to page through having 10 results per page. the problem is when moving to the next page.

i have tried to add the variables into the links to other pages also:

query = &quot;campaign=&quot; & icampaign & &quot;&lst_searchby=&quot; & fld_searchby & &quot;&txt_searchfor=&quot; & txt_searchfor

then in the link:
<a href=&quot;searchresults.asp?<%=query%>&ipag=<%=ipagecurrent +1%>&quot;>

is it any clearer? s