I am trying to have the results from the following database call paged as seen on a google results page etc. However, when following a tutorial on paging the following error is given:
ADODB.Recordset error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/testing/results.asp, line 13
I have tried tweaking the code but I am at a loss. Can anyone make a suggestion what I am doing wrong please?
Code:
Code:
<%
response.expires = -1
page_size = 10
page_current = CInt(request.querystring("page"))
set db = server.createobject("adodb.connection")
connection = "driver={MySql ODBC 3.51 Driver};server=mysql11.streamline.net;uid=vouzamoco;pwd=V0uzam0;database=vouzamoco"
db.open (connection)
set rs = server.createobject("adodb.recordset")
rs.ActiveConnection = Con
rs.cursortype = adOpenStatic
rs.cursorLocation = aduseClient
rs.PageSize = 6
sql = request.querystring("sql")
set rs = db.execute(sql)
page_count = rs.pagecount
if page_current > page_count then
page_current = page_count
end if
if page_current < 1 then
page_current = 1
end if
if page_count = 0 then
%>
<tr>
<td width="670" height="100%" style="padding: 5px 5px 5px 5px;">
No tickets matched your filter.
</td>
</tr>
<%
else
rs.absolutepage = page_current
records_shown = 0
do while records_shown < page_size and not rs.eof
for i = 0 to rs.fields.count - 1
rs_id = rs("id")
rs_category = rs("category")
rs_title = rs("title")
rs_type = rs("type")
rs_priority = rs("priority")
rs_status = rs("status")
rs_description = rs("description")
rs_creator = rs("creator")
rs_assignee = rs("assignee")
rs_open = rs("open")
rs_closed = rs("closed")
set db2 = server.createobject("adodb.connection")
connection = "driver={MySql ODBC 3.51 Driver};server=mysql11.streamline.net;uid=vouzamoco;pwd=V0uzam0;database=vouzamoco"
db2.open (connection)
set rs2 = server.createobject("adodb.recordset")
sql2 = "SELECT * FROM nhd_users WHERE id='" & rs_creator & "'"
set rs2 = db2.execute(sql2)
if not rs2.EOF then
while not rs2.EOF
rs_creator_name = rs2("first_name") & " " & rs2("last_name")
rs2.movenext()
wend
end if
rs2.close
set rs2 = nothing
db2.close
set db2 = nothing
if rs_assignee > 0 then
set db2 = server.createobject("adodb.connection")
connection = "driver={MySql ODBC 3.51 Driver};server=mysql11.streamline.net;uid=vouzamoco;pwd=V0uzam0;database=vouzamoco"
db2.open (connection)
set rs2 = server.createobject("adodb.recordset")
sql2 = "SELECT * FROM nhd_users WHERE id='" & rs_assignee & "'"
set rs2 = db2.execute(sql2)
if not rs2.EOF then
while not rs2.EOF
rs_assignee_name = rs2("first_name") & " " & rs2("last_name")
rs2.movenext()
wend
end if
rs2.close
set rs2 = nothing
db2.close
set db2 = nothing
else
rs_assignee_name = "Unassigned"
end if
set db2 = server.createobject("adodb.connection")
connection = "driver={MySql ODBC 3.51 Driver};server=mysql11.streamline.net;uid=vouzamoco;pwd=V0uzam0;database=vouzamoco"
db2.open (connection)
set rs2 = server.createobject("adodb.recordset")
sql2 = "SELECT * FROM nhd_sla WHERE id='" & rs_type & "'"
set rs2 = db2.execute(sql2)
if not rs2.EOF then
while not rs2.EOF
rs_sla_response = rs2("response")
rs_sla_resolution = rs2("resolution")
rs2.movenext()
wend
end if
rs2.close
set rs2 = nothing
db2.close
set db2 = nothing
if rs_status = "Closed" then
background = "e0b0a6"
else
if datediff("h",rs_open,NOW()) > rs_sla_resolution then
background = "e0d7a6"
else
background = "b2e0a6"
end if
end if
%>
<tr style="background-color: #<%= background %>;">
<td width="670" height="100%" style="padding: 5px 5px 5px 5px; border-bottom: 1px dashed #5d5751; cursor: pointer;" onClick="ajax_result('<%= rs_id %>');">
<table width="660" cellpadding="0" cellspacing="0" valign="top">
<tr>
<td width="460" height="14" align="left">
<span style="font-size: 14;"><b><%= rs_id %></b> - (<%= rs_category %>) <%= rs_title %></span>
</td>
<td width="200" height="14" align="right">
<span style="font-size: 14;"><%= rs_assignee_name %></span>
</td>
</tr>
<tr>
<td width="660" height="100%" style="padding: 5px 5px 5px 5px;" colspan="2">
<table width="650" cellpadding="0" cellspacing="0">
<tr>
<td width="640" height="100%" style="background-image: url('opacity.png'); padding: 5px 5px 5px 5px;" align="left">
<span style="font-size: 12px;"><%= left(rs_description,1000) %></span>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td width="660" height="10" colspan="2" align="right">
<span style="font-size: 10px;">created by <%= rs_creator_name %> at <%= rs_open %> (<%= datediff("h",rs_open,NOW()) %> hours ago)</span>
</td>
</tr>
<%
if rs_status = "Closed" then
%>
<tr>
<td width="660" height="10" colspan="2" align="right">
<span style="font-size: 10px;">closed by <%= rs_assignee_name %> at <%= rs_closed %> (<%= datediff("h",rs_closed,NOW()) %> hours ago)</span>
</td>
</tr>
<%
end if
%>
</table>
</td>
</tr>
<%
records_shown = records_shown + 1
rs.movenext()
next
loop
end if
if page_count > 0 then
%>
<tr>
<td width="670" height="12" style="background-image: url('670x12.png'); padding: 2px 2px 2px 2px;" align="center">
<span style="font-size: 12px; color: #ffffff;">Showing <%= ((10 * page_current) - 9) %> to <%= (((10 * page_current) - 9) + records_shown) %> of <%= rs.fields.count %></span>
</td>
</tr>
<%
end if
rs.close
set rs = nothing
db.close
set db = nothing
%>
The SQL is passed entirely in a querystring but works successfully when I don't try to use paging so I suspect it is an issue limited to the paging code. Please see live site (without paging implemented) at http://www.vouzamo.co.uk/testing/index.asp
Your querystring must be getting passed using AJAX as I don't see a visible one. Your multiple nested queries are not very optimal, can you post your relevant code?
ASP / MySQL Paging not working...
Reply With Quote
Bookmarks