Problems with Quotes in SQL queries in VB.Net

[Fred Flintstone]

I'm writing an app in VB.Net that talks to MS SQL Server 2000. The
problem is that when someone enters a single quote into a field, I get
SQL errors. I've looked this up and its recommended that you double up
the quotes. So if someone enters:

O'Brien

I should be sending "O''Brien"

Know what happens what I do that? I get "O''Brien" in the table. So I
figured, maybe it's an escape thing so I tried this:

"O\'Brien"

And in the table I get "O\'Brien". If I use one single quote I get an
error.

How do I get this to work?

Thanks!

 

[James Jardine]

I'm writing an app in VB.Net that talks to MS SQL Server 2000. The
problem is that when someone enters a single quote into a field, I get
SQL errors. I've looked this up and its recommended that you double up
the quotes. So if someone enters:

O'Brien

I should be sending "O''Brien"

Know what happens what I do that? I get "O''Brien" in the table. So I
figured, maybe it's an escape thing so I tried this:

"O\'Brien"

And in the table I get "O\'Brien". If I use one single quote I get an
error.

How do I get this to work?

Thanks!

Are you sending the quote " or are you sending two ' in succession. If you
are using inline sql then you should replace ' with '' (2 '). This is the
correct way to escape the single quote. A better way if possible would be
to use stored procedures or parameterized values so you don't have to escape
the single quote. What is the error you are getting?

 

[Fred Flintstone]

I've tried them as 2 single quotes, chr(39) + chr(39) and every time,
I get exactly two single quotes in the database.

So I removed the double single quotes to replicate the error as
requested and...

I don't get it. It works perfectly now. I don't need to double up
the quotes. I've been banging my head over this all day and now that
I put it back the way it was, the problem has disappeared and it's
writing records no problem, quotes or not.

Thanks anways
(I hate it when that happens)

 

[Carlos J. Quintero [VB MVP]]

Hi Fred,

- When you don´t use parameter binding, your code (or some component along
the chain) must convert a single quote ' to two single quotes ''. Otherwise
the ' character is confused with a string delimiter.

- When you bind parameters, this is not required (since string delimiters
are not needed).

--

Best regards,

Carlos J. Quintero

MZ-Tools: Productivity add-ins for Visual Studio
You can code, design and document much faster:
http://www.mztools.com