Solved

Procedure or function InsertOrders has too many arguments specified.

Posted on 2013-10-30
6
860 Views
Last Modified: 2013-10-30
Hi

I am using a stored procedure to update an order table then update an orderitems table but I keep getting Procedure has too many arguments specified

Sub DatabaseInsert()

        Dim conn As SqlConnection
        Dim comm As SqlCommand

        conn = New SqlConnection(connectionstring)
        comm = New SqlCommand("InsertOrders", conn)
        comm.CommandType = System.Data.CommandType.StoredProcedure
        comm.Parameters.Add("@PropertyID", System.Data.SqlDbType.Int)
        comm.Parameters("@PropertyID").Value = PropID
        comm.Parameters.Add("@TotalOrderCost", System.Data.SqlDbType.Money)
        comm.Parameters("@TotalOrderCost").Value = Convert.ToDecimal(Label1.Text.ToString())
        comm.Parameters.Add("@PaymentMethodID", System.Data.SqlDbType.Int)
        comm.Parameters("@PaymentMethodID").Value = paymentmethod

        objDT = Session("Cart")

        For Each Me.objDR In objDT.Rows
            comm.Parameters.Add("@ProductID", System.Data.SqlDbType.Int)
            comm.Parameters("@ProductID").Value = objDR("ItemID")
            comm.Parameters.Add("@PriceID", System.Data.SqlDbType.Int)
            comm.Parameters("@PriceID").Value = objDR("PriceID")
            comm.Parameters.Add("@PricePaid", System.Data.SqlDbType.Money)
            comm.Parameters("@PricePaid").Value = objDR("Price")

        Next
        Session("Cart") = objDT

        conn.Open()
        comm.ExecuteNonQuery()
        conn.Close()
    End Sub

Open in new window


The stored procure has the following details:

USE [FYTWebsite]
GO

/****** Object:  StoredProcedure [dbo].[InsertOrders]    Script Date: 10/30/2013 10:53:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/****** Object:  StoredProcedure [dbo].[InsertOrders]    Script Date: 10/30/2013 07:57:09 ******/
ALTER PROCEDURE [dbo].[InsertOrders]
( 
@PropertyID int,
@TotalOrderCost money,
@PaymentMethodID int,
@ProductID int,
@PriceID int,
@PricePaid money
)
AS
--create new record
INSERT INTO Orders (OrderStatusID, PropertyID, TotalOrderCost, PaymentMethodID)
VALUES (3, @PropertyID, @TotalOrderCost, @PaymentMethodID)
--obtain orderid 
DECLARE @ORDERID INT
SET @ORDERID = scope_identity()
--Create new Items
INSERT INTO OrderItems (OrderID, ProductID, PriceID, PricePaid)
VALUES (@ORDERID, @ProductID, @PriceID, @PricePaid)



GO

Open in new window


It works a treat when there is only 1 item in the basket however errors when multiple ones have been selected.
0
Comment
Question by:TPLLimited
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39612223
>For Each Me.objDR In objDT.Rows
At first glance the SP has six parameters, and if this loop executes more than once then you'll have 9, 12, 15, ... parameters which will return the error.
0
 

Author Comment

by:TPLLimited
ID: 39612243
Thanks for the response

Should I create two stored procedures to get round this storing the orderid in a variable?
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39612259
The SP looks like it's designed to work ONLY if there is a single row to be inserted into OrderItems.
... which conflicts with the requirements of ...
Insert a single row in Orders, and multiple rows in OrderItems.

>I am using a stored procedure to update an order table then update an orderitems table
Just to get our terms straight, we're inserting (adding) rows, not updating (changing existing) rows.

Perhaps a better idea would be to have two SP's, one for Orders, and one for OrderItems.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:TPLLimited
ID: 39612281
Great thanks

Sorry yes this is purely Inserting the records - a single row for orders and multiple for orderitems.

If creating 2 SP's how do you pass the ID from the single row insert into the multiple SP.

Would this need to be returned to the application and stored in a variable or can this be done via SP's only?
0
 

Author Closing Comment

by:TPLLimited
ID: 39612369
Creating 2 SP's worked a charm

Thanks
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39612376
Thanks for the accept.  Good luck with your project.  -Jim
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question