Quotes question

[stevelibby]

I have developed my own cms system but i am having issue with test updates as if there are any ' or " then it doesnt update as i use the update where a = '" & Request("FormFeild") & "'

Has anyone got any work arounds?

[bmcgee]

Either double up the ticks and double quotes with the Replace() function...

Or use stored procedures instead of inline SQL statements

[stevelibby]

can you give me an axample of what you mean.

[bmcgee]

sql = "update mytable set myfield = '" & Replace(request.form("myfield"), "'", "''") & "' WHERE somecondition=somevalue"

But better is to use a stored procedure, pass the string to it as a param. Then you don't have to worry about the ticks as the proc will handle it properly.

[bateman_ap]

I use the following, place this in your page (I usually stick it in an include on every page)

Function FixForSQL(tmpText1)
 Dim tmpText2
 tmpText2 = tmpText1
 tmpText2 = Replace(tmpText1,vbCrLf,Chr(13))
 tmpText2 = Replace(tmpText2,Chr(39),String(2,39))
 FixForSQL = tmpText2
End Function

Function FixForHTML(tmpText1)
 Dim tmpText2
 tmpText2 = tmpText1
 tmpText2 = Replace(tmpText2,Chr(13),"</p><p>" & vbCrLf)
 tmpText2 = Replace(tmpText2,Chr(9),"&#xa0;&#xa0;&#xa0;&#xa0;")
 FixForHTML = tmpText2
End Function

Then when you want to add something into a database pass it through like this before

FormField = FixForSQL(FormField)

Also when you want to display a value from the database use

FormField = FixForHTML(FormField)

Otherwise you will display '' etc