Sending SQL Statement cause error

[M8KWR]

I have create an sql connection, and the sql statement in code, using a field within a database.

The problem that i am having is that the field in the database contain characters that sql uses, thus causing the statement to fall over.

Is there anyway around this, without me having to go through all 180+ sq database to look for certain characters.

The symbols that i have come across so far that cause the statement to fail are ' and "

Many thanks for any help in advance.

M8KWR

[GaryMazzone]

For the character single qoute ( ' ) needs to be replaced in any SQL statement with two single qoutes ( '' ). The double qoute ( " ) need to be replaced with 4 ("''") double qoutes.

You do this using the Replace Function, as an example I want to sotre 1 feet 11 inches into a text field in a database:

 Dim strSQL As String = "Insert Into Distance (DistanceFrom) Values ('"
strSQL &= Replace(Me.txtDistance.Text,"'","''").Replace(Me.txtDistance.Text,"""","""""") & "')"

This will not REPEAT WILL NOT store duplicate qoutes into the database it just is used as an escape charactor for the SQL parser to let it know you are using the characters in a different way. You will also hear from many here that that is not the way to do this and you should be using Parameterized statements. I personnaly still do it this way.

[jmcilhinney]

The only time characters like that would cause an issue is if you're using string concatenation to build SQL statements, e.g.

Dim name As String = "O'Connell"
Dim command As New SqlCommand
 
command.CommandText = "SELECT * FROM Person WHERE LastName = '" & name & "'"

That will cause an issue, but it should never arise because you should never use string concatenation to build SQL statements. This the proper way to insert a variable into an SQL statement:

Dim name As String = "O'Connell"
Dim command As New SqlCommand
 
command.CommandText = "SELECT * FROM Person WHERE LastName = @LastName"
command.Parameters.AddWithValue("@LastName", name)

Now there's no issue. If everyone would do things the right way and not the lazy way this question would never be asked because everyone would learn the right way from the start.

[GaryMazzone]

Whty do you condier it the lazy way? I learned SQL on a SQL command prompt from an Oracle Database. We didn't have parameters, we leaned SQL and how to use it.

[jmcilhinney]

Whty do you condier it the lazy way? I learned SQL on a SQL command prompt from an Oracle Database. We didn't have parameters, we leaned SQL and how to use it.

If you have no choice then you have no choice, but in .NET code you have a far superior choice. Using string concatenation is the lazy way because people think it's too much trouble to make the effort to understand how to use parameters. String concatenation takes next to no thought because people already know how to do it and then we end up with thread after thread asking why SQL statements won't work because of dates or special characters in text. I used to use string concatenation myself because that's the only way I saw how to do it on the Web. When I found out how to use parameters I cursed everyone who posted anything else on the Web for being the cause of so much angst. If everyone learned to use parameters and only parameters then a whole load of heartache would be saved. If we ever had to drop back to a commandline tool then any developer with half a brain could work out how to work around the situation but in .NET you don't need to so you can apply your entire brain to things that matter.