Procedure or function has too many arguments specified

Question

5.00/5 (1 vote)

See more:

this button click event on which i am trying to save the data to database. and it gives me error procedure or function save_sale_Master has too many arguments specified

C#

private void generatebill_Click(object sender, EventArgs e)
        {
            
            SqlCommand cmd;

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            System.Data.SqlClient.SqlTransaction _trans = con.BeginTransaction();
            cmd = new SqlCommand("", con, _trans);
            cmd.Transaction = _trans;

            if (!AddEdit)
            {
                cmd.CommandText = "select_invoice";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@bs1",Convert.ToInt32(BILLSERIES.SelectedIndex));
                //sp = cmd.ExecuteNonQuery();

                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adp.Fill(dt);

                var Invoice_No = "";

                if (dt.Rows.Count>0)
                {
                    Invoice_No = dt.Rows[0]["Invoice_No"].ToString();
                    newinvoice = GenerateNumber(Invoice_No);
                }
                else
                {
                    Invoice_No = (BILLSERIES.Text + "00000");
                    newinvoice = GenerateNumber(Invoice_No);
                }
                
            }

            //if(sp<0)
            try
            {
                cmd.CommandText = "save_sale_Master";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@acc_no", CUSTOMERACCOUNTNO.Text);
                if (AddEdit)
                {
                    cmd.Parameters.AddWithValue("@Invoice_No", INVOICENO.Text);
                }
                else if (!AddEdit)
                {
                    cmd.Parameters.AddWithValue("@Invoice_No", newinvoice);
                }
                cmd.Parameters.AddWithValue("@Invoice_Date", invoicedate.Text);
                cmd.Parameters.AddWithValue("@PaymentMethod_Code", Convert.ToInt32(MODEOFPAYMENT.SelectedValue));
                cmd.Parameters.AddWithValue("@Total_Amount", Convert.ToDouble(TOTAL.Text));
                cmd.Parameters.AddWithValue("@Total_Discount", Convert.ToDouble(DISCOUNTAMOUNT1.Text == "" ? "0" : DISCOUNTAMOUNT1.Text));
                cmd.Parameters.AddWithValue("@Fright_Charges", Convert.ToDouble(FRIGHTCHARGES.Text == "" ? "0" : FRIGHTCHARGES.Text));
                cmd.Parameters.AddWithValue("@GST", Convert.ToInt32(GST.SelectedValue));
                cmd.Parameters.AddWithValue("@GST_Amount", Convert.ToDouble(GSTAMOUNT.Text));
                cmd.Parameters.AddWithValue("@Total_BillToBePaid", Convert.ToDouble(GRANDTOTAL.Text));
                cmd.Parameters.AddWithValue("@RoundingOff", Convert.ToDouble(GRANDTOTAL.Text == "" ? "0" : GRANDTOTAL.Text));
                cmd.Parameters.AddWithValue("@remark", remark.Text);
                cmd.Parameters.AddWithValue("@spremark", spremark.Text);
                cmd.Parameters.AddWithValue("@trans", TRANSPORT.Text);
                cmd.Parameters.AddWithValue("@grnno", grn.Text);
                cmd.Parameters.AddWithValue("@vichelno", vichle.Text);
                cmd.Parameters.AddWithValue("@bs", Convert.ToInt32(BILLSERIES.SelectedValue));

                if(AddEdit)
                {
                    cmd.Parameters.AddWithValue("@CALLTYPE", "Update");
                }
                else if(!AddEdit)
                {
                    cmd.Parameters.AddWithValue("@CALLTYPE", "Insert");
                }

                int t = cmd.ExecuteNonQuery();

                int counter = 0;

                if (t > 0)
                {
                    for (counter = 1; counter < itemgrid1.Rows.Count; counter++)
                    {
                        cmd.Parameters.Clear();
                        cmd.CommandText = "save_sale_detail";
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@Item_Description", itemgrid1.Rows[counter - 1].Cells["Item_Description"].Value);
                        cmd.Parameters.AddWithValue("@HSN_Code", itemgrid1.Rows[counter - 1].Cells["HSN_Code"].Value);
                        cmd.Parameters.AddWithValue("@Quantity", Convert.ToInt32(itemgrid1.Rows[counter - 1].Cells["Quantity"].Value));
                        cmd.Parameters.AddWithValue("@Converstion_Type", itemgrid1.Rows[counter - 1].Cells["Converstion_Type"].Value);
                        cmd.Parameters.AddWithValue("@Rate", Convert.ToInt32(itemgrid1.Rows[counter - 1].Cells["Rate"].Value));
                        cmd.Parameters.AddWithValue("@amount", Convert.ToDouble(itemgrid1.Rows[counter - 1].Cells["Amount"].Value));
                                                
                        if (AddEdit)
                        {
                            cmd.Parameters.AddWithValue("@units", itemgrid1.Rows[counter - 1].Cells["Unit_Type"].Value);
                            cmd.Parameters.AddWithValue("@Invoice_No", INVOICENO.Text);
                            cmd.Parameters.AddWithValue("@CALLTYPE", "Update");
                        }

                        if (!AddEdit)
                        {
                            cmd.Parameters.AddWithValue("@Invoice_No", newinvoice);
                            cmd.Parameters.AddWithValue("@units", itemgrid1.Rows[counter - 1].Cells["Unit_Type"].Value);
                            cmd.Parameters.AddWithValue("@CALLTYPE", "Insert");
                        }

                        cmd.ExecuteNonQuery();
                    }
                }
                if ( GRANDTOTAL.Text == "")
                {
                    MessageBox.Show("Please Add Items To Grid Along With Price");
                }

                else if (CUSTOMERNAMETEXT.Text == "")
                {
                    MessageBox.Show("Please Select Customer For Billing");
                }

                else if (counter == itemgrid1.Rows.Count)
                {
                    _trans.Commit();
                    MessageBox.Show("DATA HAS BEEN SAVED");                    
                    //invno = INVOICENO.Text;
                }
            }
            catch(SqlException err)
            {
                _trans.Rollback();
                MessageBox.Show(err.Message);
            }
        }

Stored proc is:

SQL

CREATE PROCEDURE [dbo].[save_sale_Master]	
	@acc_no nvarchar(50),
	@Invoice_No nvarchar(50),
	@Invoice_Date DATE, 
	@PaymentMethod_Code INT,
	@Total_Amount int,
	@Total_Discount decimal(9,2),  
	@Fright_Charges decimal(9,2), 
	@GST int, 
	@GST_Amount decimal(9,2), 
	@Total_BillToBePaid decimal(9,2), 
	@RoundingOff int,
	@remark nvarchar(1000),
	@spremark nvarchar(1000),
	@trans nvarchar(50),
	@grnno int,
	@vichelno nvarchar(50),
	@bs int,
	@CALLTYPE nvarchar(50)
	
AS
if (@CALLTYPE='Insert')
begin
	insert into Invoice_Bills(Invoice_No,Invoice_Date,PaymentMethod_Code,Total_Discount,
	Fright_Charges,GST,GST_Amount,Total_BillToBePaid,RoundingOff,Total_Amount,AccountNo,remark,specialremark,
	transportername,grnno,vichelno,Bill_series)
	values(@Invoice_No,@Invoice_Date,@PaymentMethod_Code,@Total_Discount,
	@Fright_Charges,@GST,@GST_Amount,@Total_BillToBePaid,@RoundingOff,@Total_Amount,@acc_no,@remark,@spremark,
	@trans,@grnno,@vichelno,@bs)
end
else if (@CALLTYPE='Update')
begin
	update Invoice_Bills set 
							 PaymentMethod_Code=@PaymentMethod_Code,
							 Total_Discount=@Total_Discount,
							 Fright_Charges=@Fright_Charges,
							 GST=@GST,
							 GST_Amount=@GST_Amount,
							 Total_BillToBePaid=@Total_BillToBePaid,
							 RoundingOff=@RoundingOff,
							 Total_Amount=@Total_Amount,
							 AccountNo=@acc_no,
							 remark=@remark,
							 specialremark=@spremark,
							 transportername=@trans,
							 grnno=@grnno,
							 vichelno=@vichelno,
							 Bill_series=@bs

	where Invoice_No=@Invoice_No
end

RETURN 0

What I have tried:

i tried counting all the variables in my stored procedure and even checked spelling but still i am facing the same problem.

Posted 10-Jun-19 1:33am

MukulMohal

Updated 10-Jun-19 19:00pm

#realJSOP

v3

Add a Solution

Comments

Richard MacCutchan 10-Jun-19 7:57am

Where is the code for save_sale_Master? Also, please use <pre> tags around your code to make it more readable.

MukulMohal 10-Jun-19 8:59am

added my stored procedure to the original question

Richard MacCutchan 10-Jun-19 10:06am

I notice a few potential issues with your parameters:

Invoice_Date is declared as a DATE but you are passing Text.
	@Invoice_Date DATE, 
        cmd.Parameters.AddWithValue("@Invoice_Date", invoicedate.Text);

Total_Amount is declared as integer but you are passing a double value.
	@Total_Amount int,
        cmd.Parameters.AddWithValue("@Total_Amount", Convert.ToDouble(TOTAL.Text));

The following decimal values are passed as doubles; not sur if that is correct
	@Total_Discount decimal(9,2),  
        cmd.Parameters.AddWithValue("@Total_Discount", Convert.ToDouble(DISCOUNTAMOUNT1.Text == "" ? "0" : DISCOUNTAMOUNT1.Text));
	@Fright_Charges decimal(9,2), 
        cmd.Parameters.AddWithValue("@Fright_Charges", Convert.ToDouble(FRIGHTCHARGES.Text == "" ? "0" : FRIGHTCHARGES.Text));
	@GST_Amount decimal(9,2), 
        cmd.Parameters.AddWithValue("@GST_Amount", Convert.ToDouble(GSTAMOUNT.Text));
	@Total_BillToBePaid decimal(9,2), 
        cmd.Parameters.AddWithValue("@Total_BillToBePaid", Convert.ToDouble(GRANDTOTAL.Text));

RoundingOff is declared as integer but you are passing a double value.
	@RoundingOff int,
        cmd.Parameters.AddWithValue("@RoundingOff", Convert.ToDouble(GRANDTOTAL.Text == "" ? "0" : GRANDTOTAL.Text));

@grnno is declared as integer but you are passing text.
	@grnno int,
        cmd.Parameters.AddWithValue("@grnno", grn.Text);

MukulMohal 11-Jun-19 0:58am

thanks Richard MacCutchan for pointing out the all these points and i was really unaware of these facts. I did improved my all points

And i was able to find out the way to resolve this issue

there was just the need to clear the command parameter.

MukulMohal 10-Jun-19 9:45am

in this there is one more code block in which one more stored procedure is used and i am passing a parameter to that stored procedure and stored procedure save_sale_Master is including even that parameter also. Just for that reason i am getting this error. for that can you please help me how i can do it

2 solutions

Solution 1

My guess is that your parameter list doesn't match what the stored proc save_sale_Master is expecting. Judging by your code, it's gonna difficult to determine where your problem is, and even how you're goignt o go about fixing it.

My suggestion is to re-evaluate the stored proc to see if you may need to create two discrete stored procs, or create two c# methods to create the appropriate parameter list.

EDIT =================

I'd like to add that your stored proc is - well - crap.

You SHOULD have written it this way:

SQL

UPDATE table
SET blah = @blah
    ...
WHERE acc_no = @acc_no
and   invoice_no = @invoice_no

if (@@ROWCOUNT = 0)
BEGIN
    INSERT INTO table
    ( 
        -- put your column names here
    )
    VALUES
    (
        -- put your parameter names here
    )
END

Doing it this way would eliminate the need for the @CALLTYPE parameter

Posted 10-Jun-19 2:22am

#realJSOP

Updated 10-Jun-19 4:02am

v3

Comments

MukulMohal 10-Jun-19 9:45am

in this there is one more code block in which one more stored procedure is used and i am passing a parameter to that stored procedure and stored procedure save_sale_Master is including even that parameter also. Just for that reason i am getting this error. for that can you please help me how i can do it

#realJSOP 10-Jun-19 10:09am

In your stored proc, try enclosing the parameter list with parenthesis:

ALTER PROCEDURE [dbo].[save_sale_Master](    ... parameters)ASBEGIN   ...END

I use parenthesis even if I only have one parameter.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MukulMohal · Accepted Answer · 2019-06-10T19:00:00

Solution 2

Solution to this is i need to clear the command parameter before calling next parameter. just by clearing the parameter my code is working fine.

Posted 10-Jun-19 19:00pm

MukulMohal

Comments

[no name] 18-Jun-19 8:21am

yeah, solution works good