1) What's the syntax for a Update query for a table without a primary key in vb.net for a gridview with a checkbox?
Disclaimer: Frustratingly, adding a primary key is not an option. My program is a small program in a much larger system with poor data management. My development time does not include rewriting the other software.
Here are the Columns for the table Where AgentLeads is the database and MktDtaLeads_Scrubbed is 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]
The code I have right now doesn't display any errors but doesn't update the DoNotMail field when you check the checkbox even though it does display the text "The DoNotMail value has been changed in the database for the selected field".
For the default.aspx.vb code behind I added:
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
WHERE [last name]=@lastname.selectedrow AND [first name]=@firstname.selectedrow AND [Address Line 1]=@Address Line 1.selectedrow"
Else
donotmail.SelectedValue = 0
.ConnectionString = ConfigurationManager.AppSettings("AgentLeadsConnectionString").ToString
.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"
End If
End With
End If
End Sub
Here's the code 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="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>
</Columns>
2) 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...
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>