How to store multiple rows in single button click using stored procedure

Question

In the database I created a stored procedure

ALTER procedure [dbo].[usercusdet_pro](@user varchar(25),@cusname varchar(max))--,@cnt int)
as
begin
    --declare @count int
    --set @count=0
    --if(@count<@cnt)
    insert usercusdet values(@user,@cusname)
end

to insert values. When I click the button, multiple rows should be inserted in the table.

int cnt = gvTranferRows.Rows.Count;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["gdb"].ConnectionString);

con.Open();

SqlCommand cmd = new SqlCommand("usercusdet_pro", con);
cmd.CommandType = CommandType.StoredProcedure;

if (con.State == ConnectionState.Closed)
    con.Open();

for (int i=0;i<cnt;i++)
{
     cmd.Parameters.Add("@user", SqlDbType.VarChar).Value = "A001";
     cmd.Parameters.AddWithValue("@cusname",gvTranferRows.Rows[i].Cells[0].Text);
     //cmd.Parameters.AddWithValue("@cnt", cnt);
     cmd.ExecuteNonQuery();
}

When I try to add value it shows an error:

procedure or function has too many arguments specified

What's the cause of this error?

James · Accepted Answer · 2013-09-18T11:54:13.580

0

You need to clear the parameters before the next iteration i.e.

cmd.Parameters.Add("@user", SqlDbType.VarChar).Value = "A001";
cmd.Parameters.AddWithValue("@cusname",gvTranferRows.Rows[i].Cells[0].Text);
//cmd.Parameters.AddWithValue("@cnt", cnt);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();

Or given the @user parameter is fixed, you just need to replace the @cusname one

cmd.Parameters.Add("@user", SqlDbType.VarChar).Value = "A001";
for (int i=0;i<cnt;i++)
{
    cmd.Parameters.AddWithValue("@cusname",gvTranferRows.Rows[i].Cells[0].Text);
    cmd.ExecuteNonQuery();
    cmd.Parameters.RemoveAt("@cusname");
}

edited Sep 18 '13 at 11:54

answered Sep 18 '13 at 11:48

James

80,725
18
167
237

hai james it worked.thanks.one more doubt where insert into table it stored in the random order. it is not inserting one by one like as ususal – Gopi Sep 18 '13 at 12:01

score 0 · Answer 2 · edited May 23 '17 at 12:28

0

You can use Table valued parameter starting sql server 2008 onwards.

OR

Youc an go for passing data as XML if using version prior to SQL Server 2008.

OR

Not a good apporach but you can use delimiter seperated string as well.

edited May 23 '17 at 12:28

Community

1
1

answered Sep 18 '13 at 11:52

Shantanu Gupta

20,688
54
182
286

How to store multiple rows in single button click using stored procedure

2 Answers2