We help IT Professionals succeed at work.

How split odbc search result in several Pages?

erot
erot asked
on
Medium Priority
228 Views
Last Modified: 2010-04-09
Based on the client's input I want to give him the result in several pages instead of one large one. The first page (result) shall show up by default. If there are more pages
the user shall be able to push "next n", "prev n" or similar to get the results on the next/prev page and so on..

I hope this can be solved without first generating all the
pages based on the clients search criteria, before sending the first page to the client, to reduce the payload on the server.

I'm familiar with using ASP to retrieve data from an AccessDB using ODBC.
I use FrontPage98, Access, with the ability to use ASP-pages

If you have suggestions to articles about the issue..nice..
If not but you know how to do it and can give me an example..nice2..

Comment
Watch Question

Commented:
Why don't you set session variables for each person and then when they request an item, search until x = number they want.  At that time, record the index Number in the Access DB... Then, whn they click next, go directly to that index and search from there...

1) this assumes that each record has a distinct identificaion number (autonumber works)...
2) not sure how you would implement a previous list unless you save those to disk...

- Matt

If you want, I will try to round some source together.

Commented:
Please read the abve response....

Author

Commented:
I can see you know how to...but there is no examples or
links to sites with examples...

I would very much like to see the source that you mentioned!!

erot

Commented:
Give me about a day or two and I will put something together....

Author

Commented:
Thank you...MasseyM i'm looking forward to the result!!!

Erot

Commented:
How many data in your database?

Commented:
Same question as wanted... Also, what kind of information?  I am almost finished with the code... I will email it to you asap... what is your email address?
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
SEARCH.ASP
--------------------------------------------
<%
Param = Request.QueryString("Param")
Data = Request.QueryString("Data")
%>
<%
If IsObject(Session("SEARCH_conn")) Then
    Set conn = Session("SEARCH_conn")
Else
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "SEARCH","",""
    Set Session("SEARCH_conn") = conn
End If
%>
<%
    sql = "SELECT Items.ItemID, Items.URL, Items.Description, Items.KeyWords "
    sql = sql & "FROM [Items] "
    If cstr(Param) <> "" And cstr(Data) <> "" Then
                      '    sql = sql & " WHERE [" & cstr(Param) & "] = " & cstr(Data)
    End If
    sql = sql & "ORDER BY Items.ItemID;"    
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, conn, 3, 3
%>

<% SearchString = Request.Form("SearchString") %>
<% TotalItemsWanted = Request.Form("TotalItemsWanted") %>
<% NumberToSkip = Request.Form("Skip") %>
<% If NumberToSkip = "" then NumberToSkip=0 %>
<TABLE>
<TH>Searching for: <%=SearchString%> </TH><TH>Displaying <%=TotalItemsWanted%> items per session.

<% If NumberToSkip > 0 then %>
<% Skipped = 0 %>
      <%
      On error Resume Next
      rs.MoveFirst
      do while not rs.eof and CInt(Skipped) < CInt(NumberToSkip)
      %>
      <% if Instr(UCase(rs.Fields("Description")), UCase(SearchString)) then %>
      <% Skipped = Skipped + 1 %>
      <% End If %>
      <%
      If Skipped < NumberToSkip then rs.MoveNext
      loop
      %>
<% End If %>
<% TotalItemsInDB = Skipped %>
<% CurrentItemShown = 1 %>

<%
On Error Resume Next
do while Not rs.eof
%>

      <% If InStr(UCase(rs.Fields("Description")), UCase(SearchString)) then %>
            <% TotalItemsInDB = TotalItemsInDb + 1 %>
            <% If cint(CurrentItemShown) <= cint(TotalItemsWanted) then %>
                  <TR>
                  <TD><% =rs.Fields("ItemID") %></TD>
                  <TD><% =rs.Fields("Description") %></TD>
                  <TD><A HREF="<% =rs.Fields("URL") %>">Go There</A></TD>
                  </TR>
            <% End If %>
            <% CurrentItemShown = CurrentItemShown + 1 %>
      <% End If %>
<%
rs.MoveNext
loop%>
</TABLE>
<FORM ACTION="Search.Asp" METHOD=POST>
<% SkipThis =cint(NumberToSkip) + cint(TotalItemsWanted) %>
<INPUT TYPE=HIDDEN NAME="Skip" Value="<% =SkipThis%>">
<% MsgBox(Request.Form("Skip") + TotalItemsWanted ) %>
<INPUT TYPE=TEXT NAME="SearchString" VALUE="<%=Request.Form("SearchString")%>">
Locate <SELECT NAME="TotalItemsWanted">
<OPTION>1</OPTION>
<OPTION>2</OPTION>
<OPTION>3</OPTION>
<OPTION>4</OPTION>
<OPTION SELECTED>5</OPTION>
</SELECT> Matches.
<% If SkipThis< TotalItemsInDB Then %><INPUT TYPE=SUBMIT><% End If %>
</FORM>
Located <I><% =TotalItemsInDB %></I> total Matches.

<% rs.close %>
<% TotalItemsInDB = 0 %>
<% TotalItemsWanted = 0 %>

SEARCHFORM.ASP
----------------------------------
<FORM ACTION="Search.Asp" METHOD=POST>
<INPUT TYPE=HIDDEN NAME="Skip" Value="0">
<INPUT TYPE=TEXT NAME="SearchString" VALUE="">
<INPUT TYPE=SUBMIT>
Locate <SELECT NAME="TotalItemsWanted">
<OPTION>1</OPTION>
<OPTION>2</OPTION>
<OPTION>3</OPTION>
<OPTION>4</OPTION>
<OPTION SELECTED>5</OPTION>
</SELECT> Matches.
</FORM>
<% TotalItemsInDB = 0 %>
<% TotalItemsWanted = 0 %>

Author

Commented:
...have been sleeping....
  here are the fields in the table...
  Country (= Name of country) can be "Countryname" or "All".
  Type    (= Long, Short, Fat, Thin) can be "One of those 4" or
           all.
  CustomSearch (can be every string)

  The customer can choose none, one or a combination.
  (country and type from a dropdownlist. CustomSearch
  by filling out a Text box.

  my E-mail are krudtaa@yahoo.com

Commented:
If you REALLY want me to, I will customize this to your database... Just email me your database (So I don't have to populate my own) and I will do it...  (ut, just remember, this would normally be $100/hr by any consultant (Taken me about 3 hours thus far).. but I am happy to do so because I am going to use the code as well...
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.