ASP - SQL db survey error

[Tracey27]

Help please,

I'm putting together an online survey using ASP and I keep getting this error message. My code seems fine, but I'm new to ASP. I've pasted the error and the code below it. Please take a look at it and let me know if you have any ideas. I created the DSN without any problems and can connect to the db...?

Also, does anyone know any sites where I can find tips or code for ASP - SQL db surveys?

Your help is badly needed and greatly appreciated.
Tracey 27



My error:

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

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Order'.

/demo_default.asp, line 26

My Code:

<% @EnableSessionState=False %>
<% Response.Buffer = true %>

<%
dim ticket
dim first_name
dim last_name
dim description
dim date_opened
dim date_closed
dim serviced_by
dim survey
dim clientname

survey = Request.QueryString (&quot;survey_id&quot

Set Connection = Server.CreateObject(&quot;ADODB.Connection&quot
Connection.Open &quot;DSN=Survey; UID=surveyuser; PWD=survey&quot;
Connection.CommandTimeout = 60


SQLStmt = &quot;Select surveyid,ticket#,client_id,client_name,convert(varchar(255),description)as description,date_opened,date_closed,serviced_by From Demo_Survey_tickets Where surveyid = &quot; & survey & &quot; Order By date_closed&quot;
SQLStmt1 = &quot;Select a.Question_ID,a.Question From Survey_Questions a Order By a.Question_id&quot;

set RS = Connection.Execute (SQLStmt)
set RS1 = Connection.Execute (SQLStmt1) (this is LINE 27??)

questionid = RS1(&quot;question_id&quot
clientname = rs(&quot;client_name&quot

 

[foxbox]

when your SQLStmt is on one line (in your editor) and
SQLStmt1 on the next, i count the set RS1 line as 26.

The SQL looks good to me, but try to it with this line:

SQLStmt1 = &quot;Select Question_ID, Question From &quot; &_
&quot;Survey_Questions Order By Question_id&quot;


Maybe i'm squint, and SQLStmt is line 26. Are you sure you call this page *always* with a valid survey_id?
I'm a defensive programmer, so i would add this line:
if survey = &quot;&quot; then response.redirect &quot;startpage.asp&quot;

(or whatever) [right after survey = request.querystring(&quot;survey&quot]






br
Gerard

 

[jonscott8]

I agree with FoxBox.

SQLStmt1 looks fine. The problem is probably, as FoxBox eluded to, survey is an empty string. Resulting in your SQL where clause executing as:

Where surveyid =  Order By date_closed

which would generate the error you are describing. Jon Hawkins

 

[JrClown]

What I usually do is print my SQL statement to the screen then to see if I spot errors and if I don't, I copy the printed statement and run it in Access as a query.
&quot;The reward of one duty done is the power to fulfill another&quot;
<%
Jr Clown
%>