Single Quote Problem in SQL statement

Hi,
My program has a listbox, which lists all the product names. Some of the products names contain one or more single quotes ('). The problem is the following. Please look at the code...

=====================================
Dim db as Database
Dim rs as Recordset
Set db =OpenDatabase(App.Path & "/mydb.mdb")
Set rs= db.OpenRecordset("SELECT * FROM MainTable Where [ItemName]='" _
& lstItems.text & "'" , dbOpenDynaset) 'Where lstItems is the listbox control
========================================

lstItems has the product name of "American's Flag 4'x8'". When I put it into the SQL statement, it returns a Syntex error because the single quotes ('). Is there anyone knows how to solve the problem? In C, I know that I could reformat the string as
Str="American\'s Flag 4\'x8\'"

How can I do the same thing in VB?

Thank You.

[MarkK]

use
Select * from Customers Where CustomerID=" & chr(34) & lstItems.text & Chr(34)
Good Luck

You can write a Code that will pad a Quote to the string like
McDonnald's you need string like 'MCDonnald''s'. you can use CHAR(34), but what if you have more quotes in string like ->> PS'tsu'sd'd.

So my friend the best solution is write a routine that will Pad a string with

quote.

enjoy,

[Crazy D]

With VB6 you can use the Replace$ function.
Eg.
sToSearch = Replace$(sToSearch, "'", "''")

Crazy D :-)
"One ring rules them all"

[freek]

You can add an extra quote (works for oracle)
I always use the following funcion


public Function schoonmakenString(naam as string) as string
Dim teller as Integer

teller = 1
While teller <= len(naam)
If mid(naam, teller, 1) = "'" then
'if the char is an "'" then put an second "'"
'after it (just include the "'" two times
'in the mid funtion
naam = mid(naam, 1, teller) & mid(naam, teller, len(naam))
teller = teller + 1
End If
teller = teller + 1
Wend
schoonmakenString = naam
End Function