www.webdeveloper.com
Results 1 to 4 of 4

Thread: ASP / MySQL Paging not working...

  1. #1
    Join Date
    Jun 2007
    Posts
    73

    Post ASP / MySQL Paging not working...

    Hi,

    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
    %>

  2. #2
    Join Date
    Jan 2008
    Location
    Florida
    Posts
    1,227
    Where is your querystring "sql" value coming from?

    Chances are, if you are passing the entire query via a querystring, it is being URL encoded.

  3. #3
    Join Date
    Jun 2007
    Posts
    73
    Hi,

    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

  4. #4
    Join Date
    Jan 2008
    Location
    Florida
    Posts
    1,227
    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?

Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles