Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    10

    Question Unanswered: Problem with Quote

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by rongiul
    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.
    Tedious?
    Code:
     sqlIns= "INSERT INTO CLIENT (CLIENT_NAME) VALUES ('" & replace(txt_CLIENT_NAME.TEXT,"'","''") & "')"
    
    cnn.BeginTrans
    cnn.Execute sqlIns
    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.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You can do parametrized statements with ADO

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

    Cheers
    sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Oct 2003
    Posts
    10
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •