How to use parameters "@" in an SQL command in VB

Question

I have this code to update my SQL database from data in a textbox, in VB. I need to use parameters in case the text contains a tic mark ,', or a quote ,", etc. Here is what I have:

dbConn = New SqlConnection("server=.\SQLEXPRESS;Integrated Security=SSPI; database=FATP")
    dbConn.Open()

    MyCommand = New SqlCommand("UPDATE SeansMessage SET Message = '" & TicBoxText.Text & _
                                            "'WHERE Number = 1", dbConn)

    MyDataReader = MyCommand.ExecuteReader()
    MyDataReader.Close()
    dbConn.Close()

And this is my lame attempt to set a parameter from what I have seen on the web, which I don't understand all that well.

dbConn = New SqlConnection("server=.\SQLEXPRESS;Integrated Security=SSPI; database=FATP")
    dbConn.Open()

    MyCommand = New SqlCommand("UPDATE SeansMessage SET Message = @'" & TicBoxText.Text & _
                                            "'WHERE Number = 1", dbConn)

    MyDataReader = MyCommand.ExecuteReader()
    MyDataReader.Close()
    dbConn.Close()

How do you do this? Cause if there is a ' mark in the textbox when I run the code, it crashes.

possible duplicate of [C# constructing parameter query SQL - LIKE %](http://stackoverflow.com/questions/664314/c-sharp-constructing-parameter-query-sql-like) — Ben, Jun 21 '12 at 14:01
[Also see](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx#Y300) — Matt R, Jun 21 '12 at 14:07

score 32 · Accepted Answer · answered Jun 21 '12 at 14:03

32

You are on the right path to avoiding Bobby Tables, but your understanding of @ parameters is incomplete.

Named parameters behave like variables in a programming language: first, you use them in your SQL command, and then you supply their value in your VB.NET or C# program, like this:

MyCommand = New SqlCommand("UPDATE SeansMessage SET Message = @TicBoxText WHERE Number = 1", dbConn)
MyCommand.Parameters.AddWithValue("@TicBoxText", TicBoxText.Text)

Note how the text of your command became self-contained: it no longer depends on the value of the text from the text box, so the users cannot break your SQL by inserting their own command. @TicBoxText became a name of the variable that stands for the value in the text of the command; the call to AddWithValue supplies the value. After that, your ExecuteReader is ready to go.

answered Jun 21 '12 at 14:03

Sergey Kalinichenko

714,442
84
1,110
1,523

3

Not a fan of the AddWithValue() method. Sometimes it guesses the parameter type wrong (ie: dates, varchar vs nvarchar), and sometimes when that happens the query no longer lines up with an index that it should, resulting in sometimes drastic performance problems. – Joel Coehoorn Jun 21 '12 at 14:08
@JoelCoehoorn Me neither, but that's the fastest way to get things going. – Sergey Kalinichenko Jun 21 '12 at 14:09
Thanks, that makes more sense for my purposes. – TitanicSwimmer Jun 21 '12 at 14:14

Joel Coehoorn · Answer 2 · 2013-05-29T14:19:35.590

12

There are a number of improvements in here:

Using dbConn As New SqlConnection("server=.\SQLEXPRESS;Integrated Security=SSPI; database=FATP"), _
      MyCommand As SqlCommand("UPDATE SeansMessage SET Message = @Message WHERE Number = 1", dbConn)

    'Make sure to use your exact DbType (ie: VarChar vs NVarChar) and size
    MyCommand.Parameters.Add("@Message", SqlDbType.VarChar).Value = TicBoxText.Text

    dbConn.Open()
    MyCommand.ExecuteNonQuery() ' don't open a data reader: just use ExecuteNonQuery
End Using 'Using block will close the connection for you

edited May 29 '13 at 14:19

answered Jun 21 '12 at 14:07

Joel Coehoorn

399,467
113
570
794

1

What are these improvements of which there are a number? – PsychoData Mar 07 '14 at 23:09
3

@PsychoData Correctly close the connection in case an exception is thrown, use query parameters, executenonquery() instead of executereader, explicit parameter type instead of inferred – Joel Coehoorn Mar 08 '14 at 02:05

How to use parameters "@" in an SQL command in VB

2 Answers2

Linked

Related