VB.net - 使数据库的GridView复选框字段更新布尔字段 [英] VB.net - make Gridview checkbox field 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..)

我在vb.net工作创造一个asp.net web应用程序。我有一个SqlDataSource和我的网页上的GridView。

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

我要改变由一个GridView复选框psented的DoNotMail布尔值重新$ P $，并自动更新数据库中，如果该复选框从0（假，威尔邮件）检查为1（True，不会邮件）在这里是code口使用。

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.

有关加入我身后的default.aspx.vb code：

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

有关Default.aspx页面我添加：

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）我是pretty确保我上的更新命令语法是不正确的默认code-后面部分。有谁知道正确的语法？结果
2）我得到的错误：当我后面加了一节code。在拉手CheckBox1.CheckedChangedCheckBox1加下划线，并得到了以下错误：Handles子句需要包含类型或一个定义的变量WithEvents就其基本类型。我该如何摆脱呢？我刚刚删除的行并运行code离不开它。
3）在点击复选框的DoNotMail值已经在数据库中更改选定字段的文本是在页面的顶部显示的，但如果我重新运行donotmail = 1的搜索，记录不显示监守起来这是从来没有更新W上的新价值。

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.

我难住了pretty。任何人都可以帮忙吗？这将是非常美联社preciated：）

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

感谢您的答复尼克！ UPDATE语句将不起作用B / C它没有WHERE子句。因为这是它会更新整个表。有针对此表的主键。下面是列在表中从[AgentLeads] [DBO] [MktDtaLeads_Scrubbed] - [姓氏]，[名]，[中名]，[后缀]，[地址1]，[地址2] [城市]，[ST]，[邮编code]，[电子邮件地址]，[电话树木包]，[免费电话树木包]，[InsertDate]，[SentDate]，[DoNotMail]

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]

在哪里AgentLeads是数据库和MktDtaLeads_Scrubbed是表。如何指定行？所以，我会放：

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设置donotmail = @ donotmailWHERE [姓氏] = @ lastname.selectedrow和[名字] = @ firstname.selectedrow和[地址1] = @地址线1.selectedrow

.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

是有可能做一个双向同步对整个gridview的当用户点击一个按钮，这样你就不必在每次行发生改变的时候做一个更新？因为用户可能会选中该复选框，然后选中另一个框然后取消一个盒子，这将是一个很大的更新...

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...

推荐答案

这里的code为GridView上的Default.aspx：

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

我用code此页面上详细的变化，并得到它的工作！ <一href=\"http://stackoverflow.com/questions/4239394/vb-net-sql-query-works-in-sql-server-but-not-when-called-from-checkbox\">vb.net SQL查询工作在SQL服务器而不是从复选框叫

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

有关加入我身后的default.aspx.vb code：

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

有关我用同样的code GridView的：

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>

这篇关于VB.net - 使数据库的GridView复选框字段更新布尔字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！