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-18 11:54:13Z

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, 2013 at 11:54

answered Sep 18, 2013 at 11:48

James

82.4k19 gold badges172 silver badges245 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gopi Over a year ago

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

Community · Accepted Answer · 2017-05-23 12:28:03Z

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, 2017 at 12:28

CommunityBot

11 silver badge

answered Sep 18, 2013 at 11:52

Shantanu Gupta

21.2k56 gold badges187 silver badges294 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related