ORDER BY error?

[danieljoo]

Can you tell me what is wrong with my ORDER BY statement below. I get the following error "Missing semicolon ( at end of SQL statement". Thanks.

MyVar1 = Request.Querystring("CustomerNumber&quot
MyVar2 = Request.Querystring("AttendeeFirstName&quot
MyVar3 = Request.QueryString("AttendeeLastName&quot
MyVar4 = Request.QueryString("Orderby&quot
strsql = "select * from Customer WHERE CUSTOMERNUMBER='" & myVar1 & "'"
set rs = Server.CreateObject("ADODB.Recordset&quot
If MyVar4 ="" then
sql = "update Attendee set Printed=TRUE WHERE CustomerNumber='" & myVar1 & "' and AttendeeFirstName='" & myVar2 & "' and AttendeeLastName='" & myVar3 & "'"
else
sql = "update Attendee set Printed=TRUE WHERE CustomerNumber='" & myVar1 & "' and AttendeeFirstName='" & myVar2 & "' and AttendeeLastName='" & myVar3 & "'"
sql = sql & " ORDER BY '" & MyVar4 & "'"
end if
conn.Execute (sql)

 

[Falcon99]

Try this:

sql = sql & " ORDER BY '" & MyVar4 & "';"

 

[danieljoo]

Tried that, but didn't work.

 

[mwolf00]

[ol]
[li]Why are you using an order by on an update?

[li]You shouldn't put single quotes around the column name in the order by clause. It should just be sql = sql & " ORDER BY " & MyVar4

[li]Where do you execute "strSQL"? You have 2 different query strings - what does the first string do?
[/ol]
-----------------------------------------------------------------
"Whether you think that you can, or that you can't, you are usually right."
- Henry Ford (1863-1947)

mikewolf@tst-us.com

 

[danieljoo]

I am using an Order by with the update becuase the page that gets updated has fields that can be ordered by. I don't want the order to change when the page gets updated. strsql is used to display the information for this page, while sql is used to update another page.

#2 didn't work either.

 

[Falcon99]

Just a thought as a last resort;

Put in a:

Response.Write sql
Response.end

Right before the execute statement. Save and Refresh the page. Then take the SQL code back to the database and manually run the query.

 

[mwolf00]

"the page that gets updated has fields that can be ordered by" - You are updating a database, right? Then using the new data to create another page that uses a select statement. I think that the order by clause only works on a SELECT statement... -----------------------------------------------------------------
"Whether you think that you can, or that you can't, you are usually right."
- Henry Ford (1863-1947)

mikewolf@tst-us.com

 

[danieljoo]

still no dice. Here is the output of the response.write (without the single quotes):

update Attendee set Printed=TRUE WHERE CustomerNumber='2' and AttendeeFirstName='Ellen' and AttendeeLastName='Mark' ORDER BY AttendeeCity

 

[onpnt]

As far as I can remember from a million years ago in a Advanced Structure Query Language class the ORDER BY method is not valid in the UPDATE statement. ---------------------------------------
{ str = "sleep is good for you. sleep gives you the energy you need to function";
ptr = /sleep/gi;Nstr = str.replace(ptr,"coffee"alert(Nstr); }

http://www.onpntwebdesigns.com

 

[onpnt]

in fact after reminding myself I believe that is correct

http://www.devguru.com/Technologies/sqlsyntax/quickref/sql_syntax_update.html

---------------------------------------
{ str = "sleep is good for you. sleep gives you the energy you need to function";
ptr = /sleep/gi;Nstr = str.replace(ptr,"coffee"alert(Nstr); }

http://www.onpntwebdesigns.com

 

[danieljoo]

How about another solution?

 

[mwolf00]

What do you need another solution to? Are you trying to order your output on this page? Whereever the page, you want to make sure that you use your order by clause on the select statement for that page. I'm not exactly sure what you are trying to do.... -----------------------------------------------------------------
"Whether you think that you can, or that you can't, you are usually right."
- Henry Ford (1863-1947)

mikewolf@tst-us.com

 

[Zequel]

..am using an Order by with the update becuase the page that gets updated has fields that can be ordered by. I don't want the order to change when the page gets updated.

WHAT?
I recommend any good book on SQL before you go any further. Stored orders of records in a database are not influenced by any sql statement except one that creates a clustered index such as a primary key. The basic question is why do you care how they ordered in a database. When you want order, specify an ORDER in a select statement.