Ignore ' in when inserting into a table

  • Hi,

    I have a VBscript task in my DTS.

    In that, I have update query. query is as follows...

    sqlstr1 = "update employee set managerDeptNum=" & rst("JAN") & ", "

    sqlstr1 = sqlstr1 & "MANAGERDEPDESC = '" & trim(rst("JanDeptDesc")) & "',"

    sqlstr1 = sqlstr1 & "MANAGERNAME = '" & trim(rst("JanDeptManager")) & "'"

    sqlstr1 = sqlstr1 & " where ExpdFSRBadgeNumber = '" & rst("FSRBadgeNumber") & "'"

    But the Manager name has sepcial charecter (') single quote as O'Dwyer Sean

    This is giving an error that syntax error.

    I need the whole name to be updated in the table?

    Could any one suggest on this?

    Thank You

  • Hi there,

    sorry if what I will, give you is not what you want. I didn't finish reading the code cause I was just given a new task. Anyway, I hope this helps.

    ' is used by SQL for strings like 'Quatrei'

    if you want to use it within a string, just type it twice like so

    SELECT ' '' '

    By the one, using single qoutes twice doesn't mean you can have the double qoute as a replacement

    -- single qoute

    SELECT 'Quatrei''s Code'

    -- double qoute

    SELECT 'Quatrei"s Code'

    -- this example is wrong but I think it can help you in existing records

    SELECT Replace('Quatrei"s Code','"','''')

    sorry for the bad English...

    🙂 hope itr helps

    _____________________________________________
    [font="Comic Sans MS"]Quatrei Quorizawa[/font]
    :):D:P;):w00t::cool::hehe:
    MABUHAY PHILIPPINES!

    "Press any key...
    Where the heck is the any key?
    hmmm... Let's see... there's ESC, CTRL, Page Up...
    but no any key"
    - Homer Simpson
  • Quatrei.X (9/11/2008)


    Hi there,

    sorry if what I will, give you is not what you want. I didn't finish reading the code cause I was just given a new task. Anyway, I hope this helps.

    -- double qoute

    SELECT 'Quatrei"s Code'

    -- this example is wrong but I think it can help you in existing records

    SELECT Replace('Quatrei"s Code','"','''')

    🙂 hope itr helps

    Thanks for your reply.

    I tried with Replace first directly in the query. But it wasn't worked.

    again I tried Replace using a variable in VBScript. It is working now.

    Thanks a lot for your help.

    Thank You

  • You should at least properly prepare your SQL statements and use parameterized queries. SQL injection is a common security problem and you have made it easily possible with your code.

  • 😀 Hi again,

    Glad that I was able to help ^__^

    _____________________________________________
    [font="Comic Sans MS"]Quatrei Quorizawa[/font]
    :):D:P;):w00t::cool::hehe:
    MABUHAY PHILIPPINES!

    "Press any key...
    Where the heck is the any key?
    hmmm... Let's see... there's ESC, CTRL, Page Up...
    but no any key"
    - Homer Simpson

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply