SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    [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.
    Code:
     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:
    Code:
    rsShowSales.open sqlStr,con1,3,3
    Other relevant lines in the code are:
    Code:
    
    set Con1 = Server.CreateObject("ADODB.Connection")
    con1.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=\\premfs2\sites\Database\db1c.mdb")
    and

    Code:
    dim rsShowSales, sqlStr 
    set rsShowSales = server.CreateObject("ADODB.recordset")
    and here is the query
    Code:
    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;"
    Last edited by akohl; May 22, 2002 at 08:21.

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,265
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)

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

    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.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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?
    Last edited by akohl; May 22, 2002 at 12:31.

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,265
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    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.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  5. #5
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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?

  6. #6
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    tried that just now

    I tried your suggestion.
    That line came out like this

    Code:
    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.

  7. #7
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,265
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)

    Re: tried that just now

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

    Code:
    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.
    My guess is that the ADO doesn't allow the Now()-1

    Try this:

    Code:
    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)
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  8. #8
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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?

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

    [code]
    sqlStr=sqlStr&" WHERE (((shipments.date_entered)>= #" & strDate & "#))"
    [code/]



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

    Code:
    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.
    Last edited by akohl; May 23, 2002 at 13:38.

  9. #9
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,265
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    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.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  10. #10
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,265
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    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/showt...threadid=59296
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  11. #11
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    thanks

    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •