automating email with links to records just submitted to an SQL DB using asp.net (vb)

[Jimbo]

Hello

I am currently designing an internal ordering system for IT equipment.

I am designing it in ASP.NET (vb) using Visual Studio 2003 and using
Microsoft SQL Server

I have got the system to add the order into the database and assign it
a unique ID ("EITReqCode "), and made it e-mail the it purchaser via
SMTP automatically with a link to the "Authorisat ion" page where they
are to cost and authorise the IT request. What i want to be able to do
now, is add to that link the unique ID of the order just submitted so
when the purchaser opens the e-mail they will see the order which was
submitted relating to that specific email.

I am quite new to this and quite impressed with what i have achieved
single-handidly but now require some assistance.

Can anyone help me and advise me how to perform this function.

If you want to see some of my scripting already i will post it up.

Many thanks in advance.

James O'Neill
BSc (Hons) Business Information Systems Management

[Gary]

Jimbo,

Are you using SQLMail or the System.Web.Mail namespace to send the
email?

Gary

Jimbo wrote:

Hello

I am currently designing an internal ordering system for IT equipment.

I am designing it in ASP.NET (vb) using Visual Studio 2003 and using
Microsoft SQL Server

I have got the system to add the order into the database and assign it
a unique ID ("EITReqCode "), and made it e-mail the it purchaser via
SMTP automatically with a link to the "Authorisat ion" page where they
are to cost and authorise the IT request. What i want to be able to do
now, is add to that link the unique ID of the order just submitted so
when the purchaser opens the e-mail they will see the order which was
submitted relating to that specific email.

I am quite new to this and quite impressed with what i have achieved
single-handidly but now require some assistance.

Can anyone help me and advise me how to perform this function.

If you want to see some of my scripting already i will post it up.

Many thanks in advance.

James O'Neill
BSc (Hons) Business Information Systems Management

[Jimbo]

I am using the System.Web.Mail namespace within asp.net, and using
SurgeMail as a testing SMTP server (on local machine).

Code im using is below (with alterations for confidentiality )

Sub BTNSubmit_Click (ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles BTNSubmit.Click
'Create an instance of the MailMessage class
Dim objMM As New MailMessage

'Set the properties
objMM.To = "it************ @companyname.co .uk"
objMM.From = "it*********@co mpany.co.uk"

' Send the email in text format
objMM.BodyForma t = MailFormat.Text
'(to send HTML format, change MailFormat.Text to
MailFormat.Html )

'Set the priority - options are High, Low, and Normal
objMM.Priority = MailPriority.No rmal

' Set the subject
objMM.Subject = "eIT Request"
'i want to be able to add the request number on the subject

' Set the body - use VbCrLf to insert a carriage return
objMM.Body = "A new IT request was submitted at " +
DateTime.Now + " please click below to review and authorise" & vbCrLf &
_
"http://rcsukbaswks0099/webapplication2/authorisation.a spx"

SmtpMail.SmtpSe rver = "127.0.0.1"

SmtpMail.Send(o bjMM)

Response.Redire ct("confirmed.a spx")

======end of code=========== =

Gary wrote:

Jimbo,

Are you using SQLMail or the System.Web.Mail namespace to send the
email?

Gary

[bruce barker \(sqlwork.com\)]

the typical approach is to use a querystring in the url of the linek

http://mysite.com/orders/vieworder.aspx? id=<orderid>

then in the code behind you check the Request.QuerySt ring["id"] for the
value passed. try not to make the id sequential, or they are easy to hack
(add one to your number and see someone else order)

-- bruce (sqlwork.com)
"Jimbo" <ji**********@g mail.com> wrote in message
news:11******** *************@x 69g2000cwx.goog legroups.com...

Hello

I am currently designing an internal ordering system for IT equipment.

I am designing it in ASP.NET (vb) using Visual Studio 2003 and using
Microsoft SQL Server

I have got the system to add the order into the database and assign it
a unique ID ("EITReqCode "), and made it e-mail the it purchaser via
SMTP automatically with a link to the "Authorisat ion" page where they
are to cost and authorise the IT request. What i want to be able to do
now, is add to that link the unique ID of the order just submitted so
when the purchaser opens the e-mail they will see the order which was
submitted relating to that specific email.

I am quite new to this and quite impressed with what i have achieved
single-handidly but now require some assistance.

Can anyone help me and advise me how to perform this function.

If you want to see some of my scripting already i will post it up.

Many thanks in advance.

James O'Neill
BSc (Hons) Business Information Systems Management

[Jimbo]

Yea i was thought thats how it should look, but im new to this and am
unsure how to get the id automatically into the url in the email.

Could you use my code then give me an example of what it should look
like.

Where abouts would i put the Request.QuerySt ring["id"] ?

We have looked at the ID, and went with sequential as this link is not
given to any employees, only the IT Co-Ordinator.

We have named the id 'EITReqCode', which is in a table called
'IT_Request'.

Many thanks
bruce barker (sqlwork.com) wrote:

the typical approach is to use a querystring in the url of the linek

http://mysite.com/orders/vieworder.aspx? id=<orderid>

then in the code behind you check the Request.QuerySt ring["id"] for the
value passed. try not to make the id sequential, or they are easy to hack
(add one to your number and see someone else order)

-- bruce (sqlwork.com)
"Jimbo" <ji**********@g mail.com> wrote in message
news:11******** *************@x 69g2000cwx.goog legroups.com...

Hello

I am currently designing an internal ordering system for IT equipment.

I am designing it in ASP.NET (vb) using Visual Studio 2003 and using
Microsoft SQL Server

I have got the system to add the order into the database and assign it
a unique ID ("EITReqCode "), and made it e-mail the it purchaser via
SMTP automatically with a link to the "Authorisat ion" page where they
are to cost and authorise the IT request. What i want to be able to do
now, is add to that link the unique ID of the order just submitted so
when the purchaser opens the e-mail they will see the order which was
submitted relating to that specific email.

I am quite new to this and quite impressed with what i have achieved
single-handidly but now require some assistance.

Can anyone help me and advise me how to perform this function.

If you want to see some of my scripting already i will post it up.

Many thanks in advance.

James O'Neill
BSc (Hons) Business Information Systems Management

[Gary]

Jim,

What I usually do for emailing is create an XML file and call it, say,
email.config (naming it with a .config extension prevents a user from
pulling it up through the website). In the XML file I define a
template for any emails I want to send. I use something like:

<?xml version="1.0" encoding="utf-8"?>
<email server="smtpser ver">
<template Id="EmailMessag e1">
<from><![CDATA[]]></from>
<cc><![CDATA[]]></cc>
<subject><![CDATA[]]></subject>
<message format="HTML">< ![CDATA[
<html><body>Ema il Message text to [~USER_NAME~]</body></html>]]>
</message>
</template>
</email>

**Take note of the [~USER_NAME~] token in the body. You can create as
many of these nodes as you need, for each type of email you want to
send.

Then I have code like the following to send the actual email:
public class TemplateEmail {
public static void Send(string templatePath, string templateID, string
recipients, Hashtable tokens)
{
//** Load the email configuration template
XmlDocument template = new XmlDocument();
template.Load(t emplatePath);

//** Pull out the template
XPathNavigator nav = template.Docume ntElement.Creat eNavigator();
string smtpServer = nav.GetAttribut e("SMTPServer ", String.Empty);
MailMessage msg = new MailMessage();

//** Process the message elements
XmlNode emailNode =
template.Docume ntElement.Selec tSingleNode(str ing.Format("Tem plate[@Id='{0}']",
templateID));

if (emailNode == null) throw new Exception("Inva lid or missing email
template identifier");
nav = emailNode.Creat eNavigator();
if (!nav.MoveToFir stChild()) throw new Exception("Inva lid email
template - no properties defined");

string token = string.Empty;
while (true)
{
switch (nav.Name)
{
case "From":
msg.From = nav.Value;
break;
case "Cc":
msg.Cc = nav.Value;
break;
case "Bcc":
msg.Bcc = nav.Value;
break;
case "Subject":
//** Perform the subject token replacements
msg.Subject = ReplaceTokens(n av.Value, tokens);
break;
case "Body":
//** Perform the subject token replacements
msg.Body = ReplaceTokens(n av.Value, tokens);
break;
default:
break;
}

if (!nav.MoveToNex t()) break;
}

//** Send the message
msg.To = recipients;
SmtpMail.SmtpSe rver = smtpServer;
SmtpMail.Send(m sg);
}

private static string ReplaceTokens(s tring source, Hashtable tokens)
{
string result = source;
foreach (string key in tokens.Keys)
{
result = result.Replace( key, tokens[key].ToString());
}

return result;
}
}

And then the calling program passes in a hashtable that contains the
tokens to be replaced, along with the value to replace it with. A call
for this case would look like this:
Hashtable properties = new Hashtable();
properties.add( "[~USER_NAME~]", "ggalehouse ");
Email.Send(path _to_email.confi g_file, "EmailMessage1" , properties);
//EmailMessage one is the ID attribute within the email.config file.

I hope this helps. I've used this on numerous projects with great
results.

Thanks,
Gary

[Jimbo]

Thanks Gary, I will try this out and let you know what happens!

Spent the whole day trying to do this so might give it a break and try
again tomorrow :)

Gary wrote:

Jim,

What I usually do for emailing is create an XML file and call it, say,
email.config (naming it with a .config extension prevents a user from
pulling it up through the website). In the XML file I define a
template for any emails I want to send. I use something like:

<?xml version="1.0" encoding="utf-8"?>
<email server="smtpser ver">
<template Id="EmailMessag e1">
<from><![CDATA[]]></from>
<cc><![CDATA[]]></cc>
<subject><![CDATA[]]></subject>
<message format="HTML">< ![CDATA[
<html><body>Ema il Message text to [~USER_NAME~]</body></html>]]>
</message>
</template>
</email>

**Take note of the [~USER_NAME~] token in the body. You can create as
many of these nodes as you need, for each type of email you want to
send.

Then I have code like the following to send the actual email:
public class TemplateEmail {
public static void Send(string templatePath, string templateID, string
recipients, Hashtable tokens)
{
//** Load the email configuration template
XmlDocument template = new XmlDocument();
template.Load(t emplatePath);

//** Pull out the template
XPathNavigator nav = template.Docume ntElement.Creat eNavigator();
string smtpServer = nav.GetAttribut e("SMTPServer ", String.Empty);
MailMessage msg = new MailMessage();

//** Process the message elements
XmlNode emailNode =
template.Docume ntElement.Selec tSingleNode(str ing.Format("Tem plate[@Id='{0}']",
templateID));

if (emailNode == null) throw new Exception("Inva lid or missing email
template identifier");
nav = emailNode.Creat eNavigator();
if (!nav.MoveToFir stChild()) throw new Exception("Inva lid email
template - no properties defined");

string token = string.Empty;
while (true)
{
switch (nav.Name)
{
case "From":
msg.From = nav.Value;
break;
case "Cc":
msg.Cc = nav.Value;
break;
case "Bcc":
msg.Bcc = nav.Value;
break;
case "Subject":
//** Perform the subject token replacements
msg.Subject = ReplaceTokens(n av.Value, tokens);
break;
case "Body":
//** Perform the subject token replacements
msg.Body = ReplaceTokens(n av.Value, tokens);
break;
default:
break;
}

if (!nav.MoveToNex t()) break;
}

//** Send the message
msg.To = recipients;
SmtpMail.SmtpSe rver = smtpServer;
SmtpMail.Send(m sg);
}

private static string ReplaceTokens(s tring source, Hashtable tokens)
{
string result = source;
foreach (string key in tokens.Keys)
{
result = result.Replace( key, tokens[key].ToString());
}

return result;
}
}

And then the calling program passes in a hashtable that contains the
tokens to be replaced, along with the value to replace it with. A call
for this case would look like this:
Hashtable properties = new Hashtable();
properties.add( "[~USER_NAME~]", "ggalehouse ");
Email.Send(path _to_email.confi g_file, "EmailMessage1" , properties);
//EmailMessage one is the ID attribute within the email.config file.

I hope this helps. I've used this on numerous projects with great
results.

Thanks,
Gary

[Jimbo]

Gary

Sorry to be an idiot, but could u explain the [~USER_NAME~] function to
me again...

If i want to have different emails set up for various recipients do i
do it here? And do i just repeat the code or repeat the line containing
the [~USER_NAME~] node for this?

Thanks

<?xml version="1.0" encoding="utf-8"?>
<email server="smtpser ver">
<template Id="EmailMessag e1">
<from><![CDATA[]]></from>
<cc><![CDATA[]]></cc>
<subject><![CDATA[]]></subject>
<message format="HTML">< ![CDATA[
<html><body>Ema il Message text to [~USER_NAME~]</body></html>]]>
</message>
</template>
</email>

**Take note of the [~USER_NAME~] token in the body. You can create as
many of these nodes as you need, for each type of email you want to
send.

Jimbo wrote:
Thanks Gary, I will try this out and let you know what happens!

Spent the whole day trying to do this so might give it a break and try
again tomorrow :)

Gary wrote:

Jim,

What I usually do for emailing is create an XML file and call it, say,
email.config (naming it with a .config extension prevents a user from
pulling it up through the website). In the XML file I define a
template for any emails I want to send. I use something like:

<?xml version="1.0" encoding="utf-8"?>
<email server="smtpser ver">
<template Id="EmailMessag e1">
<from><![CDATA[]]></from>
<cc><![CDATA[]]></cc>
<subject><![CDATA[]]></subject>
<message format="HTML">< ![CDATA[
<html><body>Ema il Message text to [~USER_NAME~]</body></html>]]>
</message>
</template>
</email>

**Take note of the [~USER_NAME~] token in the body. You can create as
many of these nodes as you need, for each type of email you want to
send.

Then I have code like the following to send the actual email:
public class TemplateEmail {
public static void Send(string templatePath, string templateID, string
recipients, Hashtable tokens)
{
//** Load the email configuration template
XmlDocument template = new XmlDocument();
template.Load(t emplatePath);

//** Pull out the template
XPathNavigator nav = template.Docume ntElement.Creat eNavigator();
string smtpServer = nav.GetAttribut e("SMTPServer ", String.Empty);
MailMessage msg = new MailMessage();

//** Process the message elements
XmlNode emailNode =
template.Docume ntElement.Selec tSingleNode(str ing.Format("Tem plate[@Id='{0}']",
templateID));

if (emailNode == null) throw new Exception("Inva lid or missing email
template identifier");
nav = emailNode.Creat eNavigator();
if (!nav.MoveToFir stChild()) throw new Exception("Inva lid email
template - no properties defined");

string token = string.Empty;
while (true)
{
switch (nav.Name)
{
case "From":
msg.From = nav.Value;
break;
case "Cc":
msg.Cc = nav.Value;
break;
case "Bcc":
msg.Bcc = nav.Value;
break;
case "Subject":
//** Perform the subject token replacements
msg.Subject = ReplaceTokens(n av.Value, tokens);
break;
case "Body":
//** Perform the subject token replacements
msg.Body = ReplaceTokens(n av.Value, tokens);
break;
default:
break;
}

if (!nav.MoveToNex t()) break;
}

//** Send the message
msg.To = recipients;
SmtpMail.SmtpSe rver = smtpServer;
SmtpMail.Send(m sg);
}

private static string ReplaceTokens(s tring source, Hashtable tokens)
{
string result = source;
foreach (string key in tokens.Keys)
{
result = result.Replace( key, tokens[key].ToString());
}

return result;
}
}

And then the calling program passes in a hashtable that contains the
tokens to be replaced, along with the value to replace it with. A call
for this case would look like this:
Hashtable properties = new Hashtable();
properties.add( "[~USER_NAME~]", "ggalehouse ");
Email.Send(path _to_email.confi g_file, "EmailMessage1" , properties);
//EmailMessage one is the ID attribute within the email.config file.

I hope this helps. I've used this on numerous projects with great
results.

Thanks,
Gary

[Gary]

Jim,

I was out of the office today, so I just saw your post. I sorta threw
that code out there without much explanation, so don't worry about not
following all of it.

To make a more specific analogy to your application, you could create
an email template like this:

<?xml version="1.0" encoding="utf-8"?>
<email server="smtpser ver">
<template Id="OrderConfir mation">
<from><![CDATA[em***@domain.co m]]></from>
<cc><![CDATA[]]></cc>
<subject><![CDATA[Order confirmation]]></subject>
<message format="HTML">< ![CDATA[
<html><body>
Order confirmation for: [~ORDER_ID~]
</body></html>]]>
</message>
</template>
</email>

Create the TemplateEmail class (code below) in a class library below
and add a reference to it in your project.

What you would do to send the email when an order is submitted is
create a function like:

Private Sub SendOrderConfir mation(orderID as string)
Dim properties As Hashtable = new Hashtable()
properties.add( "[~ORDER_ID~]", orderID);

//EmailMessage1 one is the ID attribute within the email.config file.
TemplateEmail.S end(path_to_ema il.config_file, "OrderConfirmat ion",
properties);
End Sub

So each time an order was submitted you would call
SendOrderConfir mation with the order identifier, and it would
dynamically replace the [~ORDER_ID~] token with the provided orderID.

Let me know if this isn't clear.

Thanks,
Gary

Then I have code like the following to send the actual email:
public class TemplateEmail {
public static void Send(string templatePath, string templateID, string
recipients, Hashtable tokens)
{
//** Load the email configuration template
XmlDocument template = new XmlDocument();
template.Load(t emplatePath);

//** Pull out the template
XPathNavigator nav = template.Docume ntElement.Creat eNavigator();
string smtpServer = nav.GetAttribut e("SMTPServer ", String.Empty);
MailMessage msg = new MailMessage();

//** Process the message elements
XmlNode emailNode =
template.Docume ntElement.Selec tSingleNode(str ing.Format("Tem plate[@Id='{0}']",
templateID));

if (emailNode == null) throw new Exception("Inva lid or missing email
template identifier");
nav = emailNode.Creat eNavigator();
if (!nav.MoveToFir stChild()) throw new Exception("Inva lid email
template - no properties defined");

string token = string.Empty;
while (true)
{
switch (nav.Name)
{
case "From":
msg.From = nav.Value;
break;
case "Cc":
msg.Cc = nav.Value;
break;
case "Bcc":
msg.Bcc = nav.Value;
break;
case "Subject":
//** Perform the subject token replacements
msg.Subject = ReplaceTokens(n av.Value, tokens);
break;
case "Body":
//** Perform the subject token replacements
msg.Body = ReplaceTokens(n av.Value, tokens);
break;
default:
break;
}

if (!nav.MoveToNex t()) break;
}

//** Send the message
msg.To = recipients;
SmtpMail.SmtpSe rver = smtpServer;
SmtpMail.Send(m sg);
}

private static string ReplaceTokens(s tring source, Hashtable tokens)
{
string result = source;
foreach (string key in tokens.Keys)
{
result = result.Replace( key, tokens[key].ToString());
}

return result;
}
}