Why ?in Access it works well,in Asp get error

[zhongwei]

Hi,everyone
In ASP I'm trying to run a Query in Access, here is the code:
<%set conn = server.createobject(&quot;adodb.connection&quot
conn.open &quot;WEBORDER&quot;
set cmd=server.createobject(&quot;adodb.command&quot
set cmd.Activeconnection=conn
cmd.Commandtext=&quot;Qlist1&quot;
set rs=cmd.execute()
.......%>
I got error message like this:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

if I run this Query in access,it works well, in Access the QList1 is:
SELECT QLastIN.JobNum, QLastIN.Lastdate, Max(FeedBack.SendDate) AS MaxOfSendDate
FROM FeedBack RIGHT JOIN QLastIN ON FeedBack.Jobnum = QLastIN.JobNum
GROUP BY QLastIN.JobNum, QLastIN.Lastdate;

To find out the problem,I replace Qlist1 by another Query(QlastIN),Asp works well. I think the problem cause by Qlist1,but in Access it works!
Any thoughts about it?

 

[link9]

Don't think you want the command object here. Sounds to me like Qlist1 is a stored query in your Access database. If so, then try this:

dim sql, rs, con
set con = server.createObject(&quot;ADODB.Connection&quot
set rs = server.createObject(&quot;ADODB.Recordset&quot
sql = &quot;SELECT * FROM Qlist1&quot;
con.open &quot;DSN=WEBORDER&quot;
rs.open sql,con

should fix you right up.


paul

 

[zhongwei]

Hi,paul
thanks for you suggestion,it fixed the problem,but i'm still confused why command object works well with another Query,but for this query it fails? thanks a lot

 

[link9]

Looks like you may have forgotten to supply the needed .commandType property of the command object, which in this case would be adCmdText.


paul