Function that writes data containing quotes to database

[kevinj]

I have a function that replaces single quotes in a SQL statement and it handles the write to database fine. I need to expand that function to replace double quotes and still have them display when the database field is displayed. Here's the function I have that does the single quote replace:

Function StoDB(data)
Dim returnVal
returnVal = "'" & Trim(Replace(data, "'", "''")) & "'"
If returnVal = "''" Then returnVal = "null"
StoDB = returnVal
End Function

What can I add so that it will handle double quotes as well?

Thanks,
Kevin

[txbakers]

I beleive it's the same function, but you have to put the double quotes inside double quotes (making four sets of quotes).

[kevinj]

I am hoping to replace every instance of " with the same so it will display. Right now it cuts off everything after the "

[aspdaddy]

Have a sneaky feeling its is 3 sets of double quotes not 4, could be wrong though :)

[txbakers]

I get so confused with that. I usually write my routines that involve quotes in JavaScript so that I can use the escape character "\".

ASP can run both languages, so why not try that - use a Javascript function to convert the quotes, then the rest in VB.

You could even do it client side first, on the Submit event.

Quotes are a nuisance for sure.

[kevinj]

I figured out the problem. It was writing the quote to the database fine. On the display page I had the following

<input type=text name="CONTACT_EMAIL" value="<%= CONTACT_EMAIL %>">

Once I removed the quotes from around the value like this

<input type=text name="CONTACT_EMAIL" value=<%= CONTACT_EMAIL %>>

the display page showed the quote fine. It was truncating the value at the quote mark in the field due to the opening quote mark value="<%= CONTACT_EMAIL %>">

Thanks everyone for your help!

[duckhunter]

The solid way to do it is replace the Character codes 34 & 39 with doubles like this:

returnVal = Replace(data, Chr(34), Chr(34) & Chr(34))
returnVal = Replace(returnVal, Chr(39), Chr(39) & Chr(39))

If you're calling a stored proc with input parameters, this is not necessary, it will do it for you.