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 1 users browsing this thread. (0 members and 1 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

"

"

X vBulletin 4.2.2 Debug Information

  • Page Generation 0.10587 seconds
  • Memory Usage 2,880KB
  • Queries Executed 15 (?)
More Information
Template Usage (33):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)bbcode_code
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (4)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (1)navbar_moderation
  • (1)navbar_noticebit
  • (1)navbar_tabs
  • (2)option
  • (4)postbit
  • (4)postbit_onlinestatus
  • (4)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (26):
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/functions_navigation.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_facebook.php
  • ./includes/functions_calendar.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (73):
  • init_startup
  • friendlyurl_resolve_class
  • init_startup_session_setup_start
  • database_pre_fetch_array
  • database_post_fetch_array
  • init_startup_session_setup_complete
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • load_show_variables
  • load_forum_show_variables
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • strip_bbcode
  • friendlyurl_clean_fragment
  • friendlyurl_geturl
  • forumjump
  • cache_templates
  • cache_templates_process
  • template_register_var
  • template_render_output
  • fetch_template_start
  • fetch_template_complete
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • build_navigation_data
  • build_navigation_array
  • check_navigation_permission
  • process_navigation_links_start
  • process_navigation_links_complete
  • set_navigation_menu_element
  • build_navigation_menudata
  • build_navigation_listdata
  • build_navigation_list
  • set_navigation_tab_main
  • set_navigation_tab_fallback
  • navigation_tab_complete
  • fb_like_button
  • showthread_complete
  • page_templates