Solved

Procedure or function InsertOrders has too many arguments specified.

Posted on 2013-10-30
6
850 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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to SQL Trace a SPECIFIC query 24 57
error on the asp.net page 1 33
Runtime Exceptions when trying to submit data 28 36
Angular JS Route 3 47
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

911 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now