Replacing Apostrophe (tick mark) in SQL query 1

[RonRepp]

Hi all:

This has been my downfall since starting a project that includes several items within the DB that use a tick mark (') for names and such.

I can do a strict find and replace, but then when I pass that value as an argument, it returns 0 records.

This is what I'd like to do:

[code}

Dim Pos As Integer
Dim s As String, t As String
Pos = InStr(0, Record, "'")
If Pos > 0 Then
s = Left(Record, Pos)
t = Replace(Record, "'", "/''/")
....
End If
[/code]

Is my syntax correct for this?

Any help will be greatly appreciated.

Thanks,



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[ThatRickGuy]

you can also use:

MyString = MyString.Replace("'","\'")
[code]

The escape character matters on the variety of SQL you are using though. SQL Server and Sybase take '' (two apostrophes) as an escaped apostrophe). I can't recall Access or Oracle's preferences though.

-Rick

VB.Net Forum forum796    forum855 ASP.NET Forum
    [monkey][url=http://www.ringdev.com]I believe in killer coding ninja monkeys.[/url][monkey]

 

[RonRepp]

Rick:

Once again...it worked like a charm.

Thanks,


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[chiph]

Take a look at faq709-1526
It's for VB6, but the same ideas/concepts apply to VB.NET.

Failing to use Parameter objects will result in your code being vulnerable to SQL Injection attacks, and performing slower than it could.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first