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

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.