[ODBC Microsoft Access Driver] Too few parameters. Expected 3

I am getting the following error message from my
website on a page that works fine when I test it
with iss on my own computer. Also, another subroutine
(a login script), on the same asp page, using the
same connection object and an identically set up
recordset object works fine. So I don’t see why it’s
not working here. Is it because of my use of
aliases for field names in the sql query?

I asked support at my webhosting company and they
responded that there is an invalid field name
either in the where or orderby clauses.

But the entire query is cut and paste from the
access databse that it connects to. And the query
works fine on the identical db file on my own
computer both from within access and through
the adodb conncection.

Can someone please explain the meaning of the error
message? What parameters is server expecting?

Thanks.


 Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 3.
/akohl/pomeranz/default.asp, line 1595

The line it refers to is:


rsShowSales.open sqlStr,con1,3,3

Other relevant lines in the code are:




set Con1 = Server.CreateObject("ADODB.Connection")
con1.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=\\\\premfs2\\sites\\Database\\db1c.mdb")


and


dim rsShowSales, sqlStr
set rsShowSales = server.CreateObject("ADODB.recordset")

and here is the query


sqlStr="SELECT shipments.shipment_id, shipments.date_entered, shipping_codes.code_name,"
sqlStr=sqlStr&"[books].[title] & "" - "" & [issue_details].[binding] AS Book,"
sqlStr=sqlStr&"issue_details.price, orders.quantity, [price]*[quantity] AS total,"
sqlStr=sqlStr&"[users].[first_name] & "" "" & [users].[middle_name] & "" "" & [users].[last_name] AS Name,"
sqlStr=sqlStr&"users.pob, users.street, users.town, [users].[state] & "" "" & [users].[country] AS State,"
sqlStr=sqlStr&"users.zip, [users].[phone_area_code] & ""-"" & [users].[phone_standard] AS Phone,"
sqlStr=sqlStr&"accounts.card_type, accounts.card_number, accounts.date_expires"
sqlStr=sqlStr&" FROM books INNER JOIN ((((orders INNER JOIN shipments ON orders.shipment_id = shipments.shipment_id) INNER JOIN (shipping_codes INNER JOIN issue_details ON shipping_codes.[shipping-code_id] = issue_details.[shipping-code_id]) ON orders.issue_details_id = issue_details.issue_details_id) INNER JOIN accounts ON orders.account_id = accounts.account_id) INNER JOIN users ON accounts.user_id = users.user_id) ON books.book_id = issue_details.book_id"
sqlStr=sqlStr&" WHERE (((shipments.date_entered)>=Now()-"&days&"))"
sqlStr=sqlStr&" ORDER BY shipments.shipment_id, issue_details.price DESC;"

On quick glance, I would guess your problem is with this part of your where clause:

sqlStr= sqlStr & " WHERE (((shipments.date_entered)>=Now()-“&days&”))"

I would calculate that Now - days separately and then include that calculation in your statement. Might be an extra step, but I think it’s easier to read.

I see what you mean. It would be easier to read. But the way I wrote it was fine by my server. I wrote it that way because I created the query in access and just cut and pasted the sql from access. That should be the most access-compatible sql syntax possible.

It just didn’t work on the webhosting server. Are we talking about incorrect syntax that works on some “forgiving” servers and not others depending on how they are configured?

I hope I don’t have to start worrying about cross-server compatibility for my asp scripts like we have to worry about cross browser compatibility for client side scripts.

As far as the error message is concerned, does it refer to a badly written sql string. It referenced the line of code which opens the recordset and said that it expected more parameters.

I still don’t get what this means and am baffled as to how the script worked on my server and not the host’s.

Any more ideas?

I’m not sure why your code is not working on your server if it’s working at home. Are you working on different databases between the two (access 97 & 2000, access & SQL Server)?

The error is in the SQL Statement. I would suggest doing a response.write of the sql statement right before the execution and try to determine what it’s trying to execute. The days field might be blank or non-numeric, etc.

I did try cint(days) to make sure it was numeric.
I know it wasn’t an emplty string because I did a response.write(days) before th query and saw that it printed the number.

But I’ll try what you suggest.

Just to clarify, are you saying that the problem is with the query based on the error message?

I tried your suggestion.
That line came out like this


WHERE (((shipments.date_entered)>=Now()-1))
ORDER BY shipments.shipment_id, issue_details.price DESC;

I pasted the whole thing from the browser back into access and it worked fine. Its the identical file as I have on the remote server. The only difference is something on that server that doesn’t want to let that sql execute on my access file.

Originally posted by akohl
[B]I tried your suggestion.
That line came out like this


WHERE (((shipments.date_entered)>=Now()-1))
ORDER BY shipments.shipment_id, issue_details.price DESC;

I pasted the whole thing from the browser back into access and it worked fine. Its the identical file as I have on the remote server. The only difference is something on that server that doesn’t want to let that sql execute on my access file. [/B]

My guess is that the ADO doesn’t allow the Now()-1

Try this:


Dim strDate
strDate = DateToStr(DateAdd("d",(days * -1),Now()))

sqlStr="SELECT shipments.shipment_id, shipments.date_entered, shipping_codes.code_name,"
sqlStr=sqlStr&"[books].[title] & "" - "" & [issue_details].[binding] AS Book,"
sqlStr=sqlStr&"issue_details.price, orders.quantity, [price]*[quantity] AS total,"
sqlStr=sqlStr&"[users].[first_name] & "" "" & [users].[middle_name] & "" "" & [users].[last_name] AS Name,"
sqlStr=sqlStr&"users.pob, users.street, users.town, [users].[state] & "" "" & [users].[country] AS State,"
sqlStr=sqlStr&"users.zip, [users].[phone_area_code] & ""-"" & [users].[phone_standard] AS Phone,"
sqlStr=sqlStr&"accounts.card_type, accounts.card_number, accounts.date_expires"
sqlStr=sqlStr&" FROM books INNER JOIN ((((orders INNER JOIN shipments ON orders.shipment_id = shipments.shipment_id) INNER JOIN (shipping_codes INNER JOIN issue_details ON shipping_codes.[shipping-code_id] = issue_details.[shipping-code_id]) ON orders.issue_details_id = issue_details.issue_details_id) INNER JOIN accounts ON orders.account_id = accounts.account_id) INNER JOIN users ON accounts.user_id = users.user_id) ON books.book_id = issue_details.book_id"
sqlStr=sqlStr&" WHERE (((shipments.date_entered)>= '#" & strDate & "#'))"
sqlStr=sqlStr&" ORDER BY shipments.shipment_id, issue_details.price DESC;"

Don’t know why it would work on your home PC but not on the server (I could see why it would work in Access but not on the server, but not between two versions of ASP)

I tried it like this.
I removed the single quotes around the date expression to get it to work in access. But I had the exact same results. The same error message even when the sql from the response.write(sqlStr) worked fine in access as a paste in. This is really starting to baffle me. do you think somethng is wrong with the server? Should I bug the hosting company about it and assert that the problem is with their ado and not with my script?


Dim strDate
strDate = cStr(DateAdd("d",(days * -1),Now()))
[code/]


sqlStr=sqlStr&" WHERE (((shipments.date_entered)>= #" & strDate & “#))”

[code/]

Then I tried commenting out both the WHERE and ORDER BY clauses like this"


sqlStr="SELECT shipments.shipment_id, shipments.date_entered, shipping_codes.code_name,"
sqlStr=sqlStr&"[books].[title] & "" - "" & [issue_details].[binding] AS Book,"
sqlStr=sqlStr&"issue_details.price, orders.quantity, [price]*[quantity] AS total,"
sqlStr=sqlStr&"[users].[first_name] & "" "" & [users].[middle_name] & "" "" & [users].[last_name] AS Name,"
sqlStr=sqlStr&"users.pob, users.street, users.town, [users].[state] & "" "" & [users].[country] AS State,"
sqlStr=sqlStr&"users.zip, [users].[phone_area_code] & ""-"" & [users].[phone_standard] AS Phone,"
sqlStr=sqlStr&"accounts.card_type, accounts.card_number, accounts.date_expires"
sqlStr=sqlStr&" FROM books INNER JOIN ((((orders INNER JOIN shipments ON orders.shipment_id = shipments.shipment_id) INNER JOIN (shipping_codes INNER JOIN issue_details ON shipping_codes.[shipping-code_id] = issue_details.[shipping-code_id]) ON orders.issue_details_id = issue_details.issue_details_id) INNER JOIN accounts ON orders.account_id = accounts.account_id) INNER JOIN users ON accounts.user_id = users.user_id) ON books.book_id = issue_details.book_id;"
'sqlStr=sqlStr&" WHERE (((shipments.date_entered)>= #" & strDate & "#))"
'sqlStr=sqlStr&" WHERE (((shipments.date_entered)>=Now()-"&days&"))"
'sqlStr=sqlStr&" ORDER BY shipments.shipment_id, issue_details.price DESC;"


Guess what. Same results. I got a query that works fine in access and gets the exact same error message as before.

Hmmmm.

I’m assuming the ; right before the where statement was only added because the where statements were commented out.

Otherwise, are you sure your databases are exactly the same? That’s the only other reason I can see for something not working through ADO when it works in straight access.

Can you show exactly what the whole strsql response.writes out to? I just need to see it to see if I’m missing something in your code.

Your other option is to convert this SQL statement to a stored query and access it that way.

Here is a good thread to show you how…
http://www.sitepointforums.com/showthread.php?s=&threadid=59296

Thanks. That is a really interesting thread and article, which I am going to take a close look at.

I’m sure there was nothing wrong with the WHERE or ORDER BY clauses. I commented them out completely and got the identical results.

How sure am I that it was the same database? Pretty…
pretty darn!(Do you know which movie that quote’s from?)

In the meantime, I got it to work by taking out the calculated fields and had the asp page to the calculations instead.

Strange that I couldn’t get it to work with calculated fields on the host’s server.