Problems with Quotes in SQL queries in VB.Net

20 Mar 2006 7:32 PM

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!

20 Mar 2006 7:43 PM

James Jardine

Show quote Hide quote

"Fred Flintstone" <idontthinkso@nospam.com> wrote in message
news:2q0u125hhj2us8d69itdbokl1pla12h2rj@4ax.com...
> 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?

20 Mar 2006 8:14 PM

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)

On Mon, 20 Mar 2006 14:43:54 -0500, "James Jardine"
<james.jard***@geoage.com> wrote:

Show quoteHide quote

>
>"Fred Flintstone" <idontthinkso@nospam.com> wrote in message
>news:2q0u125hhj2us8d69itdbokl1pla12h2rj@4ax.com...
>> 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?
>

21 Mar 2006 10:29 AM

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

Show quoteHide quote

"Fred Flintstone" <idontthinkso@nospam.com> escribió en el mensaje
news:hg2u1296dfih9rttfsvl37nufbvh9l7eo5@4ax.com...
> 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)
>