asked on

Quotes in a value passed in a SQL expression VB .NET 2003 Application

I have strings that have single quotes in the value. When I use the value in my SQL expression, of course it does not like it. Is there a way to get around this, other than changing the data in the table itself?

jake072

Yes, for all your string fields, do a simple replace:

str = str.Replace("'", "''")

Then, when outputting from the db, do the opposite:

str = str.Replace("''", "'")

Jake

mphillip85

ASKER

How do I pass a select command text with the single quote in the expression of the value like

"Select * FROM myTable WHERE myName = '" & "Cat's" & "';"

jake072

you use 2 single quotes for any single quote IN YOUR STRING.

So:

"Select * FROM myTable WHERE myName = '" & "Cat''s" & "'"

I don't know why you have the ; there?

Jake

mphillip85

ASKER

I put the ; in there because it wont error but will not pass the selec command either. Plus the programming book says to put the ;

How will this work.

"Select * FROM myTable WHERE Cat's= " & "Cat"s" & "'"

So you say that Cat's is = Cat"s ? Literally?

That is what I am going to end up with. Since I am not changing the data in the table iteslf.

I hope I am being clear. I am leaning on actually using CONTAINS when I find a single quote for these somewhat rare records.

Mike

ASKER CERTIFIED SOLUTION

jake072

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jake072

Just wondering, but why the 'B'?

I have been trying to clear any issues here. Was there something more you needed?

Jake