July 21, 2003 at 9:41 am
I received an error message as follow:
"too many arguments provided for stored procedure"
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_subform!work_ord_num)
.Parameters.Append .CreateParameter("@work_ord_line_num", adChar, adParamInput, 3, Forms!edit_shipping_sched!shipping_sched_list_subform!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!
July 21, 2003 at 11:40 am
rs_save_shipping_history.MoveNext
Loop
This means u r using a loop .
In first loop u created and appended two
Parameters @work_ord_num char(9) and
@work_ord_line_num char(3)
and in Subsequent loops u r creating and appending Parameters with out removing previous Parameters.
July 21, 2003 at 12:54 pm
No need to delete the parameters like padmakumar suggested. It would just waste CPU effort. Simply create the command OUTSIDE of the Loop and execute for each record in rs_save_shipping_history recordset:
If save_shipping_history.Parameters("ret_val").Value > 0 Then
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_subform!work_ord_num)
.Parameters.Append .CreateParameter("@work_ord_line_num", adChar, adParamInput, 3, Forms!edit_shipping_sched!shipping_sched_list_subform!work_ord_line_num)
End With
Do While Not rs_save_shipping_history.EOF
With undo_error_fields
.Execute , , adExecuteNoRecords
rs_save_shipping_history.MoveNext
End With
Loop
End If
End If
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply