Visual Basic
Python
Web Scripting
Java
Online Tools


Home

What's New

Articles

Code Downloads

Code Snippets

Message boards
Links
Tool Box

Books

Mailing List
Receive free code snippets and notices when this site is updated.

Contact

Customer Support System in ASP

If youíre doing business on the Internet you need to provide an easy way for your customers and potential customers to contact you.

Most sites do this with email. The solution presented in this article provides the following benefits to your customers over using a simple email system:

  • Customers donít have to leave the web browser in order to contact you.
  • They can check the status of their request online (whether or not you have viewed the message yet).
  • They can view your reply in a web browser as well as by email.

This solution also provides the web site owner with the benefits of:

  • Allowing questions and their replies to kept together.
  • Tracking response time (How long it has been between when you got the message to when you replied).
  • You can see what support personnel have said to your customers.

Customers can check the status of their message so they donít email you again to see if you got the first email.

Technologies used

To develop this system we use:

  • ASP Ė Active Server Pages
  • An Access Database
  • CDO Ė Collaboration Data Objects

CDO is a set of components included with NT Option Pack 4 that allow you to send and receive email using the SMTP (Simple Mail Transfer Protocol) Server also included with the option pack.

In this system we use the NewMail component of CDO to send email. It allows us to send email very easily as the code below illustrates. One thing to note about the NewMail component is that it is invalidated each time the Send method is called. This means you must use

Set objMail = CreateObject("CDONTS.NewMail") and refill the properties each time you want to send an email. All itís properties except for Version are write only.

Sending Email using the NewMail component.

Set objMail = CreateObject("CDONTS.NewMail")
objMail.From = "webmaster@developersdomain.com"
objMail.Subject = "Support Requested " & TicketNumber
objMail.Importance=1
objMail.Body = "There has been a request for support." 
objMail.To = "someonesemailaddress@voyager.co.nz"

objMail.Send

ASP Pages

This customer support application consists of the following ASP pages.

Title

Filename

Purpose

Add Request Page

addsupportrequest.asp

Customer enters their message and questions on this page.

Reply To Request Page

replytorequest.asp

For customer support to reply to the request made from the Add Request Page.

Login Page

Login.asp

The page that customer support users login to before they can use the Reply To Request Page.

Status of Support Request

checkrequeststatus.asp

For the customer to check whether their message has been viewed yet and to see the reply made.

The Support Files

There are two files that are used throughout this application.

  • Openconnection.asp
  • Validateemail.asp

Openconnection.asp

The OpenConnection function opens a connection to the database. It is placed it into a separate file to make it easy to change the connection string.

Openconnection.asp is included in every page of this application.

Openconnection.asp

<%
 Function OpenConnection()
   Set objConn = Server.CreateObject("ADODB.Connection")
 
  objConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &       
           Server.MapPath("support.mdb"))
   Set OpenConnection = objConn
 End Function
%>

Validateemail.asp

This file contains the function ValidateEmail that returns true if the email address passed has the correct format. That is it contains a "@", one or two "." and only alphanumeric characters.  Regular expressions are used to do this checking.

Validateemail.asp

<%
  Function ValidateEmail (strEmail)
    Dim myReg
    Set myReg = New RegExp
    myReg.IgnoreCase = True
    myReg.Pattern = "^[\w-\.]+@\w+\.\w+\.*\w+$"
    ValidateEmail = myReg.Test(strEmail)
  End Function
%>

The Database Schema

This support system uses a very simple access database. The table SupportRequests holds information about each request and the replies to it. Records are added to this table when a customer sends a request from the Add Request Page.

When someone from user support replies to a message the user that made the reply is recorded in the RepliedToBy field of SupportRequests.

The UserSupport table holds the details of each customer support user. When a support request is entered the users listed in this table may login and reply to the request.

Add Request Page Ė For Use by Customers


This is where is all starts.

The customer enters their details and clicks the "Send" button. When they click "Send" the following things happen.

  1. The Name, Subject, Message and Email fields are check to see if they where given and are correctly formatted.
  2. If the fields are correct, the information is added to the database.
  3. An email is sent to the address the customer gave to let them know the URL where they can get check the status of their request.
  4. An email is send to customer support (you) to tell them that someone has made a request and the URL that they can enter their reply.
  5. A message informing that their request is entered into the system and will be answered soon is displayed to the customer.

The details of how this is achieved are shown below. I have divided the code into segments.

Segment 1

Includes the support files to open database connections and validate the email address.

<html>

<head>

<title>Support Request</title>

</head>

<body bgcolor="#FFFFFF">

<!--#INCLUDE file="openconnection.asp"-->

<!--#INCLUDE file="validateemail.asp"-->

Segment 2

If the customer has clicked the "Send" button then a check is made to make sure all the required 
fields have been entered and are formatted correctly.

If there was an error it is stored into the ErrorMessage string.

<%

Dim ErrorMessage

If Not IsEmpty(Request.Form("Send")) Then

' Check that the data provided is valid

Name = Request.Form("Name")

Email = Request.Form("Email")

Subject = Request.Form("Subject")

Message = Request.Form("Message")

If Len(Name) = 0 Then ErrorMessage = "<p>Please enter your <b>name</b></p>"

If Not ValidateEmail(Email) Then ErrorMessage = ErrorMessage & _
"<p>You need to supply your <b>email</b> address so we can contact you</p>"

If Len(Subject) = 0 Then ErrorMessage = ErrorMessage & "<p>Please enter a <b>subject</b></p>"

If Len(Message) = 0 Then ErrorMessage = ErrorMessage & _
"<p>Please enter your <b>message</b></p>"

Segment 3

If no errors where created in segment 2 then a connection is opened to the database and a new row is inserted into the SupportRequests table. The Replace function is used to replace any quotation mark with two quotation marks to make it possible for the customer to enter quotation marks into their message.

After the support request is added another query executes to find the row with the greatest RequestID. This will be the row we have just added. The value of RequestID is placed in TicketNumber so we can tell the customer and customer support which record to look at.

The URL the customer should visit to view the status of their request is place into the RequestStatusURL variable.

If Len(ErrorMessage) = 0 Then

' Add the request to the database

Set objConn = OpenConnection()

objConn.Execute "INSERT INTO SupportRequests(Username,Name,Email,Subject,Message)" & _
"VALUES(""" & Username & """,""" & Name & _
""",""" & Email & """,""" & _
Replace(Subject,"""","""""") & """,""" & Replace(Message,"""","""""") & """)"

TicketNumber = objConn.Execute("SELECT MAX(RequestID) FROM SupportRequests")(0)

RequestStatusURL = "http://www.mywebsite.com/checkrequeststatus.asp?ticket=" & TicketNumber

Segment 4

This sends an email to the customer telling them that their request has been entered and where they can check itís status.

'Send Email To Person Requesting Support

Set objMail = CreateObject("CDONTS.NewMail")

objMail.From = "support@mybigcompany.com"

objMail.Subject = "Support Request " & TicketNumber

objMail.Importance=1

objMail.Body = "Your request for support has been sent. " & _
"To view the status of your request visit " & RequestStatusURL & _
vbcrlf & vbcrlf & "The Support Team"

objMail.To = Email

objMail.Send()

Segment 5

This segment sends an email to customer support giving them the URL that they can reply from.

It then displays a thank you screen to the customer and stops the processing of the page by calling Response.End.

'Send Email To User Support

Set objMail = CreateObject("CDONTS.NewMail")

objMail.From = "support@mybigcompany.com"

objMail.Subject = "Support Requested " & TicketNumber

objMail.Importance=1

objMail.Body = "There has been a request for support. " & _
"Please visit http://localhost/replytorequest.asp?ticket=" & TicketNumber & " to answer"

objMail.To = "support@mybigcompany.com"

objMail.Send()

 

objConn.Close

Set objConn = Nothing

%>

<h1>Thank You</h1>

Thank you for contacting us. You can view the status of your request by visiting our

<a href="<%=RequestStatusURL%>">request status page</a>.

<br>These details have been send to <%= Email %>.

</body>

</html>

<%

Response.End Ď Stop processing the page

End If

End If

%>

Segment 6

This segment will only run if the user hasnít yet clicked "Send" or if there was an error message.

If an attempt to send a message was made but some fields where incomplete the error message created in segment 2 is displayed. The fields from the last attempt are filled out.

<form method="POST" action="addsupportrequest.asp">

<h1>Support Request</h1>

<p>Fields marked with an <font color="#FF0000" size="1">*</font> are required.</p>

<%= ErrorMessage %>

<p>Username: <input type="text" name="Username" size="20" value="<%= Username %>"><br>

If already a customer.&nbsp;</p>

<p>Name: <input type="text" name="Name" size="20" value="<%= Name %>"> <font color="#FF0000" size="1">*</font></p>

<p>Email: <input type="text" name="Email" size="20" value="<%= Email %>"> <font color="#FF0000" size="1">*</font></p>

<p>Subject: <input type="text" name="Subject" size="20" value="<%= Subject %>">

<font color="#FF0000" size="1">*</font></p>

<p>Message: <font color="#FF0000" size="1">*</font><br>

<textarea rows="5" name="Message" cols="38"><%= Message %></textarea> </p>

<p><input type="submit" value="Send Request" name="Send"><input type="reset" value="Reset"></p>

</form>

</body>

</html>

Segment 7

This final segment closes the recordset and connection objects to conserve resources.

<% End If

rsSupportRequest.Close

Set rsSupportRequest = Nothing

objConn.Close

Set objConn = Nothing

%>

</body>

</html>

Adapting this script to your site

Change objMail.From in segment 3 to point to your email address.

Login Page Ė For Use by Customer Support

When a customer support user goes to the reply to request page, a check is made to see if the session variable "UserID" has an id in it. If not they are redirect to the login page.

When replytorequest.asp redirects the user to this page it passes the Ticket number along as well so that after they enter their username and password they can be direct back to where they want to be.

I have included below only the part of login.asp that does any real work. If the user has clicked the login button a database connection is opened and a query made to the UserSupport table to see if there are any users with the username and password given. If not an error message is returned to tell the person that their username or password where invalid.

If the username and password are correct the userís id is placed into a session variable.

The recordset and connection objects are then closed.

And finally if there was no error the user is redirect to the reply to request page.

Login.asp

<!--#INCLUDE file="openconnection.asp"-->
<%

TicketNumber = Request.Querystring("Ticket")

If Len(Request.Form("Login"))>0 Then ' Try and login

Set objConn = OpenConnection()

Set rsUser = objConn.Execute("SELECT UserSupportID FROM UserSupport WHERE Username=""" & Request.Form("Username") & """ AND Password=""" & Request.Form("Password") & """" )

If rsUser.EOF Then

ErrorMessage = "<p><b>Invalid Username or Password</b></p>"

Else

Session("UserID") = rsUser(0)

End If

 

rsUser.Close

Set rsUser = Nothing

objConn.Close

Set objConn = Nothing

If IsEmpty(ErrorMessage) Then Response.redirect "replytorequest.asp?ticket=" & TicketNumber

End If
%>

Check Request Page - For Use by Customers

This page is basically the same as the one above except that it:

  • Doesnít require the person viewing it to login and can be seen by anyone.
  • A reply canít be send from this page.

For this reason there is little point explaining the code as it is the same code with some lines removed.

Future Improvements

There are a number of things that could be done to improve on this system, like:

  • Providing a page that lists all unanswered messages.
  • Adding a password field to the SupportRequests table so that only the person that added the request can view itís status and reply. This is an important feature if sensitive information like passwords or credit card details are contained in the messages.
  • Sending the mail messages directly to the support users. You could decide which users to send the message to by picking them randomly or using a kind of round-robin system. If you where to do this it would be useful to be able to mark a customer support user as unavailable so that messages donít pile up in their inbox if their are away.
  • Provide statistics on how quickly requests are being responded to, number of responses by each support person, etc.

 

Setting up the Customer Support System

To use these scripts on your site you need to:

  1. Download the supporting materials for this article.
  2. Make the changes explained for each ASP file under the heading "Adapting this script to your site"
  3. Make sure the directory is available from the web and that the place where you place the database file has write permissions.
  4. Open up the database file and add a support user.
  5. Go to addsupportrequest.asp and try it out.

Although it is not required to use the scripts it is recommend that you place the database file into a directory where it canít be download across the net. If you do change itís location make sure you update openconnection.asp to point to it.

Related Links:

NewMail Object (CDONTS Library)

RegExp Object

MSDN HelpDesk Sample


Download code for the Customer Support Application
.

Download this article as a word document.

Tell a friend about this article