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

Question

I have this code to update my SQL database from data in a text box in VB. I need to use the options if the text contains the tic character, or quote "etc. 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 saw on the Internet that I do not understand very 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? The reason, if there is a mark in the text box, when I run the code, it crashes.

Answer 1

You are on the right track to avoid Bobby Tables , but your understanding of @ options is incomplete.

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

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

Notice how the text of your command has become self-contained: it no longer depends on the value of the text from the text field, so users cannot break your SQL by inserting their own command. @TicBoxText became the name of the variable that denotes the value in the text of the command; a call to AddWithValue passes the value. After that, your ExecuteReader ready to ExecuteReader .

Answer 2

There are a number of improvements:

 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