' marks and sql trouble

[slider]

Hello all!

i have a page where a user can type freely whatever he or she desires and it gets written to an access database via the Microsoft.Jet.O LEDB.4.0 connection using the following sql statement

sSQLB = "Update Profile SET txt1 = '" & txt1 & "', txt2 =' " & txt2 & "', txt3 = '" & txt3 & "', txt4 = '" & txt4 & "', txt5 = '" & txt5 & "' where USER_ID = " & USER_ID & ""

this works fine and enters the values into the database. The problem i face is when a user enters a ' sign. for example if a user was to write. " slider's car is the best " the ' sign in slider will cause an error stating the following "Syntax error (missing operator) in query expression"

i havnt come up with any idea of how to counteract this and im hoping someone here will.

i can see why the problem occurs its just on how to fix it i am lost.

Thanks

[ck9663]

Hello all!

i have a page where a user can type freely whatever he or she desires and it gets written to an access database via the Microsoft.Jet.O LEDB.4.0 connection using the following sql statement

sSQLB = "Update Profile SET txt1 = '" & txt1 & "', txt2 =' " & txt2 & "', txt3 = '" & txt3 & "', txt4 = '" & txt4 & "', txt5 = '" & txt5 & "' where USER_ID = " & USER_ID & ""

this works fine and enters the values into the database. The problem i face is when a user enters a ' sign. for example if a user was to write. " slider's car is the best " the ' sign in slider will cause an error stating the following "Syntax error (missing operator) in query expression"

i havnt come up with any idea of how to counteract this and im hoping someone here will.

i can see why the problem occurs its just on how to fix it i am lost.

Thanks

create a function that will replace a ' with a " or replace ' with '''.

[slider]

create a function that will replace a ' with a " or replace ' with '''.

ow thats a great idea thanks a lot!. so i would have to create a function which looks for the ' signs and then replaces them before it writes to the database and then when retrieving the values it replaces the " with '.

hmm seems simple enough. can any one point me in the right direction of how to do this?

[slider]

ok i worked it all out
if anyone else is having the same sort of problem this code will let you replace on value for another

Dim string_var,string_find,string_replace
string_var= "slider's"
string_find= "'"
string_replace="^"
response.write Replace(string_var,string_find,string_replace)

slider^s

hope this helps