quote delimiters double and single

[David Bird]

I am attempting to build a command string to update a field in a dataset.
the command string looks like this:
Dim cmdString As String = "Update [data table] set lName = '" & clName.Trim
& "', fName = '" & cfName.Trim & "' Where id = " & lnID

the command works fine unless and until clname and/or cfname contains an
apostrophe, ie: O'Hare

When the executeNonquery is run, the quotes are out of balance and a syntax
error is thrown.

How can I build a command to update a field where it has apostrophes? I
can't find any way to change delimiters, like using brackets around the
variables [O'Hare] instead of a single tick.

thanx.

daver

 

[Jon Skeet [C# MVP]]

I am attempting to build a command string to update a field in a dataset.
the command string looks like this:
Dim cmdString As String = "Update [data table] set lName = '" & clName.Trim
& "', fName = '" & cfName.Trim & "' Where id = " & lnID

the command works fine unless and until clname and/or cfname contains an
apostrophe, ie: O'Hare

When the executeNonquery is run, the quotes are out of balance and a syntax
error is thrown.

How can I build a command to update a field where it has apostrophes? I
can't find any way to change delimiters, like using brackets around the
variables [O'Hare] instead of a single tick.

The best thing is not to build up your command string like that in the
first place. Instead, use parameterised SQL. That way you don't need to
do any quoting yourself.

See http://www.pobox.com/~skeet/csharp/faq/#db.parameters

 

[Imran Koradia]

Are you using sql server? If you so, you'll need to escape the apostrophe
with yet another apostrophe:

Dim cmdString As String = _
"Update [data table] set lName = '" & _
Replace(clName.Trim, "'", "''") & "', _
fName = '" & Replace(cfName.Trim, "'", "''") & _
"' Where id = " & lnID

I believe its the same for Access but I'm not sure about other databases.

hope that helps..
Imran.