|
Subject:
|
ASP/MySQL Order by problem
|
|
Posted By:
|
lubany
|
Post Date:
|
8/14/2006 9:25:03 AM
|
Hello, I'm new to ASP/MySQL field so I have trouble with a simple 'order by' clause. Here is my code:
I want to sort recorset according to what was selected in this drop down:
----------------------
<%session("sorting") = Request.form("Sorting")
session("ID") = request.querystring("ID")%>
<form name="frmSort" action="Boutique_Products_Cat.asp" method="POST">
<div align="left">
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="100%"><select size="1" name="Sorting" class="input-combo-field" onChange="document.frmSort.submit();" >
<option>--- Sort by</option>
<option value="ProdPrice">Price</option>
<option value="ProdType">Type</option>
<option value="CreateDate">Recent</option>
</select></td>
</tr></table></div></form>
----------------------
Here is my query:
<%
dim rs, sql
set rs=server.createobject("ADODB.recordset")
if session("sorting") <> "" then
sql = "select * from ProductDetails where CatID= " & session("ID") & " order by '" & session("sorting") & "'"
else
sql = "select * from ProductDetails where CatID= " & session("ID") & " order by CreateDate desc"
end if
set rs = conn.execute(sql) %>
----------------
I tried different options but every time it gives "syntax error, check your MySQL version for correct syntax", although it reads session variable correctly and sorts right when page first loads. I'm using MySQL 4.0.26 version. What am I doing wrong??
Thanks in advance
|
|
Reply By:
|
dparsons
|
Reply Date:
|
8/14/2006 3:42:10 PM
|
When your Session("sorting") != "" what does your sql statement equate to?
The reason the pages sorts correctly on the first page load is because you have your order by clause hard coded, e.g., your are ordering on CreateDate desc, my guess would be that either A) your session value to sort on contains a special characert (@, ', or , etc.) or B) the value of sorting is not contained in the SELECT list.
Post what your sql statements looks like once the session values have been populated.
"The one language all programmers understand is profanity."
|
|
Reply By:
|
Imar
|
Reply Date:
|
8/14/2006 3:55:28 PM
|
quote:
B) the value of sorting is not contained in the SELECT list.
This is not required; you can easily sort on a column that you don't select.
The problem is with the apostrophes in the ORDER BY clause. Hard to spot because of the font, but look at this:
order by '" & session("sorting") & "'"
This results in something like:
order by 'ProdType'
which is incorrect SQL. Drop the apostrophes and things will work:
sql = "select * from ProductDetails where CatID= " & session("ID") & " order by " & session("sorting")
Cheers,
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.
|
|
Reply By:
|
lubany
|
Reply Date:
|
8/14/2006 4:47:50 PM
|
Imar,
I tried this: sql = "select * from ProductDetails where CatID= " & session("ID") & " order by " & session("sorting") - it doesn't work either. Gives exactly the same error...
|
|
Reply By:
|
Imar
|
Reply Date:
|
8/15/2006 12:59:21 AM
|
Have you tried dparsons' tip? What do you get when you write out the SQL statement?
Response.Write("Sql is " & sql)
' set rs = conn.execute(sql)
%>
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.
|
|
Reply By:
|
lubany
|
Reply Date:
|
8/15/2006 8:15:07 AM
|
Problem solved, I'm ashamed to admit it but I never checked session("ID") value. Since it's getting value from querystring, it turns to null on form reload. I assigned it to session("ID1") and everything works fine now. Thank you for your prompt help!
|
|
Reply By:
|
dparsons
|
Reply Date:
|
8/15/2006 12:52:53 PM
|
Glad you worked everything out.
"The one language all programmers understand is profanity."
|
