vb.net SQL query works in SQL server but not when called from checkbox

Question

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.

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.

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

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.

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>

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

Klaus Byskov Pedersen · Accepted Answer · 2010-11-21 18:14:00Z

1

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

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.

answered Nov 21, 2010 at 18:14

Klaus Byskov Pedersen

122k31 gold badges192 silver badges223 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

melat0nin Over a year ago

Klaus - many thanks, that worked to get the SQL running. Trouble is, I have to click the checkboxes twice to get the change to 'stick'... EDIT: i just had the logic the wrong way round... noob mistake :)

Collectives™ on Stack Overflow

vb.net SQL query works in SQL server but not when called from checkbox

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related