SQL Server : add parameter boolean from vb net into sql as bit

Question

I have a SQL Server database with a table called tblFeatures with 2 columns Feature (Nvarchar(50)) and Setting (bit). I am trying to set the value of this Setting for each Feature via a check box in VB NET. I have tried using True/False 0/1 but have not had luck doing so using SQL parameters. I can do this fine using a normal SQL Update command:

"UPDATE tblSettings SET [Setting]='" & True & "' WHERE Feature='FSCreateTicket'"

However, I want to do it the proper/safe way with SQL parameters. I have scoured Stack and Google results have not found the proper way to get this done. I either get an error, or no error but the value does not get updated.

Here is what I have am currently trying:

Public Function ChangeSetting(ByVal strFeature As String, ByVal bSetting As Boolean)
    Dim sqlcmd As New SqlCommand
    Try
        MYSQL.Open()
        sqlcmd.Connection = MYSQL
        sqlcmd.CommandText = "UPDATE tblSettings SET Setting='@setting' WHERE Feature='@feature'"
        sqlcmd.Parameters.Add("@setting", SqlDbType.Bit).Value = bSetting
        sqlcmd.Parameters.Add("@feature", SqlDbType.NVarChar, 50).Value = strFeature
        sqlcmd.ExecuteNonQuery()

I am getting the error

Conversion failed when converting the varchar value '@setting' to data type bit

I don't understand why it says varchar when the variable sent is boolean and I have declared the SqlDbType as bit.

I have also tried using the AddwithValue parameter without luck.

Public Function ChangeSetting(ByVal strFeature As String, ByVal bSetting As Boolean)
    Dim sqlcmd As New SqlCommand
    Try
        MYSQL.Open()
        sqlcmd.Connection = MYSQL
        sqlcmd.CommandText = "UPDATE tblSettings SET Setting='@setting' WHERE Feature='@feature'"
        sqlcmd.Parameters.AddWithValue("@setting", bSetting)
        sqlcmd.Parameters.Add("@feature", SqlDbType.NVarChar, 50).Value = strFeature
        sqlcmd.ExecuteNonQuery()

This produces no error, but values are not updated. Please help!

sticky bit · Accepted Answer · 2019-01-20 20:34:12Z

1

Remove the single quotes a around the parameters in your query text, i.e. change

...
sqlcmd.CommandText = "UPDATE tblSettings SET Setting='@setting' WHERE Feature='@feature'"
...

to:

...
sqlcmd.CommandText = "UPDATE tblSettings SET Setting=@setting WHERE Feature=@feature"
...

When using parameterized queries, quotes will placed if needed you don't have to do it manually and you shouldn't.

answered Jan 20, 2019 at 20:34

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

SQL Server : add parameter boolean from vb net into sql as bit

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related