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]]

JP SIngh wrote:

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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[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" <no**@none.com> wrote in message news:uJ**************@TK2MSFTNGP09.phx.gbl...

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.

[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]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

JP SIngh wrote:

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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[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

JP SIngh wrote:

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]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

JP SIngh wrote:

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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Patrice]

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

Patrice

--

"JP SIngh" <no**@none.com> a écrit dans le message de
news:u9**************@TK2MSFTNGP09.phx.gbl...

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]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

JP SIngh wrote:

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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.