I am writing a VB app that inserts data into a DB2 database and I am having problems inserting data containing a quote " ' ".

For example a user enters "Bob's Carpet Mart" as then Client_Name

My code is the following
dim cnn as adodb.connection
dim sqlIns as string

sqlIns= "INSERT INTO CLIENT (CLIENT_NAME) VALUES ('" & txt_CLIENT_NAME.TEXT & "'"

cnn.BeginTrans
cnn.Execute sqlIns
cnn.CommitTrans


My insert string (sqlIns) basically looks like this
INSERT INTO CLIENT (CLIENT_NAME) VALUES ('Bob's Carpet Mart')
As you can see there is an extra quote that creates a problem.

Unfortanetly I have tried the following without success
INSERT INTO CLIENT (CLIENT_NAME) VALUES ("Bob's Carpet Mart")

I do know that if I type
INSERT INTO CLIENT (CLIENT_NAME) VALUES ('Bob''s Carpet Mart')
in DB2 Script window it works, but this would be tedious to code and affect performance, since I would have to verify every character of all string data types.

What would be the best way to handle this.

Thanks for any help

Tedious?
And you're talking about performance while using ADO?

Alternatively, instead of ADO you could use some other MS acronym that would allow you to use parametrized statements, like ODBC SQLPrepare/SQLBindParameter/SQLExecute.

You can do parametrized statements with ADO

http://www-128.ibm.com/developerwork....html#listing6

Cheers
sathyaram

That REPLACE command did the trick, and thanks sathyaram_s, that ADO code looks cleaner than how I currently have it. When I'm done with my program I'll probally mod my code to use the parameters.


Thanks to both