vb.net SQL查询工作在SQL服务器而不是从复选框调用时 [英] vb.net SQL query works in SQL server but not when called from checkbox

问题描述

我已经得到了与复选框一个DataGrid它调用了一个名为checkbox_CheckedChanged程序。到现在为止还挺好。我已经成功地得到它的工作出的数据视图的另一列，这让我确定我处理与行的id的值。

I've got a datagrid with checkboxes which calls a routine called checkbox_CheckedChanged. So far, so good. I've managed to get it to work out the value of another column in the dataview, which allows me to ascertain the id of the row I'm dealing with.

我试图让它改变定义复选框的初始值的列的值，但我写的SQL时vb.net叫不工作 - 它的工作手动输入时到SQL Server，但是。

I'm trying to get it to change the value of the column which defines the initial value of the checkboxes, but the SQL I've written doesn't work when called by vb.net - it does work when entered manually into SQL server, however.

下面是我的code背后：

Here's my code behind:

Public Sub checkbox_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs) 'Handles checkbox.CheckedChanged
    Dim connectionString As String = WebConfigurationManager.ConnectionStrings("edinsec").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 msgId As Integer = unreadMessages.Rows(dgRow.DataItemIndex).Cells(0).Text

    Dim insertSQL As String

    If box.Checked = True Then
  insertSQL = "UPDATE messages"
  insertSQL &= "SET readit = 0"
  insertSQL &= "WHERE msgid = @msgId"
    Else
  insertSQL = "UPDATE messages"
  insertSQL &= "SET readit = 1"
  insertSQL &= "WHERE msgid = @msgId"
    End If

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

End Sub

的想法是，点击后，该复选框将在数据库中它的反面翻转readit的价值。它保持跳跃为SQLException但是，所以我看到的错误消息。

The idea is that when clicked, the checkbox will flip the 'readit' value in the database to its opposite. It keeps jumping to the SqlException however, so I'm seeing the error message.

在ASPX code的复选框是这样的：

The aspx code for the checkboxes is this:

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

任何帮助将是非常美联社preciated。 Visual Studio中（2008年）给出的零的在这种情况下，它是pretty真气在SQL错误反馈。

Any help would be much appreciated. Visual Studio (2008) gives zero feedback on SQL errors in this situation, which is pretty infuriating.

推荐答案

您需要在您的更新语句添加一些空间：

You need to add some spaces in your update statement:

   If box.Checked = True Then
      insertSQL = "UPDATE messages "
      insertSQL &= "SET readit = 0 "
      insertSQL &= "WHERE msgid = @msgId"
   Else
      insertSQL = "UPDATE messages "
      insertSQL &= "SET readit = 1 "
      insertSQL &= "WHERE msgid = @msgId"
   End If

的消息后，我已经添加空间的通知之后 readit = 0/1 。否则，你的声明将是更新messagesSET ... ，它并不在SSMS工作或者。

Notice that I have added spaces after messages and after readit = 0/1. Otherwise your statement would be UPDATE messagesSET ... which does not work in SSMS either.

这篇关于vb.net SQL查询工作在SQL服务器而不是从复选框调用时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！