We help IT Professionals succeed at work.

Can't remove single quote

Bob Schneider
Bob Schneider asked
on
Can someone tell me why this code won't replace single quotes with a pair of single quotes?

	Dim sFldVal
	
	Set rs = Server.CreateObject("ADODB.Recordset")
	sql = "SELECT Comments FROM GrdbkRprtStdnts ORDER BY Comments"
	rs.Open sql, conn, 1, 2
	Do While Not rs.EOF
		sFldVal = rs(0).Value

		If Not sFldVal & "" = "" Then 
			sFldVal = Replace(sFldVal, "'", "''")
			rs(0).Value = sFldVal
			rs.Update
		End If
		rs.MoveNext
	Loop
	rs.Close
	Set rs = Nothing

Open in new window


Everytime I run into a single quote it throws an ole db error...:

Comment
Watch Question

View Solutions Only

BRONZE EXPERT
Commented:
Pratima Pharande
BRONZE EXPERT

Commented:
Eyal
Commented:
this will replace all your code :)

conn.execute "update GrdbkRprtStdnts set Comments = replace(Comments,CHAR(39),CHAR(39)+CHAR(39))"

Open in new window

jogos
Commented:
Didn't look on the why of your error.

But it will be much more efficient to do the update directly without the reading and looping.

UPDATE GrdbkRprtStdnts 
SET Comments = REPLACE(Comments,'''','''''') 
WHERE Comments LIKE '%''%'

Open in new window

In sql for the single quote in a string you have to double it.
jogos
Commented:
but don't run that twice :)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.