Using Replace on Memo Fields

[jeremywatco]

When trying fix single quotes when displaying database items on the screen I am recieving an error. I am trying to run a replace statement on a memo field and I am getting the following error:

Microsoft VBScript runtime error '800a005e'
Invalid use of Null: 'Replace'

/functions.asp, line 14




Here is my code where it is erroring out:

Function CSQ(byVal str)
If IsNull(str) Then str = ""
CSQ = Replace(str,"'","''")
End Function



Any ideas??

[head8k]

There is no need to check if the string is null.

Function CSQ(str)
CSQ = Replace(str,"'","''")
End Function

Should do it.

[jeremywatco]

That works. Now the other problem is that when i go to display the memo field on the screen using a response.write(rs(memofieldname)) it truncates the output. Only a small portion is displayed. The database field does contain all the data... is this normal?

[arnyinc]

Using response.write(rs(memofieldname)) will retrieve the value from your database again and print the value without using your CSQ() function.

[raf]

You only need to use replace(variable,"'","''") on values for variables that you are going to insert in a database.

Values from string-variabels that you select from the database, should be encoded like
response.write(Server.HTMLEncode(rsname.Fields("variable"))

[whammy]

Actually, trying to replace a null value is what's causing that error...

And raf is right:

You only need to use replace(variable,"'","''") on values for variables that you are going to insert in a database.


Instead of this:

Function CSQ(byVal str)
If IsNull(str) Then str = ""
CSQ = Replace(str,"'","''")
End Function

It should be this:

Function CSQ(byVal str)
If IsNull(str) Then Exit Function
CSQ = Replace(str,"'","''")
End Function

[head8k]

What Whammy is saying is true but I suspect you may still be getting the truncation problem if using Access as your database. There is a known bug with ADO when using certain SQL statements that causes only the first n characters to be displayed (often 255) from a memo field. I have experienced this when using a SELECT DISTINCT... query with an ORDER BY clause. To get around the problem you will have to re-write the SQL statement. I hope this points you in the right direction.