SQL Problems

[Mike James]

I'm pretty new to ASP-db and currently I'm only trying to use it to display a table on screen. For the simple queries there doesn't seem to be a problem, I'm just defining the SQL statement to variable and then passing that variable to ASPdb. The problem I'm having is when I do this with more complex queries, I get the following:
Error #=0, Description=[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed, Source=Microsoft OLE DB Provider for ODBC Drivers, WQL State=IM006, NativeError=0.

Here's the SQL Variable that I'm trying to pass ASPdb:
SQLStmt = "SELECT * FROM equiphst WHERE (equiphst.fac_nbr = '" & Session("fac_nbr&#34 & "&#39 AND (EQUIP_DEPT = '" & Request.Form("lstDept&#34 & "&#39 AND (LH_STATUS='PD' Or LH_STATUS='PRP' Or LH_STATUS='CWP&#39"

It's this last AND statement that seems to be causing the problem. Is there a better way of doing this? Also, if I remove the last AND statement from the query, I do get the expected results from the database, but if I try and sort them by clicking on a Column Header I get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14', [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE','INSERT','PROCEDURE&# 39;,'SELECT', or 'UPDATE'.

Any suggestions to either of my problems would be greatly appreciated. Just let me know if you need additional information. Thanks.

[Frank]

Mike,
Your query is not even close to being a "complex query". Complex query we encountered breaks the ASP script into pieces and yet ASPdb took care of them. Look for them at the aspdb site. But as for your query I made up an equivalent and do not see any problem. You can modify the attached code to show the problem. Using the NWIND as a common DB, the debug is easier.

<%
Set X=Server.CreateObject(&#34;AspDB.EP&#34
X.dbQuickProps=&#34;1;NWIND;;Grid;4,auto;;;ACCESS; 5&#34;
X.dbSQL=&#34;SELECT * FROM employees WHERE (title = &#39;Sales Representative&#39 AND (EmployeeID > 2) AND (TitleOfCourtesy=&#39;Ms.&#39; Or TitleOfCourtesy=&#39;Mr.&#39; Or TitleOfCourtesy=&#39;Mrs.&#39&#34;
X.aspdbEP
%>

You can use either ASPDB.EP or ASPDB.Pro... no difference !!


FK




------------
Mike James at 11/23/99 9:47:42 AM

I&#39;m pretty new to ASP-db and currently I&#39;m only trying to use it to display a table on screen. For the simple queries there doesn&#39;t seem to be a problem, I&#39;m just defining the SQL statement to variable and then passing that variable to ASPdb. The problem I&#39;m having is when I do this with more complex queries, I get the following:
Error #=0, Description=[Microsoft][ODBC Driver Manager] Driver&#39;s SQLSetConnectAttr failed, Source=Microsoft OLE DB Provider for ODBC Drivers, WQL State=IM006, NativeError=0.

Here&#39;s the SQL Variable that I&#39;m trying to pass ASPdb:
SQLStmt = &#34;SELECT * FROM equiphst WHERE (equiphst.fac_nbr = &#39;&#34; & Session(&#34;fac_nbr&#34 & &#34;&#39 AND (EQUIP_DEPT = &#39;&#34; & Request.Form(&#34;lstDept&#34 & &#34;&#39 AND (LH_STATUS=&#39;PD&#39; Or LH_STATUS=&#39;PRP&#39; Or LH_STATUS=&#39;CWP&#39&#34;

It&#39;s this last AND statement that seems to be causing the problem. Is there a better way of doing this? Also, if I remove the last AND statement from the query, I do get the expected results from the database, but if I try and sort them by clicking on a Column Header I get the following error:
Microsoft OLE DB Provider for ODBC Drivers error &#39;80040e14&#39;, [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected &#39;DELETE&#39;,&#39;INSERT&#39;,&#39;PROCEDURE&# 39;,&#39;SELECT&#39;, or &#39;UPDATE&#39;.

Any suggestions to either of my problems would be greatly appreciated. Just let me know if you need additional information. Thanks.

[Donna]

Mike,
Your problem might be with Request.Form not returning what you think it is
returning. I would suggest you print out (response.write) your SQLStmt
to see what it contains. You might also set mydb.dbDebug=True. That might
show you what SQL is trying to execute when it fails. I&#39;ve been using ASP-db
for quite a while, and it&#39;s usually some little thing you are forgetting
to do. Good Luck!
Donna

------------
Frank at 11/23/99 11:09:44 AM

Mike,
Your query is not even close to being a &#34;complex query&#34;. Complex query we encountered breaks the ASP script into pieces and yet ASPdb took care of them. Look for them at the aspdb site. But as for your query I made up an equivalent and do not see any problem. You can modify the attached code to show the problem. Using the NWIND as a common DB, the debug is easier.

<%
Set X=Server.CreateObject(&#34;AspDB.EP&#34
X.dbQuickProps=&#34;1;NWIND;;Grid;4,auto;;;ACCESS; 5&#34;
X.dbSQL=&#34;SELECT * FROM employees WHERE (title = &#39;Sales Representative&#39 AND (EmployeeID > 2) AND (TitleOfCourtesy=&#39;Ms.&#39; Or TitleOfCourtesy=&#39;Mr.&#39; Or TitleOfCourtesy=&#39;Mrs.&#39&#34;
X.aspdbEP
%>

You can use either ASPDB.EP or ASPDB.Pro... no difference !!


FK




------------
Mike James at 11/23/99 9:47:42 AM

I&#39;m pretty new to ASP-db and currently I&#39;m only trying to use it to display a table on screen. For the simple queries there doesn&#39;t seem to be a problem, I&#39;m just defining the SQL statement to variable and then passing that variable to ASPdb. The problem I&#39;m having is when I do this with more complex queries, I get the following:
Error #=0, Description=[Microsoft][ODBC Driver Manager] Driver&#39;s SQLSetConnectAttr failed, Source=Microsoft OLE DB Provider for ODBC Drivers, WQL State=IM006, NativeError=0.

Here&#39;s the SQL Variable that I&#39;m trying to pass ASPdb:
SQLStmt = &#34;SELECT * FROM equiphst WHERE (equiphst.fac_nbr = &#39;&#34; & Session(&#34;fac_nbr&#34 & &#34;&#39 AND (EQUIP_DEPT = &#39;&#34; & Request.Form(&#34;lstDept&#34 & &#34;&#39 AND (LH_STATUS=&#39;PD&#39; Or LH_STATUS=&#39;PRP&#39; Or LH_STATUS=&#39;CWP&#39&#34;

It&#39;s this last AND statement that seems to be causing the problem. Is there a better way of doing this? Also, if I remove the last AND statement from the query, I do get the expected results from the database, but if I try and sort them by clicking on a Column Header I get the following error:
Microsoft OLE DB Provider for ODBC Drivers error &#39;80040e14&#39;, [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected &#39;DELETE&#39;,&#39;INSERT&#39;,&#39;PROCEDURE&# 39;,&#39;SELECT&#39;, or &#39;UPDATE&#39;.

Any suggestions to either of my problems would be greatly appreciated. Just let me know if you need additional information. Thanks.