[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Procedure or function InsertOrders has too many arguments specified.

Posted on 2013-10-30
6
Medium Priority
?
880 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
  • 3
  • 3
6 Comments
 
LVL 66

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 66

Accepted Solution

by:
Jim Horn earned 1500 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

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 66

Expert Comment

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Screencast - Getting to Know the Pipeline

872 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