Looped string replace in txt file from recordset

Trying to replace text with database entries

[jon173]

Here's a simple problem that has been driving me nuts for days. I've built an email marketing app in ASP and SQL. Users can upload their HTML file and send. Part of the process is to process the HTML and pick out all the "a hrefs" using RegExp which is no problem. I pick these out of the HTML file and write them to the database using the newslletterID as the common binding factor to ID these hrefs.

The next step is the problem. Using the ASP fileSystem I open the HTML file on the sever and write the HTML to a variable. Then open a recordset and pull the hrefs out of the databse that match this newsletterID. The object of the whole exercise is to find the original a hrefs in the HTML and replace them with the new hrefs which contain tracking information to track what links the user clicked on etc.

When I try to create a loop to replace the oldhrefs with the newhrefs I can not get it to loop and replace with the new links. Here's a sample of the code..

set objFS = Server.CreateObject("Scripting.FileSystemObject")
dim vartextfile
vartextfile = "c:\inetpub\wwwroot\expo\Campaign_archives\expoDirect41020200313417.htm"
set objFile = objFS.GetFile(vartextfile)
set objTS = objFile.OpenAsTextStream(ForReading, TristateFalse)

strSearchThis = objTS.Read(objFile.Size)

for x=1 to 4
'
while not gethtmltextfiles.EOF
newhtml = (gethtmltextfiles("tl_URLnew"))
oldhtml = (gethtmltextfiles("tl_URL" ))

strHTMLSOURCE1 = Replace (strSearchThis, oldhtml, newhtml )

gethtmltextfiles.MoveNext
wend
next

there are 4 records in the Db that match the newsletterID and I just hacked the For x loop to return 4 loops for now.

Can anybody offer suggestions as to the best way to achieve this looped recordset/text file replace function?

[TheDave]

What does the x do when your going into another loop after it?

For x = 1 to 4
next

is a loop, as is:

while not rs.EOF
rs.MoveNext
wend

So you have 2 loops going there, and I don't see that x doing much, like there's no arrays or anything. Perhaps the problem is somewhere in there..

[jon173]

I've been trying different things when I use the For X=?loop I get a different result, but I can not get to replace all the a hrefs. I just put in the For x=4 as I know there are 4 links in this HTML file

[TheDave]

Where does the data for oldhtml and newhtml come from? Shouldnt that be an array, simplified example:

dim old(2)
dim new(2)

old(0) = "oldlink.html"
old(1) = "anotheroldlink.html"

new(0) = "newlink.html"
new(1) = "anothernewlink.html"

c = 0
while c < ubound(old)
strHTMLSOURCE1 = Replace (strSearchThis, old(c), new(c))
c = c + 1
wend

[jon173]

The data comes from the database and each newsletter will have a different amount of links to replace so using a static array is not an option ( I think!) When the RegExp is used to pull out the hrefs from the HTML page it then writes them to the database, a new record for each Href link. To get the links to replace, I open the recordset and pull out the records that match the newsletterID.
ie: SELECT oldhtml,newhtml from tbl_transactions WHERE
newsletterID = 123

then put the records into a variable

var_oldhtml = objRs("oldhtml")
var_newdhtml = objRs("newhtml")

then I try to loop through the recordset replaceing the instance of the oldhtml in the text file with the newhtml until all the links are replaced.

while not gethtmltextfiles.EOF
newhtml = gethtmltextfiles.Fields.item("tl_URLnew").value
oldhtml = gethtmltextfiles.Fields.item("tl_URL").value

strHTMLSOURCE1 = (Replace(strSearchThis, oldhtml , newhtml ))

gethtmltextfiles.MoveNext
wend

only replaces the last record in the dataset. If I have 4 links the last one is replaced but the other 3 are not?

[jon173]

that last post was messed up the code should have been

var_oldhtml = objRs("oldhtml")
var_newdhtml = objRs("newhtml")

then I try to loop through the recordset replaceing the instance of the oldhtml in the text file with the newhtml until all the links are replaced.

while not objRs.EOF

strHTMLSOURCE1 = (Replace(strSearchThis, oldhtml , newhtml ))

objRs.MoveNext
wend

sorry!

[TheDave]

Ok, try this, and loose the for x = loop

while not objRs.EOF

var_oldhtml = objRs("oldhtml")
var_newdhtml = objRs("newhtml")

strHTMLSOURCE1 = (Replace(strSearchThis, var_oldhtml , var_newhtml ))

objRs.MoveNext
wend

[jon173]

I used this logic the first time I created the script and it only replaces the last record in the recordset. It will modify the text file and replace the last link, but ignores the rest of them - and some HTML newsletters may have 20 links!

[jon173]

(old)http://www.computerexpo.com.au/link1.asp
(new)http://www.computerexpo.com.au/admin/trans.asp?
transid=413&nlid=515&userid=¦userid¦

(old)http://www.computerexpo.com.au/link2.asp
(new)http://www.computerexpo.com.au/admin/trans.asp?transid=414&nlid=515&userid=¦userid¦

(old)http://www.computerexpo.com.au/link3.asp
(new)http://www.computerexpo.com.au/admin/trans.asp?transid=415&nlid=515&userid=¦userid¦

(old)http://www.computerexpo.com.au/link4.asp
(new)http://www.computerexpo.com.au/admin/trans.asp?transid=416&nlid=515&userid=¦userid¦

this is what the recordset returns to get the oldhtml and the newhtml. There are 4 links in this newsletter

[TheDave]

What you're doing is performing the replace on the original string each time. You need to perform the replace, set the original to be the result of the replace, and then perform the next replace on that. It's not really that complex tho:

tempstring = strSearchThis

while not objRs.EOF

var_oldhtml = objRs("oldhtml")
var_newdhtml = objRs("newhtml")

tempstring = (Replace(tempstring, var_oldhtml , var_newhtml ))

objRs.MoveNext
wend

strHTMLSOURCE1 = tempstring

[jon173]

I just received another reply from someone else helping me with this simple issue. Here is the answer. I was using a new variable to hold the textfile in when I ought to have kept the variable name the same as the replace() - here is the code that works' Thanks for the help with this it's good to know this community is here.
My code
Do while not gethtmltextfiles.EOF
newhtml = gethtmltextfiles("tl_URLnew")
oldhtml = gethtmltextfiles("tl_URL")
strHtmlSource1= Replace(strSearchThis, oldhtml,newhtml)
gethtmltextfiles.MoveNext
Loop
response.write strHtmlSource1

"The correct code"
Do while not gethtmltextfiles.EOF
newhtml = gethtmltextfiles("tl_URLnew")
oldhtml = gethtmltextfiles("tl_URL")
strSearchThis= Replace(strSearchThis, oldhtml,newhtml)
gethtmltextfiles.MoveNext
Loop
response.write strSearchThis