Update a field with a series of replace statements

[mych]

I have not done any SQL programming direct but feel I need to as my asp script times out.

I have a table D_Incidents that has a field Updates. The Updates field is an ntext and contains a string of characters. What I need to do is replace a series of = (I originally used as a separator with a <hr> and a CrLf into a <br>

my asp page had the following code

Server.ScriptTimeOut = 6000

	strSQL = "SELECT IncidentRef, Updates "
   	strSQL = strSQL & "FROM D_INCIDENTS"

	set rsIncidents =  my_Conn.Execute( strSQL)

			rsIncidents.movefirst
		
			do until rsIncidents.EOF
			
	strUpdates = rsIncidents("Updates")
	InCaseRef = rsIncidents("IncidentRef")
	
	    strUpdates = replace(strUpdates,"================================================", "<br><hr>")
		strUpdates = replace(strUpdates,chr(13)&chr(10),"<br>")

	strSQL1 = "Update D_INCIDENTS"
	strSQL1 = strSQL1 & " SET Updates =" & "'" & strUpdate & "'"
	strSQL1 = strSQL1 & " WHERE IncidentRef = " & "'" & InCaseRef & "'"

	response.Write(strSQL1 & "<br>")
       my_Conn.Execute (strSQL1)
       
			    rsIncidents.MoveNext
			loop
	
%>



<%	set rsIncidents =  nothing
	rsIncidents.close %>

unfortunately this times out no matter what value I enter for Server.ScriptTimeOut

I've search the web and found some examples of syntax for FETCH and REPLACE but not sure how to do my loop.

Any help appreciated.

 

[vongrunt]

.moveFirst is not necessary.

Check this line:

strSQL1 = strSQL1 & " SET Updates =" & "'" & strUpdate & "'"

Variable strUpdate is never declared, unlike strUpdates.

Anyway, doesn't matter. Looping in SQL = bad. Get used to this fact . Try this instead:

strSQL1 = "update D_INCIDENTS " & _
	"SET Updates = Replace(Replace(Updates, '================================================', '<br><hr>'), Char(13)+Char(10), '<br>')"
	
my_Conn.Execute strSQL1

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mych]

Jack,

Thanks for you help... what a difference an s makes!

I tried without the loop and got the following error

Argument data type ntext is invalid for argument 1 of replace function.

Looks like Im not going to be able to do it directly as the field type is ntext. I think I need to read the field into a string do the replace and then write it back. I'll try messing about but any extra pointers would be appreciated.

Thanks

 

[vongrunt]

Ack... I forgot about ntext thing...

What is max. length of data in Updates column?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mych]

Jack,

I have a table with 1500+ records at present. The field in question (Updates) is the only ntext field and the majority are under 8K in size but I rekon I have around 200 where the character count will exceed this.

This will be a one off exercise as the series of ==== are a legacy of data that was stored in a memo field in ACCESS.

Originally the data was in an ACCESS db and the updates were added to by concactanating the value of the Updates field with an audit line and the newupdate ending with a series of ====. I had introduced them as a seperator between updates. Giving this effect...

01/01/04 UserName
1st update... blah blah
blah blah.
========================
02/01/04 UserName
2nd update... blah blah
blah blah.
========================

Now that I have ported this over to SQL and am building a web based front end I need to do a replace to substitue <br> and <hr> where appropriate so that I maintain the format.

I may start looking at trying to convert the data while it is in ACCESS and then porting it over to SQL. But again if you have any other suggestion that would be great.

Thanks for your time.