Paging and MySQL Help

Gannuscio · December 21, 2007

Here is my problem.

I currently have a search engine that points to my DB and I want to be able to use paging incase a large amount of records are found. Everything works fine... I get my results in the right order..

but only on the first page.

When hit nextpage, or previous page,

I get the following error

Microsoft OLE DB Provider for ODBC Drivers error '80040e14' 

[MySQL][ODBC 3.51 Driver][mysqld-5.0.45-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY proddb.code ORDER BY AVG(rateit.stars) DESC' at line 1 

/search.asp, line 176

I can't figure out if it my my SQL STATEMENT or if it is the way I am paging the records.

Here is the rest of my code. Please help!

Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3
Const adOpenStatic = 3
Const adCmdText = 1


' For Loop To Move Through Search String, Counting Spaces, and
' Incrementing WordCounter By One At Each Occuranceh

For i = 1 to len(SValue)
   If mid(SValue, i, 1) = " " Then 
     WordCounter = WordCounter + 1
   End If
Next

' Now, We Add One To Include The First Word

WordCounter = WordCounter + 1

' Next We Dim Word As An Array, With The Maximum Number Of Words
' To Allow - In This Case, 100

Dim Word(100)

' Now, We Fill The Array With The Words

CurrentWord = 1
For i = 1 to len(SValue)
     If mid(SValue, i, 1) = " " Then
CurrentWord = CurrentWord + 1
     Else
Word(CurrentWord) = Word(CurrentWord) + mid(SValue, i, 1)
     End If
Next

' Now Lets Build The SQL Statement Based On What Search Type (SType)
' Was Selected
Dim rsRecords
Set rsRecords = Server.CreateObject("ADODB.Recordset")
rsRecords.CursorLocation = 3
rsRecords.PageSize = 3
rsRecords.CacheSize = 50

' First Part Of SQL

SQL = " SELECT proddb.code, proddb.ProductName, proddb.ProductUrl, proddb.Description, proddb.ImageURL,  proddb.Category, AVG(rateit.stars) as rateTotal from gannuscio.proddb LEFT JOIN gannuscio.rateit ON proddb.code = rateit.pid WHERE "

' For Loop To Concatenate SQL String Together

For i = 1 to WordCounter
	If SType = "AllWords" Then
		If i <> WordCounter Then
			SQL = SQL & strFieldName & " LIKE '%" & Word(i) & "%' AND "
		ElseIf i = WordCounter Then
			SQL = SQL & strFieldName & " LIKE '%" & Word(i) & "%'"
		End If
	ElseIf SType = "AnyWord" Then
		If i <> WordCounter Then
			SQL = SQL & strFieldName & " LIKE '%" & Word(i) & "%' OR "
		ElseIf i = WordCounter Then
			SQL = SQL & strFieldName & " LIKE '%" & Word(i) & "%'"
		End If
	End If
Next

' Finishing Part Of SQL Statement.

      
SQL = SQL & " GROUP BY proddb.code "
SQL = SQL & " ORDER BY AVG(rateit.stars) DESC " 


rsRecords.Open SQL, oConn, adOpenForwardOnly, adLockReadOnly

		If Len(Request("pagenum")) = 0  Then
            	rsRecords.AbsolutePage = 1
				Else
            If CInt(Request("pagenum")) <= rsRecords.PageCount Then
                rsRecords.AbsolutePage = Request("pagenum")
        			Else
                rsRecords.AbsolutePage = 1
            End If
            End If
                                               
                                               Dim abspage, pagecnt
                                               abspage = rsRecords.AbsolutePage
                                               pagecnt = rsRecords.PageCount
Dim fldF, intRec

if not rsRecords.eof then
r = rsRecords.RecordCount
    	response.write("<div style=""border:#ff66cc 1px dashed;""><font size='2'>Your Search Returned: " & r & " Products<br></font></div><br>") 
Else 
 response.Write("<div style=""border:#ff66cc 1px dashed;""><font size='2'>Sorry we could not find any products to match your search!</font></div><br>")

End If
%>

</h2>
<% If Not rsRecords.EOF And Not rsRecords.BOF Then %>
        <table width="582" height="218" border="0">

	<% 
	Linkcount = 0
	Do While NOT rsRecords.EOF AND LinkCount < rsRecords.PageSize %>
          
          <tr>
            <td width="198" height="76" rowspan="3" align="left"><p align="center">
            <% Response.Write ("<a href='product_detail.asp?prod_description=" & rsRecords(Server.URLEncode("Code")) & "'><img src='" & rsRecords("ImageURL") & "'height='100px' border='0'>") %></p>            </td>
            <th width="374" height="20" align="right" valign="bottom"><div align="center"><% Response.Write "<font color='#000000' size='3'>" & rsRecords("ProductName") & "" %></div></th>
          </tr>
          <tr>
            <th height="29" align="center" valign="bottom"><div align="center"></div></th>
          </tr>
          <tr>
            <th width="374" height="29" align="center" valign="top"><div align="center">
              <% 
		If rsRecords("rateTotal") => 1 Then
                      response.write("<font size='1'>Average Rating: </font><font size='1'>"& Round(rsRecords("rateTotal")) &" out of 5</font><br>") 
														   						  							        
         		      Else
                      response.write ("<b><a class='searchfont'>Not Yet Rated</a></b></br><a class='productfont'><br>" & vbnewline)
                      End If
				  If rsRecords("rateTotal") > 4.5 Then
		Response.Write ("<img src='images/5star.gif' border='0' align='middle'> ")
		Else	
	If rsRecords("rateTotal")  => 3.6 AND rsRecords("rateTotal") <= 4.5 Then
		Response.Write ("<img src='images/4star.gif' border='0' align='middle'> ")
		Else
	If rsRecords("rateTotal")  => 2.6 AND rsRecords("rateTotal") <= 3.5 Then
		Response.Write ("<img src='images/3star.gif' border='0' align='middle'> ")
		Else
	If rsRecords("rateTotal")  => 1.6 AND rsRecords("rateTotal") <= 2.5 Then
		Response.Write ("<img src='images/2star.gif' border='0' align='middle'> ")
		Else
	If rsRecords("rateTotal")  => .5 AND rsRecords("rateTotal") <= 1 Then
		Response.Write ("<img src='images/1star.gif' border='0' align='middle'> ")
		Else
	If rsRecords("rateTotal") < 1 Then
		Response.Write("")
			End If		
	  			End If
			End If
		End If
		End If
	End IF	  %>
            </div></th>
          </tr>
          <tr>
            <td width="198" height="22" align="left"><div align="center">
              <% Response.Write "<center><a href='" & rsRecords("ProductURL") & "' target='_blank'><b><center><font color='#CC3300'></font></center></b><br><font color='#0000FF'><center><img src='images/buynow.gif' border='0' align='center'></center></font></a>" %>
            </div></td>
            <th height="22" align="right" valign="top"><div align="center">
              <% Response.Write "<a class='rate' href='product_detail.asp?prod_description=" & rsRecords(Server.URLEncode("Code")) & "'><img src='images/bluebtn.gif' border='0'></a> " %>
            </div></th>
          </tr>
          <tr>
            <td height="1" colspan="2" align="left"><div align="center"><%
Response.Write "<a class='descriptionfont2'><font color='#000000'>" & rsRecords("Description") & ""
%></div></td>
          </tr>
          <tr>
            <td height="9" colspan="2" align="left" bordercolor="#000000" bgcolor="#CCCCCC"><div align="center"></div></td>
          </tr>
	  <%
	  LinkCount = LinkCount + 1
	  rsRecords.MoveNext %>
	  <%	
Loop
%>
    <%

End If

%>
        </table>

        <table width="200" border="1">
          <tr>
            <td><% Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=1"">First Page</a>"
                                                               Response.Write " | "
                                                               
                                                               If abspage = 1 Then
                                                               Response.Write "<span style=""color:silver;"">Previous Page</span>"
                                                               Else
                                                               Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & abspage - 1 & """>Previous Page</a>"
                                                               End If
                                                               
                                                               Response.Write " | "
                                                               
                                                               If abspage < pagecnt Then
                                                               Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & abspage + 1 & """>Next Page</a>"
                                                               Else
                                                               Response.Write "<span style=""color:silver;"">Next Page</span>"
                                                               End If
                                                               Response.Write " | "
                                                               Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & pagecnt & """>Last Page</a>"
                                                              
                                               
                                               
rsRecords.close 
set rsRecords = nothing 
oConn.close
set oConn = nothing 
%> </td>
          </tr>
        </table>
        <p> </p>
        <p align="center">
      
        </p>
      </div>

      <div class="thirds">
        <div class="smallboxtop"></div>

        <div class="smallbox">
          <script type="text/javascript">
//<![CDATA[
<!--
google_ad_client = "pub-3818016923432290";
google_ad_width = 468;
google_ad_height = 60;
google_ad_format = "468x60_as";
google_ad_type = "text";
google_ad_channel = "";
google_color_border = "FFFFFF";
google_color_bg = "FFFFFF";
google_color_link = "0000FF";
google_color_text = "000000";
google_color_url = "9D1961";
//-->
//]]>
</script> <script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript">
</script>

fenway · December 21, 2007

Please echo the plaintext sql query that causes this error.

Gannuscio · December 22, 2007

Here is my SQL Statement

SQL = " SELECT proddb.code, proddb.ProductName, proddb.ProductUrl, proddb.Description, proddb.ImageURL,  proddb.Category, AVG(rateit.stars) as rateTotal from gannuscio.proddb LEFT JOIN gannuscio.rateit ON proddb.code = rateit.pid WHERE "

' For Loop To Concatenate SQL String Together

For i = 1 to WordCounter
	If SType = "AllWords" Then
		If i <> WordCounter Then
			SQL = SQL & strFieldName & " LIKE '%" & Word(i) & "%' AND "
		ElseIf i = WordCounter Then
			SQL = SQL & strFieldName & " LIKE '%" & Word(i) & "%'"
		End If
	ElseIf SType = "AnyWord" Then
		If i <> WordCounter Then
			SQL = SQL & strFieldName & " LIKE '%" & Word(i) & "%' OR "
		ElseIf i = WordCounter Then
			SQL = SQL & strFieldName & " LIKE '%" & Word(i) & "%'"
		End If
	End If
Next

' Finishing Part Of SQL Statement.

      
SQL = SQL & " GROUP BY proddb.code "
SQL = SQL & " ORDER BY AVG(rateit.stars) DESC " 


rsRecords.Open SQL, oConn, adOpenForwardOnly, adLockReadOnly

fenway · December 23, 2007

That's code... that's not the contents of the "SQL" variable before the .open call.

Sign In

Paging and MySQL Help

Recommended Posts

Gannuscio

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Gannuscio

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information