DataAssist Sort

The Sort feature works fine when the recordset is setup with no filters. As soon as I add a WHERE statement, I get the following error when clicking the link to sort:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near the keyword 'ORDER'.
It points to line 117 - "Set rsDisplayData = rsDisplayData_cmd.Execute"
How can I implement a WHERE filter and still allow the sorting to function?

is the name of the db column you are sorting with "order"? if so this is a reserved word in SQL and should not be used as a column name. see the following for a full list of reserved words in MS SQL:
aa238507%28SQL.80%29.aspx


if not, please post a copy of your page that is generating the error s we can investigate the code.

I've attached the source code. Thanks.

to trouble shoot this error, change line 97:

Set rsDisplayData = rsDisplayData_cmd.Execute

to:

On Error Resume Next
Response.Write(rsDisplayData_cmd.CommandText)
Set rsDisplayData = rsDisplayData_cmd.Execute

this will write the sql generating the error to the screen, please post back the SQL

SELECT * FROM dbo.tblGISDataRequest WHERE ((Status <> 'Completed') OR (Status IS NULL));

have you triggered the sorting? I dont see how this SQL could generate the order you report?

I don't either :| I do not have any triggers in place, or views that I'm using. The SQL is exactly all that I'm using...straight lookup to a table that is limited to the WHERE statement (No ORDER BY). As soon as I remove the WHERE statement, the sorting works fine.

Remember the old saying...It's ALL about the DETAILS...I removed the semi-colon from the end of the SQL statement and it works now!
SELECT * FROM dbo.tblGISDataRequest WHERE ((Status <> 'Completed') OR (Status IS NULL));
to
SELECT * FROM dbo.tblGISDataRequest WHERE ((Status <> 'Completed') OR (Status IS NULL))

OK, glad to hear it is working.