Classic ASP, VBScript Concatenation issue,

Question

Below is some Classic Asp VB Script that i am trying to alter so i can get it to run on my windows 2012 server. (I know its old don't ask :( ). This worked on our old 2003 box but the library it use to use is not supported in 2012. I am now using a stored procedure to send out this e-mail. The problem i am facing is that when i concatenate the sMailBody string i must have some ' or " syntax that is not allowing it to concatenate. When I Response.Write(strRSs) I get the string but all of the HTML renders.

EXEC [dbo].[MailPackage] 'test@test.com', 'tteesstt@ttessttt.com', 'tset' {visible HTML}

      for x = 0 to uBound(aRecipientID)
            '-- Plugin real content
            sMailBody = Replace(sMailBody,"{*~BACKGROUND~*}","'"Session("LiveURL")&"")
            sMailBody = Replace(sMailBody,"{*~HEADER~*}",Session("LiveURL")&"images/testing.jpg")
            sMailBody = Replace(sMailBody,"{*~BODY~*}",sBody)
            sMailBody = Replace(sMailBody,"{*~URL~*}",Session("LiveURL") & "/viewpackage.asp?p=" & sPackageKey & "&u=" & aRecipientID(x) & "&s=" & nSendID&"'")

     Dim strRS 
    strRS = "EXEC [dbo].[MailPackage] " + "'" +  aRecipientEmail(x) + "', '" + Session("FromAddress") + "',  '" + sSubject + "', '" + sMailBody + "'"
   Response.Write(sMailBody)
   Response.Write(strRS)
    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open  Session("ConnectionString")

    Conn.Execute strRS

How can i correct my syntax so i can pass sMailBody as my message body into my stored procedure.

Answer 1

Assuming you want to replace the placeholder {*~BACKGROUND~*} in sMailBody with the content of Session("LiveURL"):

change

sMailBody = Replace(sMailBody,"{*~BACKGROUND~*}","'"Session("LiveURL")&"")

to

sMailBody = Replace(sMailBody, "{*~BACKGROUND~*}", Session("LiveURL"))

Basic rules:

1. Replace need 3 string parameters
2. A double quote in a string literal must be escaped by ""
3. VBScript does not interpolate/splice in variable content into string literals
4. concatenating the empty string - & "" - is a waste of time

The other Replace lines compile, but isn't there a Next missing?

Update thanks to @Lankymart's comment:

... Assuming you want to replace the placeholder {*~BACKGROUND~*} in sMailBody with the single-quoted content of Session("LiveURL") ...

The error in the original code and my probably wrong interpretation of jackncode's intention shows that building strings by concatenation adds so much noise to the expression that it gets messed up easily. See the millions of questions wrt putting quoted pathes and other parameters into command lines for .Run or .Exec.

For such cases it pays to write functions that do the 'embracing' so that you DRY the tangle of funny letters:

>> Function q(s) : q = "'" & s & "'" : End Function
>> Function qq(s) : qq = """" & s & """" : End Function
>> s = "x"
>> t = "<tag attr=@>"
>> WScript.Echo Replace(t, "@", q(s))
>> WScript.Echo Replace(t, "@", qq(s))
>>
<tag attr='x'>
<tag attr="x">

(cf. here)