Results 1 to 7 of 7
  1. #1
    Join Date
    May 2002
    Posts
    395

    Unanswered: too many arguments provided for stored procedure

    I received an error message as follow:
    ""
    This is my code:
    If save_shipping_history.Parameters("ret_val").Value > 0 Then
    'Do While Not (rs_save_shipping_history.EOF)
    If Not (rs_save_shipping_history.EOF) Then
    With undo_error_fields
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "spUndo_shipping_fields"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("@work_ord_num", adChar, adParamInput, work_ord_num_length, Forms!edit_shipping_sched!shipping_sched_list_subf orm!work_ord_num)
    .Parameters.Append .CreateParameter("@work_ord_line_num", adChar, adParamInput, 3, Forms!edit_shipping_sched!shipping_sched_list_subf orm!work_ord_line_num)
    .Execute , , adExecuteNoRecords
    End With
    rs_save_shipping_history.MoveNext
    Loop
    End If

    End If

    This is the stored procedure:
    CREATE PROCEDURE dbo.spUndo_shipping_fields
    @work_ord_num char(9),
    @work_ord_line_num char(3)
    AS
    Update tblshipping_sched set shipment_complete = 0, shipped_qty_remaining = shipped_qty_remaining + shipped_qty; -where work_ord_num = @work_ord_num and work_ord_line_num = @work_ord_line_num;

    I'm passing the correct number of arguments. Can anyone see what I did wrong?

    Thank you much!

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    It appears to me that you are defining and creating your parameters within a loop. I would think that by doing this you would end up appending the definitions. You should turn on SQL Profiler to see what is being passed.

    You should setup your procedure call and parameters outside the loop once and then use PARAMETERS.ITEM() to set the parameter values each time it is called.
    MCDBA

  3. #3
    Join Date
    May 2002
    Posts
    395
    Originally posted by achorozy
    It appears to me that you are defining and creating your parameters within a loop. I would think that by doing this you would end up appending the definitions. You should turn on SQL Profiler to see what is being passed.

    You should setup your procedure call and parameters outside the loop once and then use PARAMETERS.ITEM() to set the parameter values each time it is called.
    I am not quite sure how to call the stored procedure outside of the loop and how and where to use PARAMETERS.ITEM(). Could you provide me w/ an example in my code?
    This is what I have:

    Sub save_shipped_input()
    Dim save_shipping_history As New ADODB.Command, rs_save_shipping_history As New Recordset
    Dim undo_error_fields As New ADODB.Command

    With save_shipping_history
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "spUpdate_shipping_sched"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
    .Parameters.Append .CreateParameter("@t1", adInteger, adParamOutput)
    :
    Set rs_save_shipping_history = .Execute
    End With

    Debug.Print rs_save_shipping_history(0).Value ‘ yes this is one of the fields I need
    Debug.Print rs_save_shipping_history(1).Value ‘ the other fields



    With undo_error_fields
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "spUndo_shipping_fields"
    .CommandType = adCmdStoredProc
    ''parameter object is improperly defined. Inconsistent or .....
    .Parameters.Append .CreateParameter("@work_ord_num", adChar, adParamInput, work_ord_num_length, rs_save_shipping_history(0).Value)
    .Parameters.Append .CreateParameter("@work_ord_line_num", adChar, adParamInput, 3, rs_save_shipping_history(1).Value)
    ''''too many arguments were supplied for sp when attempting to execute the second record
    .Execute , , adExecuteNoRecords
    End With

    If save_shipping_history.Parameters("ret_val").Value > 0 Then
    Do While Not (rs_save_shipping_history.EOF)

    ‘’’’’’’ Is this where you call stored procedure? How?
    ‘’’’’’’ Is this where I should place Parameters.Item()’’’’’’’’’’’’’’’
    undo_error_fields.Parameters.Item (0)


    rs_save_shipping_history.MoveNext
    Loop

    End If

    Other code………..

    Thank you so very much !

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Code:
    If save_shipping_history.Parameters("ret_val").Value > 0 Then
        With undo_error_fields
            .ActiveConnection = CurrentProject.Connection
            .CommandText = "spUndo_shipping_fields"
            .CommandType = adCmdStoredProc
            .Parameters.Append .CreateParameter("@work_ord_num", adChar, adParamInput, , work_ord_num_length)
            .Parameters.Append .CreateParameter("@work_ord_line_num", adChar, adParamInput, , 3)
        End With
    
        Do While Not (rs_save_shipping_history.EOF)
            If Not (rs_save_shipping_history.EOF) Then
                With undo_error_fields
                    .Parameters.Item(0) = Forms!edit_shipping_sched!shipping_sched_list_subform!work_ord_num
                    .Parameters.Item(1) = Forms!edit_shipping_sched!shipping_sched_list_subform!work_ord_line_num
                    .Execute , , adExecuteNoRecords
            End With
            rs_save_shipping_history.MoveNext
        Loop
    End If
    MCDBA

  5. #5
    Join Date
    May 2002
    Posts
    395

    error message "Parameter object is improperly defined"

    Originally posted by achorozy
    Code:
    If save_shipping_history.Parameters("ret_val").Value > 0 Then
        With undo_error_fields
            .ActiveConnection = CurrentProject.Connection
            .CommandText = "spUndo_shipping_fields"
            .CommandType = adCmdStoredProc
            .Parameters.Append .CreateParameter("@work_ord_num", adChar, adParamInput, , work_ord_num_length)
            .Parameters.Append .CreateParameter("@work_ord_line_num", adChar, adParamInput, , 3)
        End With
    
        Do While Not (rs_save_shipping_history.EOF)
            If Not (rs_save_shipping_history.EOF) Then
                With undo_error_fields
                    .Parameters.Item(0) = Forms!edit_shipping_sched!shipping_sched_list_subform!work_ord_num
                    .Parameters.Item(1) = Forms!edit_shipping_sched!shipping_sched_list_subform!work_ord_line_num
                    .Execute , , adExecuteNoRecords
            End With
            rs_save_shipping_history.MoveNext
        Loop
    End If
    
    Thank you so much with your help in the code.  They make alot of sense.
    But it is still not working. 
    An error occurred here:
    .CreateParameter ("@work_ord_num", adChar, adParamInput, , work_ord_num_length) 
    message: "Parameter object is improperly defined. Inconsistent or incomplete information was provided."
    I tried adding (rs_save_shipping_history(0).Value at the 5th argument the error still occurred.
    
    The only line of code I took out is: "If Not (rs_save_shipping_history.EOF) Then" 
    It seems that Do While and If not are both checking for  EOF.  
    Don't we need the 5th argument in 
    .CreateParameter method?
    
    Thank you for your time.  You help is greatly appreciated!!

  6. #6
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Change

    .Parameters.Append .CreateParameter("@work_ord_num", adChar, adParamInput, , work_ord_num_length)
    .Parameters.Append .CreateParameter("@work_ord_line_num", adChar, adParamInput, , 3)

    To

    .Parameters.Append .CreateParameter("@work_ord_num", adChar, adParamInput, work_ord_num_length)
    .Parameters.Append .CreateParameter("@work_ord_line_num", adChar, adParamInput, 3)

    As far as the IF logic you are removing, I just took that from your earlier example.
    MCDBA

  7. #7
    Join Date
    May 2002
    Posts
    395
    Originally posted by achorozy
    Change

    .Parameters.Append .CreateParameter("@work_ord_num", adChar, adParamInput, , work_ord_num_length)
    .Parameters.Append .CreateParameter("@work_ord_line_num", adChar, adParamInput, , 3)

    To

    .Parameters.Append .CreateParameter("@work_ord_num", adChar, adParamInput, work_ord_num_length)
    .Parameters.Append .CreateParameter("@work_ord_line_num", adChar, adParamInput, 3)

    As far as the IF logic you are removing, I just took that from your earlier example.
    I just found my mistake I forgot to call a function that loads the size of the variables in .creatparameter method. The error has not appeared.
    Thank you so much! You've been a BIG help!
    Thanks!!

    Have a great day!

Posting Permissions

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