Selecting multiple checkboxes inside a GridView control

[Mark B]

I am following the example at
http://www.codeproject.com/KB/webforms/SelChkboxesDataGridView.aspx.

It works on my GridView.

However I want it to store these ticks as Boolean yes/no's in the underlying
table field "Subscribed". And upon opening the web page the appropriate
ticks should show too according to the values in the table. How do I
bind/retrieve these values?

 

[Mark B]

http://forums.asp.net/t/1193113.aspx

Thanks. I am using Eval which works to read the values in:

<asp:TemplateField HeaderText="Renew"
HeaderStyle-HorizontalAlign="Left">

<ItemTemplate>
<asp:CheckBox ID="chkSelect" runat="server"
Checked='<%# Eval("Renew") %>' />
</ItemTemplate>

<HeaderStyle HorizontalAlign="Left"></HeaderStyle>

</asp:TemplateField>


In order to store the selections in the table, the post you referred to
suggested:

protected void Button1_Click(object sender, EventArgs e)
{

for (int i = 0; i < GrdiView1.Rows.Count; i++)
{
CheckBox cb =
(CheckBox)GridView1.Rows.Cells[0].FindControl("CheckBox1");//Gets the
checkbox in the grid

if (cb.Checked == true)
{
//Do something here when CheckBox is Checked
}
else
{ //Do something here when CheckBox is UnChecked }
}

}




So should I run an UPDATE stored procedure (with a parameter for the IDField
value) where it says //Do something here when CheckBox is Checked?

So if there were 50 checkboxes it would run the query 50 times? Is that
inefficient?

 

[Mark B]

In order to store the selections in the table, the post you referred to
suggested:

protected void Button1_Click(object sender, EventArgs e)
{

for (int i = 0; i < GrdiView1.Rows.Count; i++)
{
CheckBox cb =
(CheckBox)GridView1.Rows.Cells[0].FindControl("CheckBox1");//Gets the
checkbox in the grid

if (cb.Checked == true)
{
//Do something here when CheckBox is Checked
}
else
{ //Do something here when CheckBox is UnChecked }
}

}

So should I run an UPDATE stored procedure (with a parameter for the
IDField value) where it says //Do something here when CheckBox is
Checked?

Yes, if you want the users to be able to update each row independently of
the others...

So if there were 50 checkboxes it would run the query 50 times? Is that
inefficient?

As above. Otherwise, you'll need a separate button which will post back,
walk through the rows and evaluate the state of each checkbox, and then
call a single stored procedure to do the mass update.

I think I need the latter-- Once a user has selected say 35 out of 50, they
click a button outside the GridView which says "Store selected items". I
wonder how I can pass say:

ID, YesNoState

234,True
235, False
236, True
237,True
....
...
etc

to a stored procedure for mass update?

 

[Mark B]

SQL Server doesn't properly support array parameters, though you can look
at XML data types if you want to be really clever. I tend not to bother
with this, as the method below is so much easier.

1) Walk through all of the rows in the GridView and construct a
comma-delimited string of the IDs of those which are checked. e.g.

"234,236,237"

2) Create a stored procedure like below and pass the above string to it:

CREATE PROCEDURE MyUpdateSP
@pstrCheckedIDs varchar(4000)
AS
DECLARE @strSQL nvarchar(4000)
SET @strSQL = 'UPDATE MyTable SET MyBitField = 1 WHERE MyIdField IN(' +
@pstrCheckedIDs + ')'
--PRINT @strSQL
UPDATE MyTable SET MyBitField = 0
EXEC sp_executesql @strSQL

Rem in the PRINT line and rem out the last two lines if you want to see
what the dynamic SQL looks like first for testing purposes.

You will almost certainly want to make the above a bit more robust by
surrounding the two write statements in a transaction. This wouldn't be a
very efficient solution if the table contained many rows and you were
updating only a handful of them, but it demonstrates the technique at
least...

Thanks again.

I've had to add a WHERE clause to include the indexed OrganizationDomain
field else it would be too slow:

SET @strSQL = 'UPDATE tblSubscription SET RenewObtain = 1 WHERE (CounterID
IN(' +
@pstrCheckedIDs + ') AND
OrganizationDomain='+@strCurrentOrganizationDomain+')'


There are however two issues with doing that:

1) It's giving an error because it's using OrganizationDomain=ABCCompany.com
instead of OrganizationDomain='ABCCompany.com'
2) It's subject to SQL Injection attack so I'd need to use a parameter
instead of using the result of the @strCurrentOrganizationDomain parameter
'soft-coded' in there.

Do you know what syntax change I'd need to make to the SET @strSQL statement
to allow for such a parameter?



Also, on another issue, I think I'd need to change:

"234,236,237"

to

"A234,A236,A237"

otherwise the IN function would find a 2 even though a 2 was not in the list
of numbers.

So would I use:

WHERE ('A'+MyIdField) IN(' + @pstrCheckedIDs + ')'

?