Building Dynamic Strings from Database Values

J

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.
 
B

Bob Barrows [MVP]

JP said:
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
 
J

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")
 
B

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
 
P

Patrice

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

Patrice
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,899
Latest member
RodneyMcAu

Latest Threads

Top