Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parameter Query Demands more Parameters (2000)

    I get the following runtime error when I try to run a parametr query from code:
    -2147217904 (80040e10) Too few parameters. Expected 3.
    This particular query (which appears in the Query section of the database window and has the first line 'PARAMETERS datPOSDate DateTime;' ) requires one parameter, not three. When I run the query directly, and provide the parameter from the keyboard, it works. The code I am using is:

    ' Create Command object
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "qry_AppendToSalesHistoryTemp"

    rstDates.Open "qry_DatesInPOSHeader", CurrentProject.Connection
    Do While Not rstDates.EOF
    Set prm = cmd.CreateParameter("datPOSDate", adDate, adParamInput, , rstDates(0))
    cmd.Parameters.Append prm

    ' arrParams = Array(CVDate(rstDates(0)))
    ' cmd.Execute varRecords, arrParams

    cmd.Execute varRecords
    rstDates.MoveNext
    Loop

    As you can see from the comments I have also tried to add the parameters as arguements of the Execute method, but had the same problem there as well. Has anybody any idea why I am getting this message?

    I was expecting to have most of my problems with the way I am adding the parameters and executing the command object inside a loop, but it fails on the first time through so I haven't got to that potential problem yet.

    Any help gratefully received,

    Ian

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameter Query Demands more Parameters (2000)

    You should create the parameters before you open the recordset. Then you can open the recordset using the command object like this:

    Set rstDates = cmd.Execute(Options:=adCmdStoredProc)
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Query Demands more Parameters (2000)

    Hi Charlotte,

    I think the opening of the recordset is unrelated to the problem. The query that is giving the error is qry_AppendToSalesHistoryTemp which is an append query. I open the recordset based on qry_DatesInPOSHeader to get the parameters which I then try to pass to qry_AppendToSalesHistoryTemp using the parameter object code. The error occurs with the line cmd.Execute varRecords, which should be running the append query.

    Ian

    PS I'm sorry if this reply appears more than once, I kept getting site not available errors.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameter Query Demands more Parameters (2000)

    The point is that in ADO, you don't have to open the recordset to get the parameters. You've created a command object already, so the parameters are available to you through that command object. So create the parameters *before* you execute the command object to create the recordset. You're creating the command object but you aren't using it to open the recordset, so the recordset doesn't inherit the parameters.
    Charlotte

Posting Permissions

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