VB.net - make Gridview checkbox update boolean field in database

There are lots of questions about this but I've not been able to solve my problem using the answers to any of them (after many, many attempts..)

I'm working in vb.net creating an asp.net web application. I have an SqlDataSource and a GridView on my page:

<asp:SqlDataSource ID="msgUnread" runat="server" 
 ConnectionString="<%$ ConnectionStrings:edinsec %>" 

 SelectCommand="SELECT [msgdate], [email], [name], [message], [readit] FROM [messages]" 
 UpdateCommand="UPDATE messages SET readit = 'True' WHERE (msgid = @msgid)">
       <UpdateParameters>
     <asp:Parameter Name="msgid" />
 </UpdateParameters>
   </asp:SqlDataSource>


   <asp:GridView ID="unreadMessages" runat="server" AutoGenerateColumns="False" 
 BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" 
 CellPadding="3" DataSourceID="msgUnread">
 <RowStyle ForeColor="#000066" />
 <Columns>
     <asp:BoundField DataField="msgdate" HeaderText="Date &amp; time" 
         SortExpression="msgdate" />
     <asp:BoundField DataField="email" HeaderText="Email" 
         SortExpression="email" />
     <asp:BoundField DataField="name" HeaderText="Name" SortExpression="name" />
                 <asp:TemplateField HeaderText="Mark as read" SortExpression="readit">
         <%--<EditItemTemplate>
             <asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("readit") %>'  OnCheckedChange="CheckBox1_CheckedChanged"  />
         </EditItemTemplate>--%>
         <ItemTemplate>
             <asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("readit") %>' OnCheckChanged="CheckBox1_CheckedChanged" AutoPostBack="true"/>
         </ItemTemplate>
     </asp:TemplateField>
 </Columns>
 <FooterStyle BackColor="White" ForeColor="#000066" />
 <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
 <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
 <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
   </asp:GridView>

The GridView is populated properly, with the checkboxes' values being properly displayed (the 'readit' field is a bit field, i.e. boolean). I'm trying to get the script to update the boolean value in the database when the checkbox is clicked. At the moment though I can't even get the script to react to a click (not even a MsgBox).

Here's my CodeBehind:

Public Partial Class enqur
Inherits System.Web.UI.Page
WithEvents CheckBox1 As CheckBox


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

End Sub

Public Sub CheckBox1_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs)
    MsgBox("test")
End Sub

End Class

As you can see I was playing with WithEvents but that didn't seem to help. In the above code all I was trying to get was some kind of reaction to the clicking of a checkbox - but nothing happens (no errors, either).

I'm pretty stumped. Can anyone help? Would b开发者_开发技巧e much appreciated :)

---

After much faffing about I ended up with this code in the aspx file:

<asp:TemplateField HeaderText="readit" SortExpression="readit">
    <ItemTemplate>
        <asp:CheckBox ID="CheckBox1" runat="server" AutoPostBack="true" OnCheckedChanged="checkbox_CheckedChanged" Checked='<%# Bind("readit") %>' 
            Enabled="true" />
    </ItemTemplate>
    <EditItemTemplate>
        <asp:CheckBox ID="CheckBox1" runat="server" AutoPostBack="true" OnCheckedChanged="checkbox_CheckedChanged" Checked='<%# Bind("readit") %>' />
    </EditItemTemplate>
      </asp:TemplateField>

...and this in the CodeBehind:

Public Sub checkbox_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs) 'Handles checkbox.CheckedChanged
    Dim box As CheckBox = DirectCast(sender, CheckBox)



    If box.Checked = True Then
  MsgBox("checked!")
    Else
  MsgBox("unchecked!")
    End If



End Sub

...which fires properly on the clicking of each checkbox. Now to make them do something useful...

---

Change

Public Sub CheckBox1_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs)

to

Public Sub CheckBox1_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs) Handles CheckBox1.CheckedChange

Basically you forgot the Handles CheckBox1.CheckedChange at the end.

---

Error: When I add "Handles CheckBox1.CheckedChanged" under the code behind section, CheckBox1 is underlined and gets the following error: "Handles clause requires a WithEvents variable defined in the containing type or one of its base types". How do I get rid of that? I just deleted the line and ran the code without it.

To change the DoNotMail boolean value represented by a Gridview checkbox and automatically update in the database if the checkbox is checked from 0 (False, Will Mail) to 1 (True, Won't Mail) here is the code I used.

For the default.aspx.vb code behind I added:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    lastname.Focus() //sets the focus on the last name text box
    If Page.IsPostBack Then
        Response.Write("The DoNotMail value has been changed in the database for the selected field")
    End If

End Sub

Public Sub checkbox1_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs)
    Dim box As CheckBox = DirectCast(sender, CheckBox)
    If box.Checked = True Then
        donotmail.SelectedValue = 1
    Else
        donotmail.SelectedValue = 0
    End If


End Sub

For the default.aspx page I added:

                 <asp:TemplateField HeaderText="DoNotMail" SortExpression="DoNotMail">     
                 <ItemTemplate>         
                 <asp:CheckBox ID="CheckBox1" runat="server" AutoPostBack="true" OnCheckedChanged="checkbox1_CheckedChanged" Checked='<%# Bind("DoNotMail") %>'
                               Enabled="true" />     
                 </ItemTemplate>     
                 <EditItemTemplate>         
                 <asp:CheckBox ID="CheckBox1" runat="server" AutoPostBack="true" OnCheckedChanged="checkbox1_CheckedChanged" Checked='<%# Bind("DoNotMail") %>' />     
                 </EditItemTemplate>       
                 </asp:TemplateField>