You must Sign In to post a response.
  • Category: .NET

    Procedure or function has too many arguments specified

    I have stored procedure like following,

    ALTER PROCEDURE [dbo].[Proc_insertdetails]
    @patient_id int ,
    @prod_name VARCHAR (50),
    @quantity float ,
    @batchno VARCHAR (30),
    @mrp float,
    @sale_price float,
    @expiry_date datetime,
    @bill_rate float

    AS
    BEGIN

    INSERT into Patient_mstdtl (patient_id,prod_id, quantity, batchno, mrp,
    purchase_price, sale_price, sale_price2, bill_rate, Scan_code,
    global_trade_item_no, stocksum_id, expiry_date)

    SELECT @patient_id ,pm.prod_id, @quantity, @batchno, @mrp, sk.purchase_price, @sale_price,
    sk.sale_price2, @bill_rate, sk.scan_code,
    pm.global_trade_item_no, sk.stocksum_id, @expiry_date

    from Product_mst pm ,stocksummary sk ,Patient_mst ptm

    where ptm.patient_id = @patient_id and
    pm.prod_name = @prod_name and
    sk.batchno =@batchno and
    sk.mrp = @mrp and
    sk.sale_price = @sale_price and
    sk.expiry_date = @expiry_date
    END


    and I called this procedure in program like following,

    cmd = new SqlCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "Proc_insertdetails";
    cmd.Connection = con;


    string[] splittedResult = r.Split('+');

    for (int x = 0; x <= splittedResult.Length-2; x++)
    {

    string[] myval = splittedResult[x].Split('<');
    string patientId = myval[0].TrimEnd(',').ToString();

    for (int y = 1; y <= myval.Length-1; y++)
    {
    string[] resValue = myval[y].Split(',');

    cmd.Parameters.Add("@patient_id",SqlDbType.Int).Value= patientId;
    cmd.Parameters.Add("@prod_name",SqlDbType.VarChar,50).Value= resValue[0];
    cmd.Parameters.Add("@quantity",SqlDbType.Float).Value= resValue[1];
    cmd.Parameters.Add("@batchno",SqlDbType.VarChar,50).Value= resValue[2];
    cmd.Parameters.Add("@mrp",SqlDbType.Float).Value= resValue[3];
    cmd.Parameters.Add("@sale_price",SqlDbType.Float).Value= resValue[4];
    cmd.Parameters.Add("@expiry_date",SqlDbType.DateTime).Value= resValue[5];
    cmd.Parameters.Add("@bill_rate",SqlDbType.Float).Value= resValue[6];
    }
    table = new DataTable();
    adapter = new SqlDataAdapter(cmd);
    adapter.Fill(table);
    Form1.message("data inserted");

    }
    }

    There is no problem or datatypes or value of parameters supplide then why this code throws this exception.

    Help me! Thank you!
  • #761674
    Hi,

    Your code looking good, I request you to please put a break point and check it line by line, and find in which line it's throwing this exception.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #761675
    Hello Priyanka,

    Update this line :

    cmd.Parameters.Add("@batchno",SqlDbType.VarChar,30).Value= resValue[2];

    because while declaring @batch no you have defined the size is 30 chars.

    and did you try to execute this stored procedure in sql..??

    You can use this also :

    cmd.Parameters.AddWithValue("@patient_id",patientId);

    One more thing where are you calling "cmd.ExecuteNonQuery()"

    I think you should check all this things first.

    Use try.. catch also to check where you are getting an error.

    try
    {
    // Your code goes here
    }
    catch(Exception ex)
    {
    MessageBox.Show(ex.StackTrace);
    }

    Hope this will help you.
    Let me know if you still facing an issues.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

  • #761678
    Hi

    I think your database columns are not matching with the values you are passing into them while executing the procedure.
    Once check in this line with database columns.

    INSERT into Patient_mstdtl (patient_id,prod_id, quantity, batchno, mrp,
    purchase_price, sale_price, sale_price2, bill_rate, Scan_code,
    global_trade_item_no, stocksum_id, expiry_date)

    Regards

    Sridhar Thota.
    If you learn't from defeat..
    You haven't really lost..

    Sridhar Thota.
    Editor: DNS Forum.

  • #761682
    Hai Priyanka,
    Did you check that there is no autogeneratecolumns to insert the records automatically and the values are corresponding to the parameter and the number of parameters and order of the parameters are same.
    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #761695
    hi

    where is r variable declare in your code?

    can you share proper code then we can easily find the solution to your issue fastly.

    string[] splittedResult = r.Split('+');

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #761700
    I checked the code in debug and now it works too but it inserts only first record when second record trying to insert into table it shows above error

  • #761713
    I think you have the issue in your for look logic. Check that.

    Keep Following inside the for loop so that you can solve current issue


    table = new DataTable();
    adapter = new SqlDataAdapter(cmd);
    adapter.Fill(table);
    Form1.message("data inserted");

    for (int y = 1; y <= myval.Length-1; y++)
    {
    string[] resValue = myval[y].Split(',');

    cmd.Parameters.Add("@patient_id",SqlDbType.Int).Value= patientId;
    cmd.Parameters.Add("@prod_name",SqlDbType.VarChar,50).Value= resValue[0];
    cmd.Parameters.Add("@quantity",SqlDbType.Float).Value= resValue[1];
    cmd.Parameters.Add("@batchno",SqlDbType.VarChar,50).Value= resValue[2];
    cmd.Parameters.Add("@mrp",SqlDbType.Float).Value= resValue[3];
    cmd.Parameters.Add("@sale_price",SqlDbType.Float).Value= resValue[4];
    cmd.Parameters.Add("@expiry_date",SqlDbType.DateTime).Value= resValue[5];
    cmd.Parameters.Add("@bill_rate",SqlDbType.Float).Value= resValue[6];

    table = new DataTable();
    adapter = new SqlDataAdapter(cmd);
    adapter.Fill(table);
    Form1.message("data inserted");

    }

    By Nathan
    Direction is important than speed

  • #761719
    I solved it by putting following lines in first for loop

    cmd = new SqlCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "Proc_insertdetails";
    cmd.Connection = con;

    actually my string has more than 1 record so at time of inserting 2nd record it doesnt have adapter object to store thats why it throws error but now i solved issue and its working too.
    Thanks for quick help!

  • #761721
    Error text only resolve your problem, you have provide more columns than actually need by SQL table. basically your provided number of columns and insertion values are not matched properly
    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #761728
    hi
    i think the issue is because you are assigning the value in for loop. so when the loop executes more than one, there can be issue. check this also

    Do Good... Enjoy your life.....

  • #762562
    Hi

    Inserting values and the database columns are not same. Then only you will get that error message.
    I think you are trying to assign more parameters.

    Regards

    Sai Krishna Reddy.


Sign In to post your comments