VB parameterization using string literals instead of assigning Params

Question

I am attempting to parameterize my query, but I can see with the message box, the actual query is just using string literals @st & @sn instead of the values passed in. I really can't seem to figure out what I'm doing wrong.

Protected Function UpdateDeviceStatus(serial_Num As String, Site As String, Status As String) As Boolean
    Windows.Forms.MessageBox.Show("Status:" + Status + "  SN:" + serial_Num + "  Site: " + Site)
    Using db = DBConnect()
        db.CommandText = $"
        UPDATE myTable SET Status =  @st WHERE serial_number = @sn "


        db.AddParameter("@st", Status)
        db.AddParameter("@sn", serial_Num)
        Windows.Forms.MessageBox.Show(db.CommandText)
        db.Execute()
        Return True
    End Using
End Function

Answer 1

This is how it's supposed to work.

The whole point of query parameters is the parameter values are NEVER, at any time, substituted directly into the SQL command. Instead, parameter values are sent to the database server in a completely separate data block from the command text. In this way, any possibility of injection is prevented.

If you had an idea parametrized queries were about correctly escaping the the inputs for inclusion in the query, this idea was incorrect. Parameterized queries do not escape the provided data! Rather, they quarantine it.

Therefore you will never be able to show the completed SQL command in a MessageBox, and that's a good thing. It will still execute with the intended values when you actually run it.

Answer 2

See the example here

What you are looking for is Command.Parameters.AddWithValue