query search on access db ... scratching my hair ... help plz

Discussion in 'ASP General' started by cooldv, Sep 14, 2003.

  1. cooldv

    cooldv Guest

    I have posted this Q earlier also, but NO solution so far. I still
    hope there must be someone with a solution.

    My Q: displaying only SPECIFIC records from an access db with ASP
    query.

    e.g. let's say there r 700 records in a DB; out of that 50 are from
    NY, 100 from NJ and rest from other states.

    (i) If i want to display all 700 records, then the script works fine,
    the paging function (previous 1 2 3 4 .... next) works perfectly.

    (ii) Now, if i run a query and want to display only the records from
    NY >> then the trouble starts.

    the results of query: number of records (50), number of pages (3 at 20
    records per page), and its display on first page (first 20 records)
    --- all these are returned correctly. BUT,
    the next pages of the query results (the 21-40 records on page 2 and
    41-50 on page 3) are not displayed.
    Intead all the 700 records start getting displayed from next and
    previous querystring buttons.

    here is the demo i have put up:
    http://www.as.pgims.org/query/query.asp

    Any kind souls out there???
     
    cooldv, Sep 14, 2003
    #1
    1. Advertising

  2. cooldv

    Ken Schaefer Guest

    You need to pass your selection criteria from page to page. Otherwise, when
    you go to the next page, your criteria is not being applied,and the whole
    resultset (all 700 records) are being returned.

    Cheers
    Ken

    "cooldv" <> wrote in message
    news:...
    : I have posted this Q earlier also, but NO solution so far. I still
    : hope there must be someone with a solution.
    :
    : My Q: displaying only SPECIFIC records from an access db with ASP
    : query.
    :
    : e.g. let's say there r 700 records in a DB; out of that 50 are from
    : NY, 100 from NJ and rest from other states.
    :
    : (i) If i want to display all 700 records, then the script works fine,
    : the paging function (previous 1 2 3 4 .... next) works perfectly.
    :
    : (ii) Now, if i run a query and want to display only the records from
    : NY >> then the trouble starts.
    :
    : the results of query: number of records (50), number of pages (3 at 20
    : records per page), and its display on first page (first 20 records)
    : --- all these are returned correctly. BUT,
    : the next pages of the query results (the 21-40 records on page 2 and
    : 41-50 on page 3) are not displayed.
    : Intead all the 700 records start getting displayed from next and
    : previous querystring buttons.
    :
    : here is the demo i have put up:
    : http://www.as.pgims.org/query/query.asp
    :
    : Any kind souls out there???
     
    Ken Schaefer, Sep 14, 2003
    #2
    1. Advertising

  3. cooldv

    cooldv Guest

    hi ken,

    thanks for ur suggestion. can you tell me how do u *pass your
    selection criteria from page to page* as you mentioned.
     
    cooldv, Sep 15, 2003
    #3
  4. cooldv

    Bob Barrows Guest

    cooldv wrote:
    > hi ken,
    >
    > thanks for ur suggestion. can you tell me how do u *pass your
    > selection criteria from page to page* as you mentioned.


    In a hidden form field? As part of the querystring? In a Session variable?
    Take your pick.
     
    Bob Barrows, Sep 15, 2003
    #4
  5. cooldv

    Ken Schaefer Guest

    If you are using Form posts go to from page to page, then you'll need to
    create hidden form inputs, eg:

    <input type="hidden" name="txtState" value="<%=strState%>">

    and on the next page, check to see if there is a value in
    Request.Form("txtState"), and if so, you include that in the WHERE clause of
    your SQL statement - *the same way that you created the recordset in the
    first place, for the first page of results*

    If you are using plain hyperlinks, you can do this via the QueryString.

    Cheers
    Ken

    "cooldv" <> wrote in message
    news:...
    : hi ken,
    :
    : thanks for ur suggestion. can you tell me how do u *pass your
    : selection criteria from page to page* as you mentioned.
     
    Ken Schaefer, Sep 15, 2003
    #5
  6. cooldv

    cooldv Guest

    Hi Ken,

    excuse my dumbo brain! just how do you write the code for what you
    suggested?
    (the demo of my trouble and the code of the file is also shown at:
    http://www.as.pgims.org/query/query.asp)

    here is my code:
    <%
    PageNo = Request.QueryString("Page")
    IF isNumeric(PageNo) THEN
    PageNo = CLng(PageNo)
    END IF
    IF PageNo < 1 THEN PageNo = 1

    set conn = server.createobject("adodb.connection")
    DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
    DSNtemp=dsntemp & "DBQ=" & server.mappath("db.mdb")
    conn.Open DSNtemp

    dim givenn
    dim familyn
    dim newman
    dim siti
    dim prov
    dim cantree

    givenn=request.form("ftn")
    familyn=request.form("ltn")
    newman=request.form("fresher")
    siti=request.form("cty")
    prov=request.form("stt")
    cantree=request.form("ctry")

    If givenn = "any" or givenn = "" then
    givenn = ""
    End If
    If familyn = "any" or familyn = "" then
    familyn = ""
    End If
    If newman = "0000" or newman = "" then
    newman = ""
    End If
    If siti = "any" or siti = "" then
    siti = ""
    End If
    If prov = "any" or prov = "" then
    prov=""
    End If
    If cantree = "any" or cantree = "" then
    cantree = ""
    End If

    sqlstmt = "SELECT * from database WHERE firstnam like '%"& givenn &"%'
    and lastnam like '%"& familyn &"%' and census like '%"& newman &"' and
    city like '%"& siti &"%' and state like '%"& prov &"%' and country
    like '%"& cantree &"%' ORDER by lastname"

    TotalRecs = rs.recordcount
    rs.Pagesize=5
    TotalPages = cInt(rs.pagecount)
    rs.absolutepage=PageNo

    <%
    If PageNo > 1 Then
    %>
    <a href="dbQ.asp?page=<%= PageNo - 1 %>">&lt;&lt;
    Prev</a>&nbsp;&nbsp;
    <%
    End If
    %>

    <%
    response.write "Page " & PageNo & " of " & TotalPages & " "
    DO WHILE iMenuCount <= TotalPages
    Response.Write "<a href=""dbQ.asp?Page=" & iMenuCount
    IF PageNo = iMenuCount THEN
    Response.Write """ class=""selected"">" & iMenuCount & "</a>&nbsp;"
    ELSE
    Response.Write """ title=""Page " & iMenuCount & """>" & iMenuCount
    & "</a>&nbsp;"
    END IF
    iMenuCount = iMenuCount + 1
    Loop

    If PageNo < TotalPages Then
    %>
    <a href="dbQ.asp?Page=<%= PageNo + 1 %>">Next &gt;&gt;</a>
    <%
    End If
    %>


    "Ken Schaefer" <> wrote in message news:<>...
    > If you are using Form posts go to from page to page, then you'll need to
    > create hidden form inputs, eg:
    >
    > <input type="hidden" name="txtState" value="<%=strState%>">
    >
    > and on the next page, check to see if there is a value in
    > Request.Form("txtState"), and if so, you include that in the WHERE clause of
    > your SQL statement - *the same way that you created the recordset in the
    > first place, for the first page of results*
    >
    > If you are using plain hyperlinks, you can do this via the QueryString.
    >
    > Cheers
    > Ken
    >
    > "cooldv" <> wrote in message
    > news:...
    > : hi ken,
    > :
    > : thanks for ur suggestion. can you tell me how do u *pass your
    > : selection criteria from page to page* as you mentioned.
     
    cooldv, Sep 16, 2003
    #6
  7. cooldv

    Ken Schaefer Guest

    Where you are creating the "Next" and "Previous" links. eg:

    : <%
    : If PageNo > 1 Then
    : %>
    : <a href="dbQ.asp?page=<%= PageNo - 1 %>">&lt;&lt;
    : Prev</a>&nbsp;&nbsp;
    : <%
    : End If
    : %>

    You see how you are adding the "&page=<%=PageNo-1%>" data to pass to the
    next page? You also need to pass your SQL criteria as well, eg givenN,
    familyN etc.

    On the next page, you need to get that out of the Request.QueryString
    collection (not the Request.Form collection), and recreate your SQL
    statement.

    To streamline everying, I suggest you change the method of your initial form
    from method="post" to method="get". That way you can just access the
    Request.QueryString collection no matter which page the user is coming from.

    Cheers
    Ken



    "cooldv" <> wrote in message
    news:...
    : Hi Ken,
    :
    : excuse my dumbo brain! just how do you write the code for what you
    : suggested?
    : (the demo of my trouble and the code of the file is also shown at:
    : http://www.as.pgims.org/query/query.asp)
    :
    : here is my code:
    : <%
    : PageNo = Request.QueryString("Page")
    : IF isNumeric(PageNo) THEN
    : PageNo = CLng(PageNo)
    : END IF
    : IF PageNo < 1 THEN PageNo = 1
    :
    : set conn = server.createobject("adodb.connection")
    : DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
    : DSNtemp=dsntemp & "DBQ=" & server.mappath("db.mdb")
    : conn.Open DSNtemp
    :
    : dim givenn
    : dim familyn
    : dim newman
    : dim siti
    : dim prov
    : dim cantree
    :
    : givenn=request.form("ftn")
    : familyn=request.form("ltn")
    : newman=request.form("fresher")
    : siti=request.form("cty")
    : prov=request.form("stt")
    : cantree=request.form("ctry")
    :
    : If givenn = "any" or givenn = "" then
    : givenn = ""
    : End If
    : If familyn = "any" or familyn = "" then
    : familyn = ""
    : End If
    : If newman = "0000" or newman = "" then
    : newman = ""
    : End If
    : If siti = "any" or siti = "" then
    : siti = ""
    : End If
    : If prov = "any" or prov = "" then
    : prov=""
    : End If
    : If cantree = "any" or cantree = "" then
    : cantree = ""
    : End If
    :
    : sqlstmt = "SELECT * from database WHERE firstnam like '%"& givenn &"%'
    : and lastnam like '%"& familyn &"%' and census like '%"& newman &"' and
    : city like '%"& siti &"%' and state like '%"& prov &"%' and country
    : like '%"& cantree &"%' ORDER by lastname"
    :
    : TotalRecs = rs.recordcount
    : rs.Pagesize=5
    : TotalPages = cInt(rs.pagecount)
    : rs.absolutepage=PageNo
    :
    : <%
    : If PageNo > 1 Then
    : %>
    : <a href="dbQ.asp?page=<%= PageNo - 1 %>">&lt;&lt;
    : Prev</a>&nbsp;&nbsp;
    : <%
    : End If
    : %>
    :
    : <%
    : response.write "Page " & PageNo & " of " & TotalPages & " "
    : DO WHILE iMenuCount <= TotalPages
    : Response.Write "<a href=""dbQ.asp?Page=" & iMenuCount
    : IF PageNo = iMenuCount THEN
    : Response.Write """ class=""selected"">" & iMenuCount & "</a>&nbsp;"
    : ELSE
    : Response.Write """ title=""Page " & iMenuCount & """>" & iMenuCount
    : & "</a>&nbsp;"
    : END IF
    : iMenuCount = iMenuCount + 1
    : Loop
    :
    : If PageNo < TotalPages Then
    : %>
    : <a href="dbQ.asp?Page=<%= PageNo + 1 %>">Next &gt;&gt;</a>
    : <%
    : End If
    : %>
    :
    :
    : "Ken Schaefer" <> wrote in message
    news:<>...
    : > If you are using Form posts go to from page to page, then you'll need to
    : > create hidden form inputs, eg:
    : >
    : > <input type="hidden" name="txtState" value="<%=strState%>">
    : >
    : > and on the next page, check to see if there is a value in
    : > Request.Form("txtState"), and if so, you include that in the WHERE
    clause of
    : > your SQL statement - *the same way that you created the recordset in the
    : > first place, for the first page of results*
    : >
    : > If you are using plain hyperlinks, you can do this via the QueryString.
    : >
    : > Cheers
    : > Ken
    : >
    : > "cooldv" <> wrote in message
    : > news:...
    : > : hi ken,
    : > :
    : > : thanks for ur suggestion. can you tell me how do u *pass your
    : > : selection criteria from page to page* as you mentioned.
     
    Ken Schaefer, Sep 16, 2003
    #7
    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. Replies:
    1
    Views:
    408
  2. Replies:
    2
    Views:
    359
    Nick Keighley
    Nov 24, 2006
  3. rogerp
    Replies:
    0
    Views:
    557
    rogerp
    Mar 22, 2009
  4. rogerp
    Replies:
    0
    Views:
    593
    rogerp
    Mar 22, 2009
  5. Nicolas
    Replies:
    0
    Views:
    735
    Nicolas
    Apr 24, 2009
Loading...

Share This Page