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!

[code=asp]<% @LANGUAGE = VBScript %>
<%
Option Explicit
Response.Expire s = -1
Dim Applicant
Set Applicant = Request.Form("t xtEmailAddress" )
Dim objRS
Dim strQuery
Set Session("DB") = Server.CreateOb ject("ADODB.Con nection")
Session("DB").O pen("OSC")

strQuery = "SELECT First_Name, Citizenship, College_1, College_2, College_3, GRE_Score_Date, TOEFLScore, "
strQuery = strQuery & "Transcript_1_D ate, Transcript_2_Da te, Transcript_3_Da te, Reference__1, Reference__2, Reference_3, Reference_4, "
strQuery = strQuery & "Letter_1_D ate, Letter_2_Date, Letter_3_Date, Letter_4_Date, ApplicationStat us "
strQuery = strQuery & "FROM tblAdmissionsDa ta WHERE Email = '" & Applicant & "';"

Set objRS = Session("DB").E xecute(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("C DONTS.NewMail")
Mail.From = "user@college.e du"
Mail.To = Applicant
Mail.Bcc = "helpdesk@colle ge.edu"
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(College 1)=FALSE) AND IsNull(Tran1) Then
Body = Body & "Transcript for " & College1 & vbCRLF
Else
End If
If (IsNull(College 2)=FALSE) AND IsNull(Tran2) Then
Body = Body & "Transcript for " & College2 & vbCRLF
Else
End If
If (IsNull(College 3)=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 & "user@college.e du"
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").c lose
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:us er@college.edu? subject=Admissi ons">user@colle ge.edu</a><br>
<a href="http://www.college.edu "</a></p>
</font></body>
</html>[/code]

[markrawlingson]

I would use something like the following..


[code=asp]
For Each oField In oRs.Fields
If IsNull( oRs( oField ) ) Then
sEmailMessage = sEmailMessage & oField & VbCrLf
End If
Next
[/code]
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..


[code=asp]
For Each oField In oRs.Fields
If IsNull( oRs( oField ) ) Then
sEmailMessage = sEmailMessage & oField & VbCrLf
End If
Next
[/code]
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:[code=asp]response.write "<!-- pulled studentName from DB: " & objRS("studentN ame") & " -->" & vbNewLine[/code]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.
[code=asp]
<%
Dim Applicant
Set Applicant = Request.Form("t xtEmailAddress" )
Dim oRs, oField, fieldCount, FName, Status
Set oRs = Server.CreateOb ject("ADODB.Rec ordset")
oRs.Open "vwAcademicsAdm issionsData WHERE Email = '" & Applicant & "';","DSN=O SC"
Set FName = oRs("First_Name ")
Set Status = oRs("Applicatio nStatus")

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("C DONTS.NewMail")
Mail.From = "user1@college. edu"
Mail.To = Applicant
Mail.Bcc = "user@college.e du"
Mail.Subject = "Your Admission Status"
Mail.Body = sMsg
Mail.Send

oRs.Close
Set oRs = Nothing
%>[/code]

[jhardman]

A similar approach to Mark's, but using the specific responses you had already programmed:
[code=asp]<%
Option Explicit
Response.Expire s = -1
Dim Applicant
Applicant = Request.Form("t xtEmailAddress" )
Dim objRS
Dim strQuery
Set Session("DB") = Server.CreateOb ject("ADODB.Con nection")
Session("DB").O pen("OSC")

strQuery = "SELECT * FROM tblAdmissionsDa ta WHERE Email = '" & Applicant & "';"

Set objRS = Session("DB").E xecute(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("C DONTS.NewMail")
Mail.From = "user@college.e du"
Mail.To = Applicant
Mail.Bcc = "helpdesk@colle ge.edu"
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("G RE")) Then
Body = Body & "GRE Score Report" & vbCRLF
Else
End If
'TOEFL Score
If objRS("Citizen" ) <> "USA" AND IsNull(objRS("T OEFL")) Then
Body = Body & "TOEFL Score" & vbCRLF
Else
End If
'Reference Letters
If objRS("Citizen" ) = "USA" Then 'Main loop
If IsNull(objRS("R efLtr1")) 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("R efLtr1")) Then
Body = Body & "2 Reference Letters" & vbCRLF
ElseIf IsNull(objRS("R efLtr2")) 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("C ollege1"))=FALS E AND IsNull(objRS("T ran1")) Then
Body = Body & "Transcript for " & objRS("College1 ") & vbCRLF
Else
End If
If isNull(objRS("C ollege2"))=FALS E AND IsNull(objRS("T ran2")) Then
Body = Body & "Transcript for " & objRS("College2 ") & vbCRLF
Else
End If
If isNull(objRS("C ollege3"))= FALSE AND IsNull(objRS("T ran3")) 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 & "user@college.e du"
[/code]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...els e 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...els e 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 & "user@college.edu"

Dim Mail
Set Mail = CreateObject("CDONTS.NewMail")
Mail.From = "user@college.edu"
Mail.To = Applicant
Mail.Bcc = "helpdesk@college.edu"
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:user@college.edu?subject=Admissions">user@college.edu</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 = "user@college.edu"
Mail.To = Applicant
Mail.Bcc = "helpdesk@college.edu"
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.[CODE=asp]
<%
Dim Applicant
Set Applicant = Request.Form("t xtEmailAddress" )
Dim oRs, oField, fieldCount, FName, Status
Set oRs = Server.CreateOb ject("ADODB.Rec ordset")
oRs.Open "vwAcademicsAdm issionsData WHERE Email = '" & Applicant & "';","DSN=O SC"
Set FName = oRs("First_Name ")
Set Status = oRs("Applicatio nStatus")

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("C DONTS.NewMail")
Mail.From = "user@college.e du"
Mail.To = Applicant
Mail.Bcc = "helpdesk@colle ge.edu"
Mail.Subject = "Your Admission Status"
Mail.Body = sMsg
Mail.Send
oRs.Close
Set oRs = Nothing %>
[/CODE]