Error Executing Access Database Query

[kschock]

The query works in 5, chokes in MX7
Error Message:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or
'UPDATE'.

The error occurred in
C:\Inetpub\wwwroot\srd\land\restr_areas\CountySear chCode.cfm: line 3
Called from C:\Inetpub\wwwroot\srd\land\restr_areas\index.cfm: line 26
Called from C:\Inetpub\wwwroot\srd\land\restr_areas\CountySear chCode.cfm: line
3
Called from C:\Inetpub\wwwroot\srd\land\restr_areas\index.cfm: line 26

1 : <CFStoredProc Procedure="qWebAreasMenu"
Datasource="srd_RestrictedAreas">
2 : <CFProcParam value="#CountyName#" cfsqltype="CF_SQL_VARCHAR"
type="In">
3 : <CFProcResult Name="C_Menu_Options" maxrows="-1">
4 : </CFStoredProc>
5 :


--------------------------------------------------------------------------------

SQL {call qWebAreasMenu( (param 1) )}
DATASOURCE srd_RestrictedAreas
VENDORERRORCODE 3092
SQLSTATE &nbsp;

Please try the following:
Check the ColdFusion documentation to verify that you are using the correct
syntax.
Search the Knowledge Base to find a solution to your problem.


Browser Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR
1.1.4322)
Remote Address 142.94.5.96
Referrer [url]http://d-c-240186/srd/land/restr_areas/[/url]
Date/Time 15-Apr-05 10:36 AM

-----------------------------------------------------------
Access Query:

PARAMETERS pCountyName Text ( 255 );
SELECT [Counties].[RecNo], [Counties].[CountyName],
[Counties].[CountyShortName], [RestrAreas].[FileName], [RestrAreas].[RestrNo]
FROM RestrAreas RIGHT JOIN (Counties RIGHT JOIN Pointers ON
[Counties].[RecNo]=[Pointers].[CountyRecNo1]) ON
[RestrAreas].[RestrNo]=[Pointers].[RestrNo]
WHERE ((([Counties].[CountyName])=[pCountyName]))
ORDER BY [Counties].[CountyShortName], [RestrAreas].[RestrNo];

[paross1]

Interesting. As a test, I set up an Access 97 database with a parameter query
saved as a stored procedure, and then called that procedure via CFSTOREDPROC in
CF 7, exactly like you have done. I did not have any problems returning a
result set. In fact, I set up my Access data source as both an ODBC socket DSN
and a Microsoft Access DSN in CF administrator, and both worked exactly the
same. (Note that you can do away with the maxrows="-1" attribute in your
CFPROCRESULT tag, as that was a CF 5 "thing".

How do your other queries behave. Are you able to successfully execute any SQL
via CFQUERY tags? I played around in CF Admin by disabling stored procedures in
the DSN, but when I did that, I got a distinct message that stated that Stored
Procedures were not allowed, so I don't think your problem is that DSN setup
parameter.

Sorry that I was not any help, but at least I know that I am able to call an
Access 97 "stored proc" via CF 7.

Phil :beer;

[kschock]

Phil,
We are using Access 2000 (9.0.4402 sr-1). OS: Windows 2000 (service pack 4) Any query made from the cfm pages work, it just won't allow parameters to be passed.