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]]

David Bird <co**********@hotmail.com> wrote:

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

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[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.

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