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

Question

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.

I want 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()
    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)
    With Me.SqlDataSource1
        Dim box As CheckBox = DirectCast(sender, CheckBox)
        If box.Checked = True Then
            donotmail.SelectedValue = 1

            .ConnectionString = ConfigurationManager.AppSettings("AgentLeadsConnectionString").ToString
            .UpdateCommand = "UPDATE MktDataLeads_scrubbed set donotmail=@donotmail"
        Else
            donotmail.SelectedValue = 0

            .ConnectionString = ConfigurationManager.AppSettings("AgentLeadsConnectionString").ToString
            .UpdateCommand = "UPDATE MktDataLeads_scrubbed set donotmail=@donotmail"
        End If
    End With

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> 

1) I'm pretty sure my syntax on the update command isn't right in the default code-behind section. Does anyone know the correct syntax?
2) I get the 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. 3) Upon clicking the checkbox, "The DoNotMail value has been changed in the database for the selected field" text is displayed at the top of the page but if i re-run the search for donotmail=1, the record doesn't show up becuase it was never updated w the new value.

I'm pretty stumped. Can anyone help? It would be very much appreciated :)

Thanks for the Response Nick! The UPDATE statement won't work b/c it has no WHERE clause. It will update the entire table as it is. There's no primary key for the table. Here are the columns for the table FROM [AgentLeads].[dbo].[MktDtaLeads_Scrubbed] - [Last Name] ,[First Name] ,[Middle Name] ,[Suffix] ,[Address Line 1] ,[Address Line 2] ,[City] ,[ST] ,[ZipCode] ,[Email Address] ,[Phone Nbr] ,[Toll Free Nbr] ,[InsertDate] ,[SentDate] ,[DoNotMail]

Where AgentLeads is the database and MktDtaLeads_Scrubbed is the table. How do I specify the row? So would i put :

.UpdateCommand = "UPDATE MktDataLeads_scrubbed set donotmail=@donotmail" WHERE [last name]=@lastname.selectedrow AND [first name]=@firstname.selectedrow AND [Address Line 1]=@Address Line 1.selectedrow

is it possible to do a two way sync on the entire gridview when the user hits a button so you don't have to do an update every time a row is changed? because the user might check the box and then check another box then uncheck a box and it would be a lot of updates...

Answer 1

I ended up getting permission to add a primary key column to the database by the name of ID to call each record more easily and optimizing the code by calling a stored procedure from the db (faster) instead of running the SQL commands from the Code Behind (slower).

For the default.aspx.vb I put:

Public Sub checkbox_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs) 'Handles checkbox.CheckedChanged 
    With Me.SqlDataSource1
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("AgentLeadsConnectionString").ConnectionString


        .UpdateParameters.Clear()

        .ConnectionString = ConfigurationManager.AppSettings("AgentLeadsConnectionString").ToString
        .UpdateCommand = "up_UpdateMktDataLeads"
        .UpdateParameters.Add("donotmail", Me.donotmail.Text)
        .UpdateCommandType = SqlDataSourceCommandType.StoredProcedure

    End With

    GridView2.DataBind()

End Sub

Here's the stored procedure the code behind called:

     PROCEDURE [dbo].[up_UpdateMktLeadsDoNotMail]

           @ID integer, 
           @DoNotMail bit

     AS

     UPDATE [dbo].[MktDtaLeads_Scrubbed]

     SET [DoNotMail] = @DoNotMail

     WHERE [ID] = @ID

Here's the front end coding for the GridView on default.aspx:

        <asp:GridView ID="GridView2" runat="server" CellPadding="2" 
            DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None" 
            AutoGenerateColumns="False">
            <Columns>
                <asp:BoundField DataField="Last Name" HeaderText="Last Name" 
                    SortExpression="Last Name" />
                <asp:BoundField DataField="First Name" HeaderText="First Name" 
                    SortExpression="First Name" />
                <asp:BoundField DataField="Address Line 1" HeaderText="Addr 1" 
                    SortExpression="Address Line 1" />
                <asp:BoundField DataField="Address Line 2" HeaderText="Addr 2" 
                    SortExpression="Address Line 2" />
                <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
                <asp:BoundField DataField="ST" HeaderText="ST" SortExpression="ST" />
                <asp:BoundField DataField="ZipCode" HeaderText="ZipCode" 
                    SortExpression="ZipCode" />
                <asp:BoundField DataField="Email Address" HeaderText="Email Addr" 
                    SortExpression="Email Address" />
                <asp:BoundField DataField="Phone Nbr" HeaderText="Phone Nbr" 
                    SortExpression="Phone Nbr" />

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

            </Columns>
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <RowStyle BackColor="#EFF3FB" Font-Size="Smaller" />
            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#2461BF" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>

Answer 2

Here's the code for the GridView on default.aspx:

I used a variation of the code detailed on this page and got it to work! vb.net SQL query works in SQL server but not when called from checkbox

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

    Public Sub checkbox_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs) 'Handles checkbox.CheckedChanged
    Dim connectionString As String = ConfigurationManager.ConnectionStrings("AgentLeadsConnectionString").ConnectionString


    Dim box As CheckBox = DirectCast(sender, CheckBox)
    Dim tblcell As TableCell = CType(box.Parent, TableCell)
    Dim dgRow As GridViewRow = CType(tblcell.Parent, GridViewRow)

    Dim lastname As String = [last name].Rows(dgRow.DataItemIndex).Cells(0).Text
    Dim firstname As String = [first name].Rows(dgRow.DataItemIndex).Cells(0).Text
    Dim address As String = [Address Line1].Rows(dgRow.DataItemIndex).Cells(0).Text

    Dim insertSQL As String

    If box.Checked = True Then
        insertSQL = "UPDATE MktDataLeads_scrubbed "
        insertSQL &= "SET donotmail=1 "
        insertSQL &= "WHERE [last name]= @lastname AND [first name]=@firstname AND [Address Line1]=@address "
    Else
        insertSQL = "UPDATE MktDataLeads_scrubbed "
        insertSQL &= "SET donotmail=0 "
        insertSQL &= "WHERE [last name]= @lastname AND [first name]=@firstname AND [Address Line1]=@address "
    End If

    Using con As New SqlConnection(connectionString)
        Dim cmd As New SqlCommand(insertSQL, con)
        cmd.Parameters.AddWithValue("@donotmail", donotmail)
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch Err As SqlException
            MsgBox("Error", 65584, "Insertion Error")
        End Try
        con.Close()
    End Using

End Sub

For the gridview I used the same code:

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

Answer 3

You'll want to use the GridView.RowCommand event

Make sure to add the OnRowCommand listener to the GridView then update accordingly

Public Sub gridview1_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs)
     If e.CommandName = "UpdateDoNotMail" Then
        With Me.SqlDataSource1
           Dim box As CheckBox = DirectCast(sender, CheckBox)
           If box.Checked = True Then
               donotmail.SelectedValue = 1

               .ConnectionString = ConfigurationManager.AppSettings("AgentLeadsConnectionString").ToString
               .UpdateCommand = "UPDATE MktDataLeads_scrubbed set donotmail=@donotmail"
           Else
               donotmail.SelectedValue = 0

               .ConnectionString = ConfigurationManager.AppSettings("AgentLeadsConnectionString").ToString
               .UpdateCommand = "UPDATE MktDataLeads_scrubbed set donotmail=@donotmail"
           End If
       End With
    End If
End Sub

and the TemplateField

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