replace ' with " 1

[andycape]

I am submitting information into a database using ASP & SQL. Before i send the information into the database i want to replace all '(single quotations) with a "(double quote). Users will be using this so its impossible to stop them from entering single quotes which will crash the sql statement, so I need to change them into double quotes.

can you do this in sql before inserting into the database?
thanx. andy

 

[stanja]

hai ,

use some vbscript in your asp page to do the replace
fe Replace(data, "'", ""&quot this will replace the ' by "

 

[JamesLean]

You do not have to replace the single quotes with a double quote - you just have to replace it with two single quotes:

strData = Replace(strData, "'", "''")

This will actually insert the correct string (including the single quote) into the table.

--James

 

[andycape]

thanx !

 

[Bygbobbo]

An even better solution/practice is making a stored procedure on the SQL side that does the insert for you. if you pass in variables (with single ticks) from your asp to execute your stored proc, it will be inserted with a single tick.

Then you don't have to handle the double tick later on when you select.

I prefer to keep all my operations in small containers so later on I can modify and test things in their appropriate environments which makes things easier when it comes to change management. This also makes a lot of code re-usable.

Bygs