CODEWITHSUNDEEP
sqlsql-servervb.net
AlexLega
AlexLega

Reputation: 11

Insert data to a SQL Server database that contains apostrophes

I'm making this program on Vb.net 2012 that has a connection to SQL Server 2012.

One of the columns of this database table is Description, and in some cases the date may include apostrophes, for example... 'chainsaw 15'3" X 1 1/2 X .050 X 3/4'

When I run the query the apostrophe that is between the data causes an error at the syntax of the query, this is the query line in VB.net.

CMD.CommandText = 
"INSERT INTO Table_ARTICLES 
 (NUMPART, DESCRIPTION, LOCATION, MAX, ACTUAL, MIN, Unidad_de_medida) 
 VALUES ('" & txtNumParte.Text & "', '" & txtDescripcion.Text & "', 
  '" & txtLocaclizacion.Text & "', '" & txtMaximo.Text & "', '" & txtActual.Text & "',
  '" & txtMin.Text & "', '" & cmbUnidad.Text & "')"

Does anybody know how to make this query accept those characters on the query?

Upvotes: 0

Views: 796

Answers (2)

Eli
Eli

Reputation: 2608

Please use parameterized SQL (i.e. stored procedures) to prevent SQL injection and the like.

As for your question, you would want to replace the single quote (apostrophe) with two single quotes before you add it as a parameter. This way the first one acts as an escape character which will allow for the apostrophe to be inserted into the database.

Example: txtNumParte.Text.Replace("'", "''")

Upvotes: 0

Icarus
Icarus

Reputation: 63966

As @pmbAustin pointed out, is a terrible idea to build sql statements via string concatenation due to SQL Injection attacks and other problems. The approach you should use is called a parametrized query:

CMD.CommandText = "INSERT INTO (NUMPART, DESCRIPTION, LOCATION, MAX, ACTUAL, 
MIN, Unidad_de_medida) 
VALUES (@NUMPART, @DESCRIPTION,@LOCATION,@MAX,@ACTUAL,@MIN,@UNIDAD_DE_MEDIDA)"

And then:

CMD.Parameters.Add("@NUMPART",txtNumParte.Text);
CMD.Parameters.Add("@DESCRIPTION",txtDescripcion.Text);
//...and so on
CMD.ExecuteNonQuery();

Upvotes: 3

Related Questions