How to convert character ( ' ) in VB to accepted in SQL - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

How to convert character ( ' ) in VB to accepted in SQL

warrenwarren Posts: 1Member
I hope someone out there can help me in this matter

I developed an application using VB 6 and SQL 7.0

The problem i am facing is that in the application whenever a user keys in a data in particular field the the symbol ( ' )
eg. People's

I get a run time error prompt from SQL which is

Run time error '-2147217900(80040214)
[Microsoft][ODBC SQL SERVER DRIVER][SQL DRIVER]Line 1: incorrect syntax near 's'

Is there a way that i can force or even convert this (') in VB to be acccepted by SQL server? I hope someone out there can really help with a solution.....thanx guys & girl

I really would appricate the help.


Comments

  • MikePMikeP Posts: 3Member
    : I hope someone out there can help me in this matter
    :
    : I developed an application using VB 6 and SQL 7.0
    :
    : The problem i am facing is that in the application whenever a user keys in a data in particular field the the symbol ( ' )
    : eg. People's
    :
    : I get a run time error prompt from SQL which is
    :
    : Run time error '-2147217900(80040214)
    : [Microsoft][ODBC SQL SERVER DRIVER][SQL DRIVER]Line 1: incorrect syntax near 's'
    :
    : Is there a way that i can force or even convert this (') in VB to be acccepted by SQL server? I hope someone out there can really help with a solution.....thanx guys & girl
    :
    : I really would appricate the help.
    :
    :


  • MikePMikeP Posts: 3Member
    I only program in VBA so this might not work.

    The only way I know of is to use a replace function.
    You can either replace the ' with nothing, so the function therefore deletes the quote, or if you want to keep the quote and store it in SQl then you replace the quote with a double quote, like ''.

    SQL then interprets this as the desired input.

    Hope this helps.

    General syntax is:

    var = replace("text","'","''")

    can you let me know if this works.

    Thanks

    Mike
    : I hope someone out there can help me in this matter
    :
    : I developed an application using VB 6 and SQL 7.0
    :
    : The problem i am facing is that in the application whenever a user keys in a data in particular field the the symbol ( ' )
    : eg. People's
    :
    : I get a run time error prompt from SQL which is
    :
    : Run time error '-2147217900(80040214)
    : [Microsoft][ODBC SQL SERVER DRIVER][SQL DRIVER]Line 1: incorrect syntax near 's'
    :
    : Is there a way that i can force or even convert this (') in VB to be acccepted by SQL server? I hope someone out there can really help with a solution.....thanx guys & girl
    :
    : I really would appricate the help.
    :
    :


  • JerryNJerryN Posts: 1Member
    I came across this problem a couple of years ago. I fixed it by checking the value of the keyascii value in the keypress event of the control.

    if chr(keyascii) = "'" then
    keyascii = asc("`")
    endif

    This would automatically change the single quote to a character that not be accepted by SQL but also give a similar appearance as the single quote.

    : I only program in VBA so this might not work.
    :
    : The only way I know of is to use a replace function.
    : You can either replace the ' with nothing, so the function therefore deletes the quote, or if you want to keep the quote and store it in SQl then you replace the quote with a double quote, like ''.
    :
    : SQL then interprets this as the desired input.
    :
    : Hope this helps.
    :
    : General syntax is:
    :
    : var = replace("text","'","''")
    :
    : can you let me know if this works.
    :
    : Thanks
    :
    : Mike
    : : I hope someone out there can help me in this matter
    : :
    : : I developed an application using VB 6 and SQL 7.0
    : :
    : : The problem i am facing is that in the application whenever a user keys in a data in particular field the the symbol ( ' )
    : : eg. People's
    : :
    : : I get a run time error prompt from SQL which is
    : :
    : : Run time error '-2147217900(80040214)
    : : [Microsoft][ODBC SQL SERVER DRIVER][SQL DRIVER]Line 1: incorrect syntax near 's'
    : :
    : : Is there a way that i can force or even convert this (') in VB to be acccepted by SQL server? I hope someone out there can really help with a solution.....thanx guys & girl
    : :
    : : I really would appricate the help.
    : :
    : :
    :
    :


Sign In or Register to comment.