Not able to update record

[karunakaran]

Hi all,
I am not able to update a record with the following type.
Front end VB6.
Database access.

Table about company.
fields. id(number), companyname(text),status(text)

If I update a record without "'" symbol in the company name field everything works fine.

But if I give the company name like " ONG'S " in the update state ment it strucks. It is giving some syntax error in the SQL statement.

Anyone please help me how to over come with this "'" symbol to include in a company name.

Thanks in advance
karun

[lenin]

Hello,
I am presuming the issue is with the VB string. In which case try this ( insert example ):

ls_SQL = "Insert into customer(name) values(""gerry's"")"

Thanks

lenin

[karunakaran]

Hai,
If it is hard coded it is working.It is already I tried.
But I cannot pass it through a variable.

Can You and any please help me

Thanks
karun

[Mark Sreeves]

This might help you:

Option Explicit
Dim db As Database
Const Quote = """"



Private Sub Command2_Click()
Dim strSQL As String
'this works
strSQL = "INSERT into table1(colour1)VALUES(" & Quote & Text1.Text & Quote & ");"

'this fails with ONG'S
'strSQL = "INSERT into table1(colour1)VALUES('" & Text1.Text & "');"

db.Execute strSQL

End Sub

[Clunietp]

Or you could just use VB6's replace function on all sql statements before you execute them:

strSQL = "Select * from Customers where LastName = 'O'Brian'"

'note that the 3rd parameter is 2 single quotes
'while the 2nd parameter is 1 single quote
strSQL = replace(strSQL, "'", "''")

'the sql is now properly formatted to allow
'querying on single quotes
set rs = cn.execute(strSQL)

Tom