Solved

Procedure or function InsertOrders has too many arguments specified.

Posted on 2013-10-30
6
859 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
asp.net web application 3 44
Running powershell scripts from ASP.NET 6 54
orderby list (from Json) 1 28
Linked Server - SP with Param to VIew 7 19
In this article I will describe the Backup & Restore 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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

740 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