Reputation: 4784
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][email protected] AND [first name][email protected] 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...
Upvotes: 0
Views: 11481
Reputation: 4784
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>
Upvotes: 0
Reputation: 4784
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>
Upvotes: 0
Reputation: 2354
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>
Upvotes: 1