Problem with Paging and Next 10 Records

Results 1 to 7 of 7

Thread: Problem with Paging and Next 10 Records

  1. #1
    Join Date
    Dec 1969
    Posts
    13

    Default Problem with Paging and Next 10 Records

    Using the coding below, my query returns the recordset, but no information is found. The table header information shows up, but there is no information in the table. When I click on the NEXT button, I get the following error: <BR><BR>"Microsoft OLE DB Provider for ODBC Drivers error &#039 80040e14&#039[Microsoft][ODBC Microsoft Access Driver] Syntax error in WHERE clause. /test8/search.asp, line 101.<BR><BR>If I delete the lines that deal with the page counting, the script works fine. All records pertaining to the query appear in one table (recordset). Basically, the query works, the paging doesn&#039t. Any help with this would be appreciated. If it matters, the database is Access 97 and I am using Win98 and PWS to do the testing.<BR><BR>&#060;%@ LANGUAGE="VBSCRIPT" &#037;&#062;<BR>&#060;% Option Explicit &#037;&#062;<BR>&#060;!--#include file="adovbs.inc"--><BR><BR>&#060;%<BR>&#039Set how many records per per we want<BR>Const NumPerPage = 10<BR><BR>&#039Retrieve what page we&#039re currently on<BR>Dim CurrentPage<BR>If Request.QueryString("CurrentPage") = "" then<BR>CurrentPage = 1 &#039We are on the first page<BR>Else<BR>CurrentPage = Request.QueryString("CurrentPage")<BR>End If<BR><BR>&#039Pass in the table to search, and the field to search, and, optionally, the column to sort by<BR>Dim strFieldName<BR>Dim strTableName<BR>Dim strOrderByField<BR>Dim SValue<BR>Dim SType<BR>strFieldName = Request("Column")<BR>strTableName = Request("Table")<BR>strOrderByField = Request("Field")<BR>&#039SValue = Seach String From Form / QueryString<BR>&#039SType = Search Type (All Words or Any Word) From Form / QueryString<BR>SValue = Request("SValue")<BR>SType = Request("SType")<BR><BR>&#039For Loop To Move Through Search String, Counting Spaces, and<BR>&#039Incrementing WordCounter By One At Each Occurance<BR>Dim i<BR>Dim WordCounter<BR>For i = 1 to len(SValue)<BR> If mid(SValue, i, 1) = " " Then <BR> WordCounter = WordCounter + 1<BR> End If<BR>Next<BR><BR>&#039Now, We Add One To Include The First Word<BR>WordCounter = WordCounter + 1<BR><BR>&#039Next We Dim Word As An Array, With The Maximum Number Of Words To Allow - In This Case, 100<BR>Dim Word(100)<BR><BR>&#039Now, We Fill The Array With The Words<BR>Dim CurrentWord<BR>CurrentWord = 1<BR>For i = 1 to len(SValue)<BR> If mid(SValue, i, 1) = " " Then<BR> CurrentWord = CurrentWord + 1<BR> Else<BR> Word(CurrentWord) = Word(CurrentWord) + mid(SValue, i, 1)<BR> End If<BR>Next<BR><BR>&#039Now Lets Build The SQL Statement Based On What Search Type (SType) Was Selected<BR><BR>&#039First Part Of SQL<BR>Dim SQL<BR>SQL = "SELECT * FROM books WHERE "<BR><BR>&#039For Loop To Concatenate SQL String Together<BR>Dim SQL1<BR>For i = 1 to WordCounter<BR> If SType = "AllWords" Then<BR> If i &#060;> WordCounter Then<BR> SQL1 = SQL1 & strFieldName & " LIKE &#039%" & Word(i) & "%&#039 AND "<BR> ElseIf i = WordCounter Then<BR> SQL1 = SQL1 & strFieldName & " LIKE &#039%" & Word(i) & "%&#039"<BR> End If<BR> End If<BR>Next<BR> <BR>&#039Finishing Part Of SQL Statement.<BR>if Len(strOrderByField) > 0 then<BR>&#039We need to perform an ORDER BY!<BR> SQL = SQL & SQL1 & " ORDER BY " & strOrderByField<BR>Else<BR> SQL = SQL & SQL1<BR>End If<BR>&#037;&#062;<BR><BR>&#060;%<BR>&#039Connect to our database.<BR>Dim dbConn<BR>Set dbConn = Server.CreateObject("ADODB.Connection")<BR>dbConn. Open "DSN=books"<BR><BR>&#039Create an explicit recordset object...<BR>Dim rsRecords<BR>Set rsRecords = Server.CreateObject("ADODB.Recordset")<BR><BR>&#03 9Set the cursor location property<BR>rsRecords.CursorLocation = adUseClient<BR><BR>&#039Set the cache size to equal the number of records per page<BR>rsRecords.CacheSize = NumPerPage<BR><BR>rsRecords.Open SQL, dbConn<BR><BR>If rsRecords.EOF then<BR>response.write "No records in the database"<BR>Else<BR><BR>rsRecords.MoveFirst<BR>rs Records.PageSize = NumPerPage<BR><BR>&#039Get the maximum number of pages<BR>Dim TotalPages<BR>TotalPages = rsRecords.PageCount<BR><BR>&#039Set the absolute page<BR>rsRecords.AbsolutePage = CurrentPage<BR><BR>&#039Counting variable for our recordset<BR>Dim count<BR>&#037;&#062;<BR><BR>&#060;HTML>&#060;HEAD >&#060;TITLE>&#060;/TITLE><BR>&#060;/HEAD><BR>&#060;BODY><BR><BR>&#060;%&#039Print out the current page number and total pages<BR>Response.Write("Page " & CurrentPage & " of " & TotalPages & "&#060;p>")<BR>&#037;&#062;<BR><BR><BR>&#060;T ABLE BORDER=1><BR>&#060;tr>&#060;th>Key&#060;/th>&#060;th>Authors&#060;/th>&#060;th>Titles&#060;/th>&#060;th>Have?&#060;/th>&#060;/tr><BR><BR>&#060;%<BR>&#039Set count equal to zero and Count<BR>Count = 0<BR>Do While Not rsRecords.EOF &#060; rsRecords.PageSize<BR>response.write("&#060;TR>")< BR>&#037;&#062;<BR><BR>&#060;%<BR>If rsRecords("Key")&#060;>"" Then<BR>response.write("&#060;td>&#060;font size=2>&#060;a href=details.asp?Key=" & rsRecords("Key") & ">Details&#060;/a>&#060;/font>&#060;/td>")<BR>End If<BR>&#037;&#062;<BR> <BR>&#060;%<BR>If rsRecords("Authors")&#060;>"" Then<BR>response.write("&#060;td>&#060;font size=2>" & rsRecords("Authors") & "&#060;/font>&#060;/td>")<BR>End If<BR>&#037;&#062;<BR> <BR>&#060;%<BR>If rsRecords("Title")&#060;>"" Then<BR>response.write("&#060;td>&#060;font size=2>" & rsRecords("Title") & "&#060;/font>&#060;/td>")<BR>End If<BR>&#037;&#062;<BR> <BR>&#060;%<BR>If rsRecords("Have?")&#060;>"" Then<BR>response.write("&#060;td>&#060;font size=2>" & rsRecords("Have?") & "&#060;/font>&#060;/td>")<BR>End If<BR>&#037;&#062;<BR><BR>&#060;/tr><BR><BR>&#060;%<BR>Count = Count + 1<BR>rsRecords.MoveNext<BR>Loop<BR>&#037;&#062;<BR >&#060;/TABLE><BR><BR>&#060;form><BR>&#060;%<BR>&#039Displ ay Next / Previous buttons<BR>If CurrentPage > 1 then<BR>&#039We are not at the beginning, show the previous button<BR>Response.Write("&#060;INPUT TYPE=BUTTON VALUE=PREVIOUS ONCLICK=""document.location.href=&#039search.asp?C urrentPage=" & CurrentPage - 1 & "&#039"">")<BR>End If<BR><BR>If CInt (CurrentPage) &#060;> CInt (TotalPages) then<BR>&#039We are not at the end, show a next button<BR>Response.Write("&#060;INPUT TYPE=BUTTON VALUE=NEXT ONCLICK=""document.location.href=&#039search.asp?C urrentPage=" & CurrentPage + 1 & "&#039"">")<BR>End If<BR>&#037;&#062;<BR>&#060;/form><BR><BR>&#060;%<BR>rsRecords.Close<BR>Set RsRecords = nothing<BR>dbConn.Close<BR>Set dbConn = nothing<BR>&#037;&#062;<BR><BR>&#060;/BODY><BR>&#060;/HTML><BR>&#060;%End If&#037;&#062;

  2. #2
    Join Date
    Dec 1969
    Posts
    2,849

    Default RE: Problem with Paging and Next 10 Records

    The problem doesn&#039t have to do with paging, it has to do with your construction of the SQL string. What is line 101? I assume it&#039s the line where you issue the .Open method on the recordset object. Right before that line, put:<BR><BR>Response.Write "SQL String = " & SQL<BR><BR>I am willing to bet money that your SQL string is messed up. See, when you call the next page, you have to pass all of those variables again! You are not passing anything but CurrentPage when you click the next button. You need to also pass the variables Column, Field, Table, etc.

  3. #3
    Join Date
    Dec 1969
    Posts
    13

    Default RE: Problem with Paging and Next 10 Records

    Scott,<BR>I probably should have mentioned that I have almost no experience working with ASP&#039s and SQL (rather obvious). The coding that I am using has come from various examples and tutorials on the web which I piecemealed together.<BR><BR>I copied the line: <BR>Response.Write "SQL String = " & SQL<BR>right before the line:<BR>rsRecords.Open SQL, dbConn<BR><BR>in my script. When I fill out my form and send the query, this is what appears above my recordset:<BR><BR>SQL String = SELECT * FROM baseballbooks WHERE Authors LIKE &#039%roger%&#039 AND Authors LIKE &#039%kahn%&#039 ORDER BY Key Page 1 of 2<BR>(this is correct, right????)<BR><BR>My table (recordset from the query) holds no information, just the table header info (4 cells with the names - Key, Authors, Title and Have). 14 records should have been returned, 10 on the first page and 4 on the second (if this had worked). I also have a NEXT button, below the table, which produced the error message that I mentioned in the first post.<BR><BR>Does this make any sense? Not sure what to try next. Any help would be greatly appreciated. <BR><BR>

  4. #4
    Join Date
    Dec 1969
    Posts
    2,849

    Default RE: Problem with Paging and Next 10 Records

    When you click the next button, what does the SQL read? I assume the SQL you pasted above works fine, right? You see the first 10 records just peachy, right?<BR><BR>Now, when you load up the second page, since you are not passing all of your parameters, you will get a screwed up SQL statement. Share that one with us...


  5. #5
    Join Date
    Dec 1969
    Posts
    13

    Default RE: Problem with Paging and Next 10 Records

    Unfortuately, I do not see the first 10 records - just a html table with 4 cells (Key, Authors, Title and Have?) and the NEXT button. I also have this showing up as the first line on the page: SQL String = SELECT * FROM books WHERE Authors LIKE &#039%kahn%&#039 ORDER BY Key Page 1 of 2. This would be from my inserting the line: <BR>Response.Write "SQL String = " & SQL<BR>just before the line: <BR>rsRecords.Open SQL, dbConn<BR><BR>Clicking the NEXT button causes the following error:<BR><BR>SQL String = SELECT * FROM books WHERE <BR>Microsoft OLE DB Provider for ODBC Drivers error &#039 80040e14&#039 <BR>[Microsoft][ODBC Microsoft Access Driver] Syntax error in WHERE clause. <BR>/test9/searchfield4.asp, line 103<BR><BR>If I delete or rem out the coding that has to do with the paging and next ten records, the query works fine (returns table with 15 records).<BR> <BR>

  6. #6
    Join Date
    Dec 1969
    Posts
    69

    Default RE: Problem with Paging and Next 10 Records

    Uf, I myself am quit new in this business, but I thing I know what your problem is. I cannot go through you example, but if you send me your e-mail I would send you a complet page I use in my project. I hope that would be of some help. My e-mail is stevang@mail.com

  7. #7
    Join Date
    Dec 1969
    Posts
    13

    Default RE: Problem with Paging and Next 10 Records

    Thanks, will do.<BR><BR>I guess cutting and pasting is not always the correct way to do things. The reason my first 10 records did not show is because I deleted the words "and Count" from the line:<BR>Do While Not rsRecords.EOF and Count &#060; rsRecords.PageSize<BR><BR>I still cannot get the NEXT button to return the next 10 records. Error message remains the same. Getting closer though.<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •