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