Building Dynamic Strings from Database Values

[JP SIngh]

Hi All

Hope someone can help us with an issue here.

We use an ASP application in which we send out emails using cdo for various
events.

At present the email text is hard coded into the code. for example

strMessage = "Dear " & rs("firstname")
strMessage = strMessage & "Your request has been approved with reference id
" & rs("id")

This causes an administration overhead as everytime the text of the message
needs to be changed we need to alter the code to incorporate text or
database values.

What we want is to store the message text into a table so it can be altered
by the application administrators through GUI by adding the text through
freetext entry and field names though a dropdown.

and then use something like

strFirstName = rs("firstname")

Set rst = Server.CreateObject("ADODB.Recordset")
sql = "select * from templates where id = 1"
rst.Open sql, conn
strText = rst("templatedata")

Response.write "templatedate " & strText

Now is the strText Contains the text Dear {FirstName} This is a contract
between the two parties.

How do I change all occurances of the {FirstName} with strFirstName such
that if firstname is "SIMON" the strText should be

"Dear SIMON This is a contract between the two parties."

Any help is appreciated.

 

[Bob Barrows [MVP]]

Set rst = Server.CreateObject("ADODB.Recordset")
sql = "select * from templates where id = 1"

Don't use selstar: http://www.aspfaq.com/show.asp?id=2096

rst.Open sql, conn
strText = rst("templatedata")

Response.write "templatedate " & strText

Now is the strText Contains the text Dear {FirstName} This is a
contract between the two parties.

How do I change all occurances of the {FirstName} with strFirstName
such that if firstname is "SIMON" the strText should be

"Dear SIMON This is a contract between the two parties."

Use the Replace function:

strText = Replace(strText,"{FirstName}", strFirstName)

Bob Barrows

 

[Steven Burn]

strText = Replace(rst("templatedata"), "FIRSTNAME", rst("firstname"))

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

 

[JP SIngh]

Great. Thanks a quick reply.

Now I have potentially 20-30 field names in the same message. Do I have to
have 30 replace statements or can i write something generic

the string name in the message will always correspond to the fieldname in
the database

for example

{FirstName} - rs("FirstName")
{LastName} - rs("LastName")
{Department} - rs("Department")

 

[Bob Barrows [MVP]]

You'll need to do a loop:

for each fld in rs.Fields
strText=Replace(strText,"{" & fld.Name & "}", fld.Value)
next

Bob Barrows

 

[Patrice]

You could also use the standard .NET placeholder {0} for first parameter,
{1} for second etc...

Patrice