CODEWITHSUNDEEP
sqlms-accesspyodbc
eep
eep

Reputation: 73

Too few parameters error using pyodbc on a query containing a hardcoded where clause?

I've seen several questions about the "Too Few Parameters" error but have not found one that quite matches my situation.

Simplified code:

db.cursor.execute ('''SELECT [PT_NUMBER] FROM [newtest1];''')

newtest1 is a saved MS Access query containing a subquery with a hardcoded where clause:

SELECT table1.PT_NUMBER, table1.ID
FROM table1
WHERE (table1.ID  In (select ID from table2 where field1 = "TESTVALUE"));

My error is:

pyodbc.Error: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver]
Too few parameters. Expected 1. (-3010) (SQLExecDirectW)')

If I instead use the same code to select from a query that does not contain a subquery with a hardcoded where clause, I get no error:

db.cursor.execute ('''SELECT [PT_NUMBER] FROM [newtest2];''')

With newtest2 defined as:

SELECT table1.PT_NUMBER, table1.ID
FROM table1
WHERE (table1.ID In (select ID from table2));

So it doesn't seem to be the most common issue of a column name typo, nor an issue about passing in a parameter directly rather than using ?, at least not from the code.

It seems that the hardcoded WHERE is causing a problem even though it's in the Access query, not the python code.

Is it possible to SELECT from an Access query with a subquery with a hardcoded WHERE clause in this way, with pyodbc?

Any pointers will be much appreciated.

Upvotes: 1

Views: 1090

Answers (1)

eep
eep

Reputation: 73

I ended up getting this to work correctly, by using single quotes around the hardcoded value in the access query, instead of double quotes:

WHERE (table1.ID  In (select ID from table2 where field1 = 'TESTVALUE'))

Upvotes: 3

Related Questions