cmd.ExecuteNonQuery() is not allowed

Question

In my webmethod, i am updating the database. but while debugging the cursor escapes on cmd.ExecuteNonQuery(). my code is,

        If tbl = "All" Then
            cmd = New SqlCommand("update setbool set pos_val='False',valid_rp='False',Pos_save='False'", conn)
            cmd.ExecuteNonQuery()
            Return Nothing
        Else
            cmd = New SqlCommand("update setbool set " & tbl & "='True'", conn)
            cmd.ExecuteNonQuery()
            Return Nothing
        End If

Any suggestion?

Why are you duplicating code in the `If` and `Else` blocks? Create the command object first, set the `CommandText` in the `If` and `Else` blocks because that's the only bit that's different, then call `ExecuteNonQuery` and `Return` afterwards. — jmcilhinney, Apr 23 '14 at 04:29
As for your issue, we would need to know what the error message is to know for sure but I'm guessing that either your column name is causing a syntax error or your values are causing a data type mismatch. If those are `bit` columns then you need to use values 1 and 0, not text containing "True" and "False". — jmcilhinney, Apr 23 '14 at 04:29
whenever i debug, the debugging point escape at cmd.Executenonquery which means database is not getting updated — King of kings, Apr 23 '14 at 04:40

score 0 · Accepted Answer · answered Apr 23 '14 at 04:49

I found my own solution.

instead of

        cmd = New SqlCommand("update setbool set pos_val='False',valid_rp='False',Pos_save='False'", conn)
        cmd.ExecuteNonQuery()
        Return Nothing

i changed

            Using cmd1 As New SqlCommand("update setbool set pos_val='False',valid_rp='False',Pos_save='False'")
                cmd1.CommandType = CommandType.Text
                cmd1.Connection = conn
                conn.Open()
                cmd1.ExecuteNonQuery()
                conn.Close()
            End Using
            Return Nothing

cmd.ExecuteNonQuery() is not allowed

1 Answers1