DataGrid and CheckBox Controls


Question:  

How to have checkbox that is checked or unchecked depending on a field value being in the Database, and then allow the user to click on it and record the new checkbox state in the database record for that row.

Solution:

webform1.aspx

<asp:datagrid id="DataGrid1" style="Z-INDEX: 101; LEFT: 9px; POSITION: absolute; TOP: 15px" runat="server" AutoGenerateColumns="False"
DataKeyField="Productid">
<Columns>
<
asp:TemplateColumn HeaderText="ProductName">
<
ItemTemplate>
<
asp:CheckBox id=chk runat="server" Text =<%# DataBinder.Eval(Container.DataItem, "ProductName") %> checked='<%# DataBinder.Eval(Container.DataItem, "Discontinued") %>'></asp:CheckBox>
</
ItemTemplate>
</
asp:TemplateColumn>
<
asp:ButtonColumn ButtonType="PushButton" CommandName="Update" Text="Update"></asp:ButtonColumn></Columns>
</
asp:datagrid>

webform1.asp.vb

Step 1:Simple Binding with the DataGrid. The Products Table has the Field Discontinued (DataType:bit) Based on the value 0/1 of the Discontinued field uncheck/check the Checkbox.

Dim myconnection As SqlConnection
Dim myda As SqlDataAdapter
Dim mycmd As SqlCommand
Dim ds As DataSet
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
bindData()
End If
End
Sub
Sub
bindData()
myconnection =
New SqlConnection("Server=localhost;uid=sa;password=;database=northwind;")
myda =
New SqlDataAdapter("Select * from Products where productid<4", myconnection)
ds =
New DataSet
myda.Fill(ds, "AllTables")
DataGrid1.DataSource = ds
DataGrid1.DataBind()
End Sub

Step 2
: If the user check's/uncheck's the original value of the Checkbox and clicks the button the new checkbox value should be stored in the Database table.

Private Sub DataGrid1_ItemCommand(ByVal source As Object, _ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.ItemCommand
Dim prodchknew As CheckBox = e.Item.FindControl("chk")
Dim updval As String
If prodchknew.Checked Then
updval = "1"
Else
updval = "0"
End If
If e.CommandName = "Update" Then
myconnection = New SqlConnection("Server=localhost;uid=sa;password=;database=northwind;")
Dim strsql As String = "Update Products set Discontinued='" & updval & "'" & _" where Productid=" & DataGrid1.DataKeys(CInt(e.Item.ItemIndex))
' Response.Write(strsql)
mycmd = New SqlCommand(strsql, myconnection)
myconnection.Open()
mycmd.ExecuteNonQuery()
bindData()
End If
End
Sub