Problem using Select Case statement

[bpw22az]

I am currently in the process of setting up an asp page that sends an inquiring student an email regarding his/her application status. The student enters his/her email address on a web page, the address is associated with the correspponding record in a sql database table, and an email is generated and sent to them based on app status. I am focusing on the "Incomplete" portion which will list all null fields from the table in the email. Using the code below works fine, although as soon as I insert a Select Case statement to determine what type of email should be sent, my null fields disappear from the email. An email is still sent, but without the list of null fields. Any ideas? Thanks.

Currently my asp script is sending email just fine along with the correct null fields from my database. The only problem I am having is using 'select case' statements. I have one field in this table that distinguishes if an application is complete. There are three options, (Complete, Incomplete, Under Review). I would like the script to check these values and send the appropriate email. The null field email would go with the Incomplete application email. The other two just would send out a general email letting them know their status. Any time I setup a select case statement around the code, the emails cease to show the null fields anymore. Any ideas? Here is my latest code:

<%
Dim Applicant
Set Applicant = Request.Form("txtEmailAddress")
Dim oRs, oField, fieldCount, FName, Status
Set oRs = Server.CreateObject("ADODB.Recordset")
oRs.Open "vwAcademicsData 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
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

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

[jeffstl]

It would probably help if I could see where and how exactly you are using the Select Case?

Once idea however would be to create a flag to see if a field was marked as null, then check this flag to determine if the incomplete email goes out.

This might not be what your looking for, but like I said, if I could see how and where the select case is going that is causing issues I might be able to see the problem.

dim strNullFlag as String
strNullFlag = "N"
 
For Each oField In oRs.Fields
     If IsNull( oRs( oField.name ) ) Then
          sMsg = sMsg & oField.name & VbCrLf
          strNullFlag = "Y"
     End If
Next
 
 

[bpw22az]

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

[jeffstl]

Well at first glance I don't see what would cause that. Especially if your msg is getting populated.

The only thing that adding the select case as it appears here would cause would be for the code under the Case "AI" to not be run.

I guess I would make certain that the code under the Case "AI" is actually executing.

You can check by trying to either step through the code (not sure what set up you have), or you can try a response.write or debug.print on the Status to see what actually ends up in that variable.

Put a response.write Status right before the Select Case and see what is in "Status" before you hit your Select Case. From there maybe you can write out the values as the code executes to figure out whats going wrong.