Problems with Quotes in SQL queries in VB.Net

  • Thread starter Thread starter Fred Flintstone
  • Start date Start date
F

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!
 
Fred Flintstone said:
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!
Click to expand...

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?
 
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)
 
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
 
Back
Top