By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,069 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

ASP vbscript recordset paging headache

P: 2
Hi,

I am having a problem using Dreamweaver CS3 standard recordset paging behavior. It doesn’t seem to work when I pass parameter values from a FORM on my search page, to the recordset on my results page.

- Recordset Paging works if no parameters are used in the recordset sql code (ie. simple sql code):

SELECT *
FROM db_name
WHERE (db_field1 LIKE ‘%text1%’ OR db_field2 LIKE ‘%text2%’)


- Recordset Paging works if I pass the same text to the sql code using parameters set within the recordset:

SELECT *
FROM db_name
WHERE (db_field1 LIKE %parameter1% OR db_field2 LIKE %parameter2%)

- inside the recordset parameter1 was set to ‘text1’, parameter2 was set to ‘text2


- However if I pass the same text (text1, text2) from a FORM on a search page to the recordset using recordset parameters, the Recordset Paging fails.

SELECT *
FROM db_name
WHERE (db_field1 LIKE %parameter1% OR db_field2 LIKE %parameter2%)

- inside the recordset parameter1 and parameter2 were set using the Request.Form("….")
- on the FORM the values of the fields were set to text1, text2


The form, database, sql code all work fine. After I perform a search, the correct database results are displayed on my first results.asp page. My problem is when I try to use recordset paging. For some reason when I try to page through the results I get the following error:

Error Type:
ADODB.Field (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

I have completely striped my code to the bare essentials, and conclude that the search criteria are not being sent to the results page each time I click “Next” etc. Therefore each time I try to page through the results, the recordset values are all empty and the EOF value is returned.

Please have a quick look at the code below. Any help, advice, suggestions or other scripts (javascript or vbscript) that will work instead will be gratefully accepted. I have been trying to fix this for days and it’s driving me crazy.


To help you understand the code here is a brief explanation of its purpose, a list of the variables used and the error produced.

- takes 2 search fields from FORM on search page (SearchValue1 = city, SearchValue2 = country)
- compares with database field (Keywords) for any matches (database name = test20.mdb)
- outputs the text contents of database field (Image_Name)
- the text outputs are displayed one per page eg. page 1 = record 1, page2 = record 2, page 3 = record 3 ....
- a "Next" link pages through the displayed results

File Name = results.asp
Connection File Name = con_cwphoto.asp
Recordset Name = rs
Database name = test20.mdb (MSAccess)
Database Field Names = Keywords, Image_Name
FORM Fields (on search.asp page) = SearchValue1 (city), SearchValue2 (country)


The URL passed by clicking the “Next” link

http://localhost/conor%20wall%20phot...s/results.asp? SearchValue1=city&SearchValue2=country&submit=Subm it&index=1


It seems to pass the search criteria, but maybe it isn’t being passed correctly.


The error:

Error Type:
ADODB.Field (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

The line the error points to:

Expand|Select|Wrap|Line Numbers
  1. <p><%=(rs.Fields.Item("Image_Name").Value)%></p>
  2.  
This line is in the html tags and displays the contents of the “Image_Name” database field.



The CS3 recordset paging behavior code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
  3. <!--#include file="../Connections/con_cwphoto.asp" -->
  4. <%
  5. ‘ Get search criteria from FORM on the search page 
  6. ‘ (FORM has 2 fields - SearchValue1 and SearchValue2)
  7.  
  8. Dim rs__SearchValue1
  9. rs__SearchValue1 = "NULL"
  10. If (Request.Form("SearchValue1") <> "") Then 
  11.   rs__SearchValue1 = Request.Form("SearchValue1")
  12. End If
  13. %>
  14. <%
  15. Dim rs__SearchValue2
  16. rs__SearchValue2 = "NULL"
  17. If (Request.Form("SearchValue2") <> "") Then 
  18.   rs__SearchValue2 = Request.Form("SearchValue2")
  19. End If
  20. %>
  21.  
  22. <%
  23. ' set up connection (con_cwphoto)
  24. ' SQL code compares search criteria with "Keywords" db field in "test20.mdb" db
  25.  
  26. Dim rs
  27. Dim rs_cmd
  28. Dim rs_numRows
  29.  
  30. Set rs_cmd = Server.CreateObject ("ADODB.Command")
  31. rs_cmd.ActiveConnection = MM_con_cwphoto_STRING
  32. rs_cmd.CommandText = "SELECT * FROM test20 WHERE (Keywords LIKE ? OR Keywords LIKE ?)" 
  33. rs_cmd.Prepared = true
  34. rs_cmd.Parameters.Append rs_cmd.CreateParameter("param1", 200, 1, 255, "%" + rs__SearchValue1 + "%") ' adVarChar
  35. rs_cmd.Parameters.Append rs_cmd.CreateParameter("param2", 200, 1, 255, "%" + rs__SearchValue2 + "%") ' adVarChar
  36.  
  37. Set rs = rs_cmd.Execute
  38. rs_numRows = 0
  39. %>
  40. <%
  41. '  *** Recordset Stats, Move To Record, and Go To Record: declare stats variables
  42.  
  43. Dim rs_total
  44. Dim rs_first
  45. Dim rs_last
  46.  
  47. ' set the record count
  48. rs_total = rs.RecordCount
  49.  
  50. ' set the number of rows displayed on this page
  51. If (rs_numRows < 0) Then
  52.   rs_numRows = rs_total
  53. Elseif (rs_numRows = 0) Then
  54.   rs_numRows = 1
  55. End If
  56.  
  57. ' set the first and last displayed record
  58. rs_first = 1
  59. rs_last  = rs_first + rs_numRows - 1
  60.  
  61. ' if we have the correct record count, check the other stats
  62. If (rs_total <> -1) Then
  63.   If (rs_first > rs_total) Then
  64.     rs_first = rs_total
  65.   End If
  66.   If (rs_last > rs_total) Then
  67.     rs_last = rs_total
  68.   End If
  69.   If (rs_numRows > rs_total) Then
  70.     rs_numRows = rs_total
  71.   End If
  72. End If
  73. %>
  74. <%
  75. Dim MM_paramName 
  76. %>
  77. <%
  78. ' *** Move To Record and Go To Record: declare variables
  79.  
  80. Dim MM_rs
  81. Dim MM_rsCount
  82. Dim MM_size
  83. Dim MM_uniqueCol
  84. Dim MM_offset
  85. Dim MM_atTotal
  86. Dim MM_paramIsDefined
  87.  
  88. Dim MM_param
  89. Dim MM_index
  90.  
  91. Set MM_rs    = rs
  92. MM_rsCount   = rs_total
  93. MM_size      = rs_numRows
  94. MM_uniqueCol = ""
  95. MM_paramName = ""
  96. MM_offset = 0
  97. MM_atTotal = false
  98. MM_paramIsDefined = false
  99. If (MM_paramName <> "") Then
  100.   MM_paramIsDefined = (Request.QueryString(MM_paramName) <> "")
  101. End If
  102. %>
  103. <%
  104. ' *** Move To Record: handle 'index' or 'offset' parameter
  105.  
  106. if (Not MM_paramIsDefined And MM_rsCount <> 0) then
  107.  
  108.   ' use index parameter if defined, otherwise use offset parameter
  109.   MM_param = Request.QueryString("index")
  110.   If (MM_param = "") Then
  111.     MM_param = Request.QueryString("offset")
  112.   End If
  113.   If (MM_param <> "") Then
  114.     MM_offset = Int(MM_param)
  115.   End If
  116.  
  117.   ' if we have a record count, check if we are past the end of the recordset
  118.   If (MM_rsCount <> -1) Then
  119.     If (MM_offset >= MM_rsCount Or MM_offset = -1) Then  ' past end or move last
  120.       If ((MM_rsCount Mod MM_size) > 0) Then         ' last page not a full repeat region
  121.         MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
  122.       Else
  123.         MM_offset = MM_rsCount - MM_size
  124.       End If
  125.     End If
  126.   End If
  127.  
  128.   ' move the cursor to the selected record
  129.   MM_index = 0
  130.   While ((Not MM_rs.EOF) And (MM_index < MM_offset Or MM_offset = -1))
  131.     MM_rs.MoveNext
  132.     MM_index = MM_index + 1
  133.   Wend
  134.   If (MM_rs.EOF) Then 
  135.     MM_offset = MM_index  ' set MM_offset to the last possible record
  136.   End If
  137.  
  138. End If
  139. %>
  140. <%
  141. ' *** Move To Record: if we dont know the record count, check the display range
  142.  
  143. If (MM_rsCount = -1) Then
  144.  
  145.   ' walk to the end of the display range for this page
  146.   MM_index = MM_offset
  147.   While (Not MM_rs.EOF And (MM_size < 0 Or MM_index < MM_offset + MM_size))
  148.     MM_rs.MoveNext
  149.     MM_index = MM_index + 1
  150.   Wend
  151.  
  152.   ' if we walked off the end of the recordset, set MM_rsCount and MM_size
  153.   If (MM_rs.EOF) Then
  154.     MM_rsCount = MM_index
  155.     If (MM_size < 0 Or MM_size > MM_rsCount) Then
  156.       MM_size = MM_rsCount
  157.     End If
  158.   End If
  159.  
  160.   ' if we walked off the end, set the offset based on page size
  161.   If (MM_rs.EOF And Not MM_paramIsDefined) Then
  162.     If (MM_offset > MM_rsCount - MM_size Or MM_offset = -1) Then
  163.       If ((MM_rsCount Mod MM_size) > 0) Then
  164.         MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
  165.       Else
  166.         MM_offset = MM_rsCount - MM_size
  167.       End If
  168.     End If
  169.   End If
  170.  
  171.   ' reset the cursor to the beginning
  172.   If (MM_rs.CursorType > 0) Then
  173.     MM_rs.MoveFirst
  174.   Else
  175.     MM_rs.Requery
  176.   End If
  177.  
  178.   ' move the cursor to the selected record
  179.   MM_index = 0
  180.   While (Not MM_rs.EOF And MM_index < MM_offset)
  181.     MM_rs.MoveNext
  182.     MM_index = MM_index + 1
  183.   Wend
  184. End If
  185. %>
  186. <%
  187. ' *** Move To Record: update recordset stats
  188.  
  189. ' set the first and last displayed record
  190. rs_first = MM_offset + 1
  191. rs_last  = MM_offset + MM_size
  192.  
  193. If (MM_rsCount <> -1) Then
  194.   If (rs_first > MM_rsCount) Then
  195.     rs_first = MM_rsCount
  196.   End If
  197.   If (rs_last > MM_rsCount) Then
  198.     rs_last = MM_rsCount
  199.   End If
  200. End If
  201.  
  202. ' set the boolean used by hide region to check if we are on the last record
  203. MM_atTotal = (MM_rsCount <> -1 And MM_offset + MM_size >= MM_rsCount)
  204. %>
  205. <%
  206. ' *** Go To Record and Move To Record: create strings for maintaining URL and Form parameters
  207.  
  208. Dim MM_keepNone
  209. Dim MM_keepURL
  210. Dim MM_keepForm
  211. Dim MM_keepBoth
  212.  
  213. Dim MM_removeList
  214. Dim MM_item
  215. Dim MM_nextItem
  216.  
  217. ' create the list of parameters which should not be maintained
  218. MM_removeList = "&index="
  219. If (MM_paramName <> "") Then
  220.   MM_removeList = MM_removeList & "&" & MM_paramName & "="
  221. End If
  222.  
  223. MM_keepURL=""
  224. MM_keepForm=""
  225. MM_keepBoth=""
  226. MM_keepNone=""
  227.  
  228. ' add the URL parameters to the MM_keepURL string
  229. For Each MM_item In Request.QueryString
  230.   MM_nextItem = "&" & MM_item & "="
  231.   If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then
  232.     MM_keepURL = MM_keepURL & MM_nextItem & Server.URLencode(Request.QueryString(MM_item))
  233.   End If
  234. Next
  235.  
  236. ' add the Form variables to the MM_keepForm string
  237. For Each MM_item In Request.Form
  238.   MM_nextItem = "&" & MM_item & "="
  239.   If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then
  240.     MM_keepForm = MM_keepForm & MM_nextItem & Server.URLencode(Request.Form(MM_item))
  241.   End If
  242. Next
  243.  
  244. ' create the Form + URL string and remove the intial '&' from each of the strings
  245. MM_keepBoth = MM_keepURL & MM_keepForm
  246. If (MM_keepBoth <> "") Then 
  247.   MM_keepBoth = Right(MM_keepBoth, Len(MM_keepBoth) - 1)
  248. End If
  249. If (MM_keepURL <> "")  Then
  250.   MM_keepURL  = Right(MM_keepURL, Len(MM_keepURL) - 1)
  251. End If
  252. If (MM_keepForm <> "") Then
  253.   MM_keepForm = Right(MM_keepForm, Len(MM_keepForm) - 1)
  254. End If
  255.  
  256. ' a utility function used for adding additional parameters to these strings
  257. Function MM_joinChar(firstItem)
  258.   If (firstItem <> "") Then
  259.     MM_joinChar = "&"
  260.   Else
  261.     MM_joinChar = ""
  262.   End If
  263. End Function
  264. %>
  265. <%
  266. ' *** Move To Record: set the strings for the first, last, next, and previous links
  267.  
  268. Dim MM_keepMove
  269. Dim MM_moveParam
  270. Dim MM_moveFirst
  271. Dim MM_moveLast
  272. Dim MM_moveNext
  273. Dim MM_movePrev
  274.  
  275. Dim MM_urlStr
  276. Dim MM_paramList
  277. Dim MM_paramIndex
  278. Dim MM_nextParam
  279.  
  280. MM_keepMove = MM_keepBoth
  281. MM_moveParam = "index"
  282.  
  283. ' if the page has a repeated region, remove 'offset' from the maintained parameters
  284. If (MM_size > 1) Then
  285.   MM_moveParam = "offset"
  286.   If (MM_keepMove <> "") Then
  287.     MM_paramList = Split(MM_keepMove, "&")
  288.     MM_keepMove = ""
  289.     For MM_paramIndex = 0 To UBound(MM_paramList)
  290.       MM_nextParam = Left(MM_paramList(MM_paramIndex), InStr(MM_paramList(MM_paramIndex),"=") - 1)
  291.       If (StrComp(MM_nextParam,MM_moveParam,1) <> 0) Then
  292.         MM_keepMove = MM_keepMove & "&" & MM_paramList(MM_paramIndex)
  293.       End If
  294.     Next
  295.     If (MM_keepMove <> "") Then
  296.       MM_keepMove = Right(MM_keepMove, Len(MM_keepMove) - 1)
  297.     End If
  298.   End If
  299. End If
  300.  
  301. ' set the strings for the move to links
  302. If (MM_keepMove <> "") Then 
  303.   MM_keepMove = Server.HTMLEncode(MM_keepMove) & "&"
  304. End If
  305.  
  306. MM_urlStr = Request.ServerVariables("URL") & "?" & MM_keepMove & MM_moveParam & "="
  307.  
  308. MM_moveFirst = MM_urlStr & "0"
  309. MM_moveLast  = MM_urlStr & "-1"
  310. MM_moveNext  = MM_urlStr & CStr(MM_offset + MM_size)
  311. If (MM_offset - MM_size < 0) Then
  312.   MM_movePrev = MM_urlStr & "0"
  313. Else
  314.   MM_movePrev = MM_urlStr & CStr(MM_offset - MM_size)
  315. End If
  316. %>
  317.  
  318. <html>
  319. <head>
  320. <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  321. <title>Untitled Document</title>
  322. </head>
  323.  
  324. <body>
  325. <p>&nbsp;</p>
  326. <%
  327. ' html code: displays the text from "Image_Name" db field on the results.asp page
  328. ' "Next" link pages through the results (1 by 1)
  329. ' each time it passes a string with the search criteria and the index or offset numbers 
  330. %>
  331. <p><%=(rs.Fields.Item("Image_Name").Value)%></p>
  332. <p>&nbsp;</p>
  333. <p>&nbsp;<a href="<%=MM_moveNext%>">Next</a></p>
  334. <p>&nbsp;</p>
  335. <p>&nbsp;</p>
  336. <p>&nbsp;</p>
  337. </body>
  338. </html>
  339. <%
  340. rs.Close()
  341. Set rs = Nothing
  342. %>
  343.  
  344.  
Jan 15 '08 #1
Share this Question
Share on Google+
2 Replies


P: 2
Hi,

Following the suggestion of a member of a different forum, I have fixed the code error.

The code which gets the search values from the FORM on the search page, must be changed so that it also gets the search values passed in the URL query string. Only one of these occurrences will ever happen. The first time the results.asp page is displayed it will use the search values pulled from the FORM. All other times the results.asp page is displayed (because of recordset paging - Next, Previous …) it will use the same search values, but this time pulled from the URL.

Therefore the code:

Expand|Select|Wrap|Line Numbers
  1. <%
  2. Dim rs__SearchValue1
  3. rs__SearchValue1 = "NULL"
  4. '  Get search criteria from FORM on the search page (FORM has 2 fields - SearchValue1 and SearchValue2)
  5. If (Request.Form("SearchValue1") <> "") Then 
  6.   rs__SearchValue1 = Request.Form("SearchValue1")
  7. End If
  8. %>
  9.  
Must be changed to:

Expand|Select|Wrap|Line Numbers
  1. <%
  2. Dim rs__SearchValue1
  3. rs__SearchValue1 = "NULL"
  4.  
  5. '  Get search criteria from FORM on the search page (FORM has 2 fields - SearchValue1 and SearchValue2)
  6. If (Request.Form("SearchValue1") <> "") Then 
  7.   rs__SearchValue1 = Request.Form("SearchValue1")
  8.  
  9. '  If the FORM field is empty, then look in the URL query string for the old FORM field value 
  10. Elseif (Request.QueryString("SearchValue1") <> "") Then 
  11.   rs__SearchValue1 = Request.QueryString("SearchValue1")
  12. End If
  13. %>
  14.  
I will also email Dreamweaver to tell them to update their standard record paging behavior code.

Thanks to all who replied with help.

Conor
Jan 16 '08 #2

jhardman
Expert 2.5K+
P: 3,405
I'm glad you got it fixed. Thanks for posting your solution.

Jared
Jan 16 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.