Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Procedure or function InsertOrders has too many arguments specified.

Posted on 2013-10-30
6
Medium Priority
?
874 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 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

618 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