Data Order

I am new at ASP and trying to order the data i retive from the data base by veriable that represents any of the collum names in my Access data base.

I have opened my connection and named it objConn, then i have created a record set and assigned it to Projects.

I have also assigned a value to my variable calld Order - the string that this variable holds is one of the collum names in the data base.

I am attempting to use the following code to open my connection with the results orderd by the Order variable.


Projects.Open "SELECT * FROM Projects ORDER BY '" & Order & "' ", objConn


I don't get any errors when the page runs but it also do not order my data the way i asked it to.

I have checked the Order variable by printing it to the screen and it does in fact hold a string that matches the collum names.

I have also checked the Select statment without the variable by just typing in all of the posibilities that can be assigned to the Order variable and it works just fine.

So the problem must be when i add in the variable.

Thanks in advance for helping me out.

looking at your statement you are wrapping the Order value in single quotes. Remove the single quote. YOu might also wish to add asc or desc to the order by clause.

Also run the complete SQL statement with the order value in the SQL Environment, to ensure the Syntax for the whole string is correct not just by looking at them seperately.

It's also possible that order is a sql "reserved word". Try putting [ ] around the column name, or rename the column.

Why don't you try setting up your db call in a record set like this,


set rsProject = server.createObject("ADODB.Recordset")
sqlStatement = "SELECT * FROM Projects ORDER BY " & Order
rsProject.Open sqlStatement, objConn, 0, 1


if not rsProject .eof and not rsProject.bof then
rsProject .movefirst

variableName = rsProject .fields("databaseFieldName")
variableName = rsProject .fields("databaseFieldName")
variableName = rsProject .fields("databaseFieldName")
variableName = rsProject .fields("databaseFieldName")

end if

rsProject .Close
set rsProject = nothing


The 0, 1 is assuming that you only want to read from this list and not write otherwise it would be 1, 3. That should work for you. And where ever you want your data to show just call the correct variable name and it should show. Here is a quick example

<table>
<tr>
<td><%= variableName1%>
</tr>
</table>

Let me know if this helps