I've a stored procedure which handles multiple functionalities like checking filetype and inserting the data's to multiple table with foreignkey constraints.In the same SP i handle an Insert which only inserts PK ID's of two tables into Relation table(bridge
table).
My issue here is I've a gridview with checkboxes and when users does multiple selection I need those Id's to be inserted into my table (Bridge table).I'm using the below code.It throws error when I do multiple select saying "too many parametes passed" but
inserts if one is checked.
I don't need to add a new row,I just have list of itmes in my Gridview with checkbox.When User makes multiple selection I need to insert those checked values to my database.
That is because you are adding the same parameter multiple times. It isn't calling 4 inserts for 4 checked, it is calling 1 insert with too many parameters.
Your best bet would be to loop through and call the stored procedure each time for the values you want to insert.
foreach(rows in gridview)
{
//find control of the checkbox
if(checkbox.checked)
{
SqlCommand cmd = new SqlCommand("MyStoredProcedure", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = TextBox1.Text;
cmd.Parameters.Add("@age", SqlDbType.NVarChar).Value = Textbox2.Text;
cmd.param.add("@View", SqlDbType.varchar).Value = row.Cells[2].Text;
cmd.ExecuteNonQuery();
}
}
Then continue with the rest of the method. It may be best to check the return value of each one to make sure each are a success.
If I've helped you, please mark my post as an Answer. Thanks.
My problem here is i don't want the entire form insert based on checkbox checked,It's optional.If checked will insert the form data with checked values otherwise inserts the form data.So i don't want to call the SP inside if checkbox.checked.hope this helps
to understand.otherwise is it a better approach having a seprate stored procedure for checkbox check insert.Like one form handling 2 stored procedure.Please advice.
protected void Button2_Click(object sender, EventArgs e)
{
StringCollection sc = new StringCollection();
string id = string.Empty;
foreach(GridViewRow gr in GridView1.Rows)
{
CheckBox cb = (CheckBox)gr.FindControl("selector"); //find the CheckBox
if (cb != null)
{
if (cb.Checked)
{
id = GridView1.DataKeys[gr.RowIndex].Value.ToString(); // get the id of the field to be deleted
sc.Add(id); // add the id to be deleted in the StringCollection
}
}
}
updateRecords(sc); // call method for delete and pass the StringCollection values
// BindGridView(); // Bind GridView to reflect changes made here
}
protected void updateRecords(StringCollection sc)
{
DateTime now = DateTime.Now;
string constring = ConfigurationManager.ConnectionStrings["helpDesk"].ConnectionString;
SqlConnection assingncon = new SqlConnection(constring);
StringBuilder sb = new StringBuilder(string.Empty);
foreach(string str in sc)
{
const string query = "UPDATE call_detail set assigntoname=@assigntoname,assignTime=@assignTime where callID";
sb.AppendFormat("{0}='{1}'; ",query,str);
}
try
{
assingncon.Open();
SqlCommand Qcmd = new SqlCommand(sb.ToString(), assingncon);
Qcmd.Parameters.AddWithValue("@assigntoname", DropDownList2.SelectedValue);
Qcmd.Parameters.AddWithValue("@assignTime", now);
Qcmd.ExecuteNonQuery();
}
catch{}
finally{assingncon.Close();}
}
i have use StringCollection class to as a container for id of the rows on basis of checkbox checked or not
and then passed it to the function which update the row that is checked in gridview.
You sorry, one thing I forgot. You could have to cmd.Dispose() afterwards.
You may also consider a using(conn = new Connection()) around the commands. This will prevent an open connection from using memory.
So based upon what you said, you want everything in a foreach loop. But you only set the value of the view if the item is true. At least I think that is what you want. Just make sure you have some try..catch blocks in there and dispose of your commands.
Does that help?
If I've helped you, please mark my post as an Answer. Thanks.
Thanks for your response.I'm trying to follow your code but I'm not sure in the below part.Do i have to write update query because I've multiple tables with foreign constraint in my SP.I underatnd that we do a update here but unsure whther I've to update
query. please help
foreach(string str in sc)
{
const string query = "UPDATE call_detail set assigntoname=@assigntoname,assignTime=@assignTime where callID";
sb.AppendFormat("{0}='{1}'; ",query,str);
}
srividhyavat...
Member
92 Points
283 Posts
Help on Gridview checkbox checked
Apr 04, 2012 01:59 PM|LINK
I've a stored procedure which handles multiple functionalities like checking filetype and inserting the data's to multiple table with foreignkey constraints.In the same SP i handle an Insert which only inserts PK ID's of two tables into Relation table(bridge table).
My issue here is I've a gridview with checkboxes and when users does multiple selection I need those Id's to be inserted into my table (Bridge table).I'm using the below code.It throws error when I do multiple select saying "too many parametes passed" but inserts if one is checked.
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); SqlCommand cmd = new SqlCommand("MyStoredProcedure", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = TextBox1.Text; cmd.Parameters.Add("@age", SqlDbType.NVarChar).Value = Textbox2.Text; foreach (GridViewRow gvrow in GridView1.Rows) { string view; CheckBox chk = (CheckBox)gvrow.FindControl("Checkbox1"); if (chk.Checked) { view = Convert.ToString(gvrow.Cells[2].Text); cmd.Parameters.Add("@ViewName", SqlDbType.NVarChar).Value = view; } }Any Suggestion or correction to my code will be much helpfull.Thanks.
cool.asp
Member
545 Points
190 Posts
Re: Help on Gridview checkbox checked
Apr 04, 2012 02:09 PM|LINK
not sure... but do you need to add a new row? Is your code trying to utilize the same row?
srividhyavat...
Member
92 Points
283 Posts
Re: Help on Gridview checkbox checked
Apr 04, 2012 02:19 PM|LINK
I don't need to add a new row,I just have list of itmes in my Gridview with checkbox.When User makes multiple selection I need to insert those checked values to my database.
trekie86
Member
290 Points
76 Posts
Re: Help on Gridview checkbox checked
Apr 04, 2012 03:56 PM|LINK
That is because you are adding the same parameter multiple times. It isn't calling 4 inserts for 4 checked, it is calling 1 insert with too many parameters.
Your best bet would be to loop through and call the stored procedure each time for the values you want to insert.
foreach(rows in gridview) { //find control of the checkbox if(checkbox.checked) { SqlCommand cmd = new SqlCommand("MyStoredProcedure", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = TextBox1.Text; cmd.Parameters.Add("@age", SqlDbType.NVarChar).Value = Textbox2.Text; cmd.param.add("@View", SqlDbType.varchar).Value = row.Cells[2].Text; cmd.ExecuteNonQuery(); } }Then continue with the rest of the method. It may be best to check the return value of each one to make sure each are a success.
srividhyavat...
Member
92 Points
283 Posts
Re: Help on Gridview checkbox checked
Apr 04, 2012 04:32 PM|LINK
My problem here is i don't want the entire form insert based on checkbox checked,It's optional.If checked will insert the form data with checked values otherwise inserts the form data.So i don't want to call the SP inside if checkbox.checked.hope this helps to understand.otherwise is it a better approach having a seprate stored procedure for checkbox check insert.Like one form handling 2 stored procedure.Please advice.
srividhyavat...
Member
92 Points
283 Posts
Re: Help on Gridview checkbox checked
Apr 04, 2012 04:36 PM|LINK
Evem After placing my Sp inside If checkbox.checked condition it throws me error saying
ERROR: Procedure or function InsertResource has too many arguments specified.
parthiv.kuba...
Member
110 Points
196 Posts
Re: Help on Gridview checkbox checked
Apr 04, 2012 04:53 PM|LINK
see the code you will find it helpfull
protected void Button2_Click(object sender, EventArgs e) { StringCollection sc = new StringCollection(); string id = string.Empty; foreach(GridViewRow gr in GridView1.Rows) { CheckBox cb = (CheckBox)gr.FindControl("selector"); //find the CheckBox if (cb != null) { if (cb.Checked) { id = GridView1.DataKeys[gr.RowIndex].Value.ToString(); // get the id of the field to be deleted sc.Add(id); // add the id to be deleted in the StringCollection } } } updateRecords(sc); // call method for delete and pass the StringCollection values // BindGridView(); // Bind GridView to reflect changes made here } protected void updateRecords(StringCollection sc) { DateTime now = DateTime.Now; string constring = ConfigurationManager.ConnectionStrings["helpDesk"].ConnectionString; SqlConnection assingncon = new SqlConnection(constring); StringBuilder sb = new StringBuilder(string.Empty); foreach(string str in sc) { const string query = "UPDATE call_detail set assigntoname=@assigntoname,assignTime=@assignTime where callID"; sb.AppendFormat("{0}='{1}'; ",query,str); } try { assingncon.Open(); SqlCommand Qcmd = new SqlCommand(sb.ToString(), assingncon); Qcmd.Parameters.AddWithValue("@assigntoname", DropDownList2.SelectedValue); Qcmd.Parameters.AddWithValue("@assignTime", now); Qcmd.ExecuteNonQuery(); } catch{} finally{assingncon.Close();} } i have use StringCollection class to as a container for id of the rows on basis of checkbox checked or not and then passed it to the function which update the row that is checked in gridview.trekie86
Member
290 Points
76 Posts
Re: Help on Gridview checkbox checked
Apr 04, 2012 04:56 PM|LINK
You sorry, one thing I forgot. You could have to cmd.Dispose() afterwards.
You may also consider a using(conn = new Connection()) around the commands. This will prevent an open connection from using memory.
So based upon what you said, you want everything in a foreach loop. But you only set the value of the view if the item is true. At least I think that is what you want. Just make sure you have some try..catch blocks in there and dispose of your commands.
Does that help?
srividhyavat...
Member
92 Points
283 Posts
Re: Help on Gridview checkbox checked
Apr 04, 2012 08:18 PM|LINK
More info please.
srividhyavat...
Member
92 Points
283 Posts
Re: Help on Gridview checkbox checked
Apr 04, 2012 08:20 PM|LINK
To
parthiv.kubavat
Thanks for your response.I'm trying to follow your code but I'm not sure in the below part.Do i have to write update query because I've multiple tables with foreign constraint in my SP.I underatnd that we do a update here but unsure whther I've to update query. please help
foreach(string str in sc) { const string query = "UPDATE call_detail set assigntoname=@assigntoname,assignTime=@assignTime where callID"; sb.AppendFormat("{0}='{1}'; ",query,str); }