ASP/SQL/Email Question

[bpw22az]

Bare with me guys. I will try and make this short and to the point. I have a website that allows students to check their admission status by submitting their email address which in turn sends them and our admissions office an email regarding their status in our SQL database. The ASP script that runs pulls all information from one table.

There are three possible status types; 'AC (App Complete), AI (App Incomplete), and UR (Under Review)'. If a student happens to be tagged as AC or UR an email is sent letting him or her know their status.

Now if the student is listed as AI, the email generated should list fields that have not been completed (described below in the code). As of right now the email is generated and sent, although fields are never listed. Just a blank space beneath "You are currently missing:".

I threw in some response.write statements to ensure data was being pulled from the database, which it appears to be doing.

Setup: Website (FrontPage), SQL 2005, MS Access 2003

Any suggestions would be great. Thanks!

<% @LANGUAGE = VBScript %>
<%
Option Explicit
Response.Expires = -1
Dim Applicant
Set Applicant = Request.Form("txtEmailAddress")
Dim objRS
Dim strQuery
Set Session("DB") = Server.CreateObject("ADODB.Connection")
    Session("DB").Open("OSC")
 
strQuery = "SELECT First_Name, Citizenship, College_1, College_2, College_3, GRE_Score_Date, TOEFLScore, "
strQuery = strQuery & "Transcript_1_Date, Transcript_2_Date, Transcript_3_Date, Reference__1, Reference__2, Reference_3, Reference_4, "
strQuery = strQuery & "Letter_1_Date, Letter_2_Date, Letter_3_Date, Letter_4_Date, ApplicationStatus "
strQuery = strQuery & "FROM tblAdmissionsData WHERE Email = '" & Applicant & "';"
 
Set objRS = Session("DB").Execute(strQuery)
 
If objRS.EOF = TRUE Then
response.write ("<p align='center'><a target='_self' href='http://www.college.edu'>")
response.write ("<img alt='College' src='images/Banner.jpg' border='0'></a></p>")
response.write ("There is no record with that email address in the database.")
Else
 
Dim FName
Set FName = objRS(0)
Dim Citizen
Set Citizen = objRS(1)
Dim College1
Set College1 = objRS(2)
Dim College2
Set College2 = objRS(3)
Dim College3
Set College3 = objRS(4)
Dim GRE
Set GRE = objRS(5)
Dim TOEFL
Set TOEFL = objRS(6)
Dim Tran1
Set Tran1 = objRS(7)
Dim Tran2
Set Tran2 = objRS(8)
Dim Tran3
Set Tran3 = objRS(9)
Dim Ref1
Set Ref1 = objRS(10)
Dim Ref2
Set Ref2 = objRS(11)
Dim Ref3
Set Ref3 = objRS(12)
Dim Ref4
Set Ref4 = objRS(13)
Dim RefLtr1
Set RefLtr1 = objRS(14)
Dim RefLtr2
Set RefLtr2 = objRS(15)
Dim RefLtr3
Set RefLtr3 = objRS(16)
Dim RefLtr4
Set RefLtr4 = objRS(17)
Dim AdStatus
Set AdStatus = objRS(18)
 
Dim Body
Dim Mail
Set Mail = CreateObject("CDONTS.NewMail")
Mail.From = "[e-mail]"
Mail.To = Applicant
Mail.Bcc = "[e-mail]"
Mail.Subject = "Your Admission Status"
 
Body = Body & "Greetings " & FName & "!" & vbCRLF & vbCRLF
Select Case AdStatus
    case "AC"
        Body = Body & "Your application to the College has been received and is complete." & vbCRLF
    case "AI"
        Body = Body & "Your application has been received but is incomplete." & vbCRLF
        Body = Body & "You are currently missing:" & vbCRLF & vbCRLF
            'GRE Report
                If IsNull(GRE) Then
                Body = Body & "GRE Score Report" & vbCRLF
                Else
                End If
            'TOEFL Score
                If Citizen <> "USA" AND IsNull(TOEFL) Then
                Body = Body & "TOEFL Score" & vbCRLF
                Else
                End If
            'Reference Letters
                If Citizen = "USA" Then 'Main loop
                    If IsNull(RefLtr1) AND IsNull(RefLtr2) Then
                    Body = Body & "2 Reference Letters" & vbCRLF
                    ElseIf IsNull (RefLtr2) Then
                    Body = Body & "A Reference Letter has been received from " & Ref1 & ". One additional letter is required." & vbCRLF
                    End If
                Else 'Main loop
                    If IsNull(RefLtr1) AND IsNull(RefLtr2) Then
                    Body = Body & "2 Reference Letters" & vbCRLF
                    ElseIf IsNull(RefLtr2) Then
                    Body = Body & "A Reference Letter has been received from " & Ref1 & ". One additional letter is required." & vbCRLF
                    End If
                End if 'Main loop
            'Transcripts
                If (IsNull(College1)=FALSE) AND IsNull(Tran1) Then
                Body = Body & "Transcript for " & College1 & vbCRLF
                Else
                End If
                If (IsNull(College2)=FALSE) AND IsNull(Tran2) Then
                Body = Body & "Transcript for " & College2 & vbCRLF
                Else
                End If
                If (IsNull(College3)=FALSE) AND IsNull(Tran3) Then
                Body = Body & "Transcript for " & College3 & vbCRLF
                Else
                End If
        Body = Body & vbCRLF & "Please note the deadline for submission of all application materials for international applicants is December 1." & vbCRLF
    case "UR"
        Body = Body & "Your application to the College has been forwarded to the Admissions Committee for review." & vbCRLF
    case else
        Body = Body & "No application has been received." & vbCRLF
End Select
Body = Body & vbCRLF & "Thank you," & vbCRLF & vbCRLF & "User" & vbCRLF & "The Program" & vbCRLF & "The College" & vbCRLF & "College" & vbCRLF & "[e-mail]"
Mail.Body = Body
Mail.Send
set Mail = nothing
%>
 
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Status Result</title>
</head>
<body>
<p align="center"><a target="_self" href="http://www.college.edu">
<img alt="The College" src="http://www.college.edu/images/Banner.jpg" border="0"></a></p>
<br>
<font face="arial, sans serif,helvetica" size="2"><p>
<%
response.write (FName)
response.write (",<br><br>")
response.write ("Your admission status has been emailed to you at ")
response.write (Applicant)
objRS.close
Session("DB").close
Set objRS = Nothing
Set Session("DB") = Nothing
Set FName = Nothing
Set Citizen = Nothing
Set College1 = Nothing
Set College2 = Nothing
Set College3 = Nothing
Set GRE = Nothing
Set TOEFL = Nothing
Set Tran1 = Nothing
Set Tran2 = Nothing
Set Tran3 = Nothing
Set Ref1 = Nothing
Set Ref2 = Nothing
Set Ref3 = Nothing
Set Ref4 = Nothing
Set RefLtr1 = Nothing
Set RefLtr2 = Nothing
Set RefLtr3 = Nothing
Set RefLtr4 = Nothing
Set AdStatus = Nothing
 
End If
%>
</p>
<p>
Thank you,<br>
College <br>
Office of Admissions<br>
<a href="mailto:[e-mail]?subject=Admissions">[e-mail]</a><br>
<a href="http://www.college.edu"</a></p>
</font></body>
</html>

[markrawlingson]

I would use something like the following..

For Each oField In oRs.Fields
   If IsNull( oRs( oField ) ) Then
      sEmailMessage = sEmailMessage & oField & VbCrLf
   End If
Next
 

This is of course assuming that you're allowing null values for the fields which are missing information. Then you can just run through each of the fields in your table, checking to see which ones are empty, and write the field's name out.

Sincerely,
Mark

[bpw22az]

Mark, I appreciate the reply. My concern using the code below is I am only looking for particular null fields depending on which case is valid, ie 'AC', 'AI', and 'UR'.

Something weird to add actually: I noticed as I placed response.write statements for each field (Citizen, GRE, etc) the emails started populating as they should. The only thing now is if you leave the write statements, students see a nice long string of information across the top of the webpage that also lets them know an email has been sent regarding their status.

Any ideas? :) Thanks.

I would use something like the following..

For Each oField In oRs.Fields
   If IsNull( oRs( oField ) ) Then
      sEmailMessage = sEmailMessage & oField & VbCrLf
   End If
Next
 

This is of course assuming that you're allowing null values for the fields which are missing information. Then you can just run through each of the fields in your table, checking to see which ones are empty, and write the field's name out.

Sincerely,
Mark

[jhardman]

Something weird to add actually: I noticed as I placed response.write statements for each field (Citizen, GRE, etc) the emails started populating as they should. The only thing now is if you leave the write statements, students see a nice long string of information across the top of the webpage that also lets them know an email has been sent regarding their status.

Any ideas? :) Thanks.

The simple way to solve that problem is to put your response.write statements in html comment tags:

response.write "<!-- pulled studentName from DB: " & objRS("studentName") & " -->" & vbNewLine

but I would be surprised if that was really what had solved the problem... Are you sure nothing else changed when you added the response.write statements?

Jared

[bpw22az]

It has been a very strange problem. I have basically gotten to the point where the email will send with the null fields just fine. Although now if I try to run the code with a Select Case statement the email will show up without the fields listed. Here is my last post:


The email seems to work, although when using the case statement to determine the status it will not place the null fields in the email. The email goes out just fine for all, but is missing the null fields when I add the Select Case code.

<%
Dim Applicant
Set Applicant = Request.Form("txtEmailAddress")
Dim oRs, oField, fieldCount, FName, Status
Set oRs = Server.CreateObject("ADODB.Recordset")
oRs.Open "vwAcademicsAdmissionsData WHERE Email = '" & Applicant & "';","DSN=OSC"
Set FName = oRs("First_Name")
Set Status = oRs("ApplicationStatus")
 
Select Case Status
case "AI"
sMsg = sMsg & "Greetings " & FName & "!" & vbCRLF & vbCRLF
sMsg = sMsg & "Your application to the College has been received." & vbCRLF & vbCRLF
sMsg = sMsg & "Any missing items on your application are listed below:" & vbCRLF & vbCRLF
For Each oField In oRs.Fields
If IsNull( oRs( oField.name ) ) Then
sMsg = sMsg & oField.name & VbCrLf
End If
Next
End Select
Dim Mail
Set Mail = CreateObject("CDONTS.NewMail")
Mail.From = "[e-mail]"
Mail.To = Applicant
Mail.Bcc = "[e-mail]"
Mail.Subject = "Your Admission Status"
Mail.Body = sMsg
Mail.Send
 
oRs.Close
Set oRs = Nothing
%>

[jhardman]

A similar approach to Mark's, but using the specific responses you had already programmed:

<%
Option Explicit
Response.Expires = -1
Dim Applicant
Applicant = Request.Form("txtEmailAddress")
Dim objRS
Dim strQuery
Set Session("DB") = Server.CreateObject("ADODB.Connection")
    Session("DB").Open("OSC")
 
strQuery = "SELECT * FROM tblAdmissionsData WHERE Email = '" & Applicant & "';"
 
Set objRS = Session("DB").Execute(strQuery)
 
If objRS.EOF = TRUE Then %>
   <p align='center'><a target='_self'  href='http://www.college.edu'><img
    alt='College' src='images/Banner.jpg' border='0'></a></p>
   There is no record with that email address in the database.
<%
Else
 
Dim Body
Dim Mail
Set Mail = CreateObject("CDONTS.NewMail")
Mail.From = "[e-mail]"
Mail.To = Applicant
Mail.Bcc = "[e-mail]"
Mail.Subject = "Your Admission Status"
 
Body = Body & "Greetings " & objRS("FName") & "!" & vbCRLF & vbCRLF
Select Case objRS("AdStatus")
    case "AC"
        Body = Body & "Your application to the College has been received and is complete." & vbCRLF
    case "AI"
        Body = Body & "Your application has been received but is incomplete." & vbCRLF
        Body = Body & "You are currently missing:" & vbCRLF & vbCRLF
            'GRE Report
                If IsNull(objRS("GRE")) Then
                Body = Body & "GRE Score Report" & vbCRLF
                Else
                End If
            'TOEFL Score
                If objRS("Citizen") <> "USA" AND IsNull(objRS("TOEFL")) Then
                Body = Body & "TOEFL Score" & vbCRLF
                Else
                End If
            'Reference Letters
                If objRS("Citizen") = "USA" Then 'Main loop
                    If IsNull(objRS("RefLtr1")) AND Then
                    Body = Body & "2 Reference Letters" & vbCRLF
                    ElseIf IsNull (objRS("RefLtr2")) Then
                    Body = Body & "A Reference Letter has been received from " & objRS("Ref1") & ". One additional letter is required." & vbCRLF
                    End If
                Else 'Main loop
                    If IsNull(objRS("RefLtr1")) Then
                    Body = Body & "2 Reference Letters" & vbCRLF
                    ElseIf IsNull(objRS("RefLtr2")) Then
                    Body = Body & "A Reference Letter has been received from " & objRS("Ref1") & ". One additional letter is required." & vbCRLF
                    End If
                End if 'Main loop
            'Transcripts
                If isNull(objRS("College1"))=FALSE AND IsNull(objRS("Tran1")) Then
                Body = Body & "Transcript for " & objRS("College1") & vbCRLF
                Else
                End If
                If isNull(objRS("College2"))=FALSE AND IsNull(objRS("Tran2")) Then
                Body = Body & "Transcript for " & objRS("College2") & vbCRLF
                Else
                End If
                If isNull(objRS("College3"))= FALSE AND IsNull(objRS("Tran3")) Then
                Body = Body & "Transcript for " & College3 & vbCRLF
                Else
                End If
        Body = Body & vbCRLF & "Please note the deadline for submission of all application materials for international applicants is December 1." & vbCRLF
    case "UR"
        Body = Body & "Your application to the College has been forwarded to the Admissions Committee for review." & vbCRLF
    case else
        Body = Body & "No application has been received." & vbCRLF
End Select
Body = Body & vbCRLF & "Thank you," & vbCRLF & vbCRLF & "User" & vbCRLF & "The Program" & vbCRLF & "The College" & vbCRLF & "College" & vbCRLF & "[e-mail]"
 

Let me know if this helps.

Jared

[jhardman]

It has been a very strange problem. I have basically gotten to the point where the email will send with the null fields just fine. Although now if I try to run the code with a Select Case statement the email will show up without the fields listed. Here is my last post:


The email seems to work, although when using the case statement to determine the status it will not place the null fields in the email. The email goes out just fine for all, but is missing the null fields when I add the Select Case code.

Sorry, I must have started typing my second response before your last was posted, I hope it isn't confusing. You adapted Mark's code correctly. There must be a line or two missing here, I assume you truncated the code. Why not change this to an if...then...else statement rather than a SELECT CASE? you only have three different possibilities for status, right?

[bpw22az]

I actually did try switching from Select Case to If/Then/Else. Same result. Yep, three possiblities. AC, AI, and UR are the three codes in the Status database field.

So strange. The email will be populated with the null fields just fine when testing with a user that has the AI (Incomplete) status in my database, although I will test the same user with the Select Case or If Then Else statements added, and I will receive the same email without the fields listed.

:)

Sorry, I must have started typing my second response before your last was posted, I hope it isn't confusing. You adapted Mark's code correctly. There must be a line or two missing here, I assume you truncated the code. Why not change this to an if...then...else statement rather than a SELECT CASE? you only have three different possibilities for status, right?

[jhardman]

I actually did try switching from Select Case to If/Then/Else. Same result. Yep, three possiblities. AC, AI, and UR are the three codes in the Status database field.

So strange. The email will be populated with the null fields just fine when testing with a user that has the AI (Incomplete) status in my database, although I will test the same user with the Select Case or If Then Else statements added, and I will receive the same email without the fields listed.

:)

try something like this pseudo code:
[code]response.write status

if status = ai
response.write all the null fields, use the loop or whatever
elseif status = ac
response.write all complete
else
response.write in review
end if [code]it sure looks like the problem is in the select case or if statement, not in the emailing, so try the above just to check if the if statement is executing at all.

Jared

[bpw22az]

Ok, gave the response.write a go with If/Then/Else statements. Sure enough, the null fields are displayed on the web-page although they are missing from the email. It appears the data is there for the taking, but will not transfer with the email when using Select Case or If/Then/Else.

Any ideas? :) (Code I used is below)

<%
Dim Applicant
Set Applicant = Request.Form("txtEmailAddress")
Dim oRs, oField, fieldCount, strQuery
Set Session("DB") = Server.CreateObject("ADODB.Connection")
    Session("DB").Open("OSC")
 
strQuery = "SELECT First_Name, Citizenship, College_1, College_2, College_3, GRE_Score_Date, TOEFLScore, "
strQuery = strQuery & "Transcript_1_Date, Transcript_2_Date, Transcript_3_Date, Reference__1, Reference__2, Reference_3, Reference_4, "
strQuery = strQuery & "Letter_1_Date, Letter_2_Date, Letter_3_Date, Letter_4_Date, ApplicationStatus "
strQuery = strQuery & "FROM vwAcademicsAdmissionsData WHERE Email = '" & Applicant & "';"
 
Set oRS = Session("DB").Execute(strQuery)
 
Dim FName
Set FName = oRS(0)
 
        sMsg = sMsg & "Greetings " & FName & "!" & vbCRLF & vbCRLF
        sMsg = sMsg & "Your application to the College has been received." & vbCRLF & vbCRLF
 
    IF ApplicationStatus = AI Then
 
        sMsg = sMsg & "Any missing items on your application are listed below:" & vbCRLF & vbCRLF
        For Each oField In oRs.Fields
        If IsNull( oRs( oField.name ) ) Then
        Response.Write(oField.name)
        End If
        Next
 
    Else If ApplicationStatus = AC Then
        Response.Write("Your application is complete")
 
    Else
        Response.Write("Under Review")
        End If
 
 
        sMsg = sMsg & vbCRLF & "Please note the deadline for submission of all application materials for international applicants is December 1." & vbCRLF
        sMsg = sMsg & vbCRLF & "Thank you," & vbCRLF & vbCRLF & "Test User" & vbCRLF & "Academic Programs" & vbCRLF & "College" & vbCRLF & "[e-mail]"
 
Dim Mail
Set Mail = CreateObject("CDONTS.NewMail")
Mail.From = "[e-mail]"
Mail.To = Applicant
Mail.Bcc = "[e-mail]"
Mail.Subject = "Your Admission Status"
Mail.Body = sMsg
Mail.Send
set Mail = nothing
%> 
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Status Result</title>
</head>
<body>
<p align="center"><a target="_self" href="http://www.college.edu">
<img alt="College" src="http://www.college.edu/images/BannerHome1.jpg" border="0"></a></p>
<br>
<font face="arial, sans serif,helvetica" size="2"><p>
<%
response.write (FName)
response.write (",<br><br>")
response.write ("Your admission status has been emailed to you at ")
response.write (Applicant)
oRs.Close
Set oRs = Nothing 
End If
%>
</p>
<p>
Thank you,<br>
Test User<br>
College<br>
Office of Admissions<br>
<a href="mailto:[e-mail]?subject=Admissions">[e-mail]</a><br>
<a href="http://www.college.edu/programs" target="_self">www.college.edu/programs</a></p>
</font></body>
</html>

try something like this pseudo code:
[code]response.write status

if status = ai
response.write all the null fields, use the loop or whatever
elseif status = ac
response.write all complete
else
response.write in review
end if [code]it sure looks like the problem is in the select case or if statement, not in the emailing, so try the above just to check if the if statement is executing at all.

Jared

[jhardman]

First- Please use the code tags available (the - # - button).

Second- Try this variation, let me know if it helps

'...
        sMsg = sMsg & "Greetings " & FName & "!" & vbCRLF & vbCRLF
        sMsg = sMsg & "Your application to the College has been received." & vbCRLF & vbCRLF
 
    IF ApplicationStatus = AI Then
 
        sMsg = sMsg & "Any missing items on your application are listed below:" & vbCRLF & vbCRLF
        For Each oField In oRs.Fields
        If IsNull( oRs( oField.name ) ) Then
            sMsg = sMsg & oField.name & vbCRLF
        End If
        Next
 
    Else If ApplicationStatus = AC Then
        sMsg = sMsg & "Your application is complete" & vbCRLF
 
    Else
        sMsg = sMSG & "Under Review" & vbCRLF
        End If
'...

[bpw22az]

Jared,

For a second there I had it running. All three status codes were sending out the correct email, even the incomplete null fields, although I think I may have moved an 'End If' statement and now whenever the status = "AI" no email is sent. AC and UR still work. Latest below:

<%
Dim Applicant
Set Applicant = Request.Form("txtEmailAddress")
Dim oRs, oField, fieldCount, FName, Status
Set oRs = Server.CreateObject("ADODB.Recordset")
oRs.Open "vwAcademicsAdmissionsData WHERE Email = '" & Applicant & "';","DSN=OSC"
Set FName = oRs("First_Name")
Set Status = oRs("ApplicationStatus")
 
sMsg = sMsg & "Greetings " & FName & "!" & vbCRLF & vbCRLF
        sMsg = sMsg & "Your application to the College has been received." & vbCRLF & vbCRLF
 
    IF Status = "AI" Then
 
        sMsg = sMsg & "Any missing items on your application are listed below:" & vbCRLF & vbCRLF
        For Each oField In oRs.Fields
        If IsNull( oRs( oField.name ) ) Then
            sMsg = sMsg & oField.name & vbCRLF
        End If
        Next
 
    Else If Status = "AC" Then
        sMsg = sMsg & "Your application is complete" & vbCRLF
 
    Else
        sMsg = sMsg & "Under Review" & vbCRLF
        End If
 
Dim Mail
Set Mail = CreateObject("CDONTS.NewMail")
Mail.From = "[e-mail]"
Mail.To = Applicant
Mail.Bcc = "[e-mail]"
Mail.Subject = "Your Admission Status"
Mail.Body = sMsg
Mail.Send
oRs.Close
Set oRs = Nothing 
End If
%>
 

Second- Try this variation, let me know if it helps

'...
        sMsg = sMsg & "Greetings " & FName & "!" & vbCRLF & vbCRLF
        sMsg = sMsg & "Your application to the College has been received." & vbCRLF & vbCRLF
 
    IF ApplicationStatus = AI Then
 
        sMsg = sMsg & "Any missing items on your application are listed below:" & vbCRLF & vbCRLF
        For Each oField In oRs.Fields
        If IsNull( oRs( oField.name ) ) Then
            sMsg = sMsg & oField.name & vbCRLF
        End If
        Next
 
    Else If ApplicationStatus = AC Then
        sMsg = sMsg & "Your application is complete" & vbCRLF
 
    Else
        sMsg = sMSG & "Under Review" & vbCRLF
        End If
'...

[jhardman]

Subtle variation. "elseif" is one word.

<%
Dim Applicant
Set Applicant = Request.Form("txtEmailAddress")
Dim oRs, oField, fieldCount, FName, Status
Set oRs = Server.CreateObject("ADODB.Recordset")
oRs.Open "vwAcademicsAdmissionsData WHERE Email = '" & Applicant & "';","DSN=OSC"
Set FName = oRs("First_Name")
Set Status = oRs("ApplicationStatus")
 
sMsg = sMsg & "Greetings " & FName & "!" & vbCRLF & vbCRLF
     sMsg = sMsg & "Your application to the College has been received." & vbCRLF & vbCRLF
 
IF Status = "AI" Then
 
   sMsg = sMsg & "Any missing items on your application are listed below:" & vbCRLF & vbCRLF
 
   For Each oField In oRs.Fields
 
      If IsNull( oRs( oField.name ) ) Then
         sMsg = sMsg & oField.name & vbCRLF
      End If
 
   Next
 
ElseIf Status = "AC" Then
 
   sMsg = sMsg & "Your application is complete" & vbCRLF
 
Else
 
   sMsg = sMsg & "Under Review" & vbCRLF
 
End If
 
Dim Mail
Set Mail = CreateObject("CDONTS.NewMail")
Mail.From = "[e-mail]"
Mail.To = Applicant
Mail.Bcc = "[e-mail]"
Mail.Subject = "Your Admission Status"
Mail.Body = sMsg
Mail.Send
oRs.Close
Set oRs = Nothing %>