Escape Character

[desi2002]

Hello,

the query is
insert into test1 (name) values (@string)

if the @string includes the single quote character in ('), i need to escape that character in some fashion. Does somebody know how to do it?

Thanks
S

 

[desi2002]

i got the answer.
@string='Hello ''World''' ends up being inserted into your database as Hello 'World'.

 

[tlbroadbent]

I'm not quite sure if I understand the problem. The insert statement should work even if the string contains a single quote. However, if you are creating and executing the Insert statement, you'll need to replace the single quotye with two single quotes. and enclose the string in quotes.

Example:
Set @sql='Insert Test1 Values (''' + replace(@string, '''', '''''') + ''')'
Exec(@sql)

Please let me know if this is what you need or if I've missed the point entirely. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[chiph]

Terry -
I don't think he's using ADO parameters, which take care of escaping the single and double-quote characters for you.

Chip H.

 

[desi2002]

Terry and Chip Thanks for your response.
I could solve the problem.

The user enters the data through html form. I insert the data into the database through "insert" statment in my asp file. I wrote a function "replacequote" which replaces a single quote by double quotes in the string and use the new string into "insert" statement.

Ex: "message" is a form field.
message=replacequote(message)

sqltrack = "Insert into users (message)"
sqltrack = sqltrack & "values ('" & message & "')"
Set dbConn = GetConnection()
'Execute the SQL's
dbConn.Execute sqltrack

So if message="It's a test"
replacequote(message) will make message as "It''s a test"

Then it takes care of quotes within the data, and doesn't give SQL server error message "Incorrect syntax near 's".
Now it stores it properly in the database as: It's a test

Thanks
Smita