[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

CRecordset.Open throws exception querying MS Access database "Too few parameters. Expected 1"

Posted on 2008-02-04
10
Medium Priority
?
840 Views
Last Modified: 2009-03-02
using a simple call:
CString queryStr;
queryStr.Format("SELECT Col1 FROM UnionQuery");
recset.Open(CRecordset::forwardOnly,queryStr,CRecordset::readOnly);

the last statement throws an exc CDBException, and the message is too few parameters, Expected 1.  I cannot pinpoint the reason.  The UnionQuery object is a union query, and i tried to reduce it to find the problem, but it still happens with something like this:

SELECT "a" As Name, 0 as ID, "a" as Command, "a" as ResultB64, "a" as ResultNAB64, "a" As FileName,"a" As FileVersion,"a" as LocationPath,Yes As Required,"a" As UTC, 0 As ProductID,0 As ServicePackID, Yes As Auxiliary, "a" as Exe, "a" as Switches, "a" as Cmd, "a" as prerun, "a" as postrun, 0 as RebootDetectable, 0 as Replaced, "a" as ReplacedBy, 0 as NETSEC, "a" as MS, 0 As OSID
FROM SupportedOSes
WHERE 1=0

UNION

SELECT pScript.Name, cVerify.ID, Command, ResultB64, ResultNAB64, Null, Null, Null, Null, Null,Null,Null, Null ,Null ,Null ,Null ,Null ,Null ,Null ,pScript.Replaced ,Null ,pScript.NETSEC ,pScript.MS, pScript.OSID
FROM (tableP INNER JOIN pScript ON tableP.ID = pScript.ID) INNER JOIN cVerify ON cVerify.ID = tableP.ID
WHERE tableP.ID > 0
ORDER BY Name, ID ASC

The reason for the first query in the union is to establish the column types for the rest of the query.  THis is the first leg of the union, there are several more, but this seems to still reproduce the problem.
0
Comment
Question by:bowser17
  • 6
  • 3
10 Comments
 
LVL 85
ID: 20819331
If you open the query in Access (or some other query designer) does it work correctly?
0
 
LVL 1

Author Comment

by:bowser17
ID: 20824231
Yes.  and ive searched google.  All of the google results talk about an invalid column name, which would make sense since it would be viewed as a parameter.  This is not the case.
0
 
LVL 85
ID: 20825017
Can you post the SQL of the query here?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 85
ID: 20825049
Sorry ... I see that you already have.

Is this your actual query that you're running:

SELECT Col1 FROM UnionQuery

or is this just an example? If it's NOT just an example, then I don't see a Col1 field in your query ...
0
 
LVL 1

Author Comment

by:bowser17
ID: 20825076
Its in there the original message.  There are 2 queries, the union query, and then the query from C++.  The C++ query is very simple, SELECT Col1 from UnionQuery
0
 
LVL 1

Author Comment

by:bowser17
ID: 20825616
Sorry, yes.  that was an example.. just select the first field, or SELECT *.. it all gives the same exception
0
 
LVL 1

Author Comment

by:bowser17
ID: 21107915
If anyone is still looking at this thread, (maybe i should start a new one)...

I was thinking about this and maybe its a driver issue?  What are the alternatives to CDatabase and ODBC connections?  I was thinking about using the Jet driver, but i cannot figure out how to do it.  I believe the jet driver is what Access uses internally?
0
 
LVL 1

Author Comment

by:bowser17
ID: 21156345
This question has been submitted to the Microsoft product group.
0
 
LVL 1

Accepted Solution

by:
bowser17 earned 0 total points
ID: 21407002
a very simple, yet frustrating solution:

do not use double quotes in access queries.  perhaps this is a best practice that i missed.  According to http://support.microsoft.com/?id=237994  this query would have worked in old versions of MDAC.  i feel that if used to work and it is a valid syntax in access, it should continue to work today.  I'm trying to get a more robust answer from MS.
0
 

Expert Comment

by:sdela
ID: 23780683
Very frustrating indeed! Thank you for posting the solution. I had built my union SQL query based on a normal query so that the double quotes appear automatically in SQL view. I had another union query that imported into Excel without a problem because the query was based on a linked query and a table (no double quotes appear in this case in SQL view).

THANK YOU!!!!

0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question