Home » Classic ASP » Calculating total from recordset using “select case”

Calculating total from recordset using “select case”

total score
Here is code in classic asp

Here is example on how to calculate recordset total using “select case” statement. I have calculated essay and resume scholarship values based on records fromĀ  two essay and resume tables, and queried results based on applicant-student registration ID or name. Then I calculated “total score” of each student using totals from two categories they were judged upon: essay and resume.

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>

<html xmlns=”http://www.w3.org/1999/xhtml”>
<head>
<title></title>
<link href=”Styles/Site960.css” rel=”stylesheet” type=”text/css” />
</head>
<body>
<%
Dim objConn, objRS ‘/conn str applicant tbl
Dim objConnE, objRSE ‘/conn str essay sum qry
Dim objConnR, objRSR ‘/conn str resume sum qry
Dim SumEss, SumEss1, i_select, i_select2, count, countR

SumEss = 0
SumEss1 = 0
SumRes = 0
SumRes1 = 0
count = 0
countR = 0

‘ / open db conn – connopen /
Set objConn = Server.CreateObject(“ADODB.Connection”)
dbPath = Server.MapPath(“scholarhip.accdb”)
objConn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & dbPath, “”, “”
SET objRS = Server.CreateObject(“ADODB.RecordSet”)
objRS.Open “SELECT aName, sProgram FROM Applicants WHERE sProgram = ‘Incoming Freshmen’ “,objConn,1,2

Response.Write(“<table class=’datagrid’>”)
Response.Write(“<tr style=’background: #e1d6c7; font-weight: bold;’>”)
Response.Write(“<td>#</td><td>Applicant</td><td>Schol. Program</td><td>Essay</td><td>Resume</td><td>Final Score</td>”)
Response.Write(“</tr>”)
While not objRS.EOF
countR = countR + 1
Response.Write(“<tr>”)
Response.Write(“<td style=’text-align: right;’>”& countR &”.</td>”)
Response.Write(“<td>”& objRS(“aName”) &”</td>”)
Response.Write(“<td>”& objRS(“sProgram”) &”</td>”)
‘/ total essay
Response.Write(“<td style=’text-align: right;’>”)
SET objRSE = Server.CreateObject(“ADODB.RecordSet”)
objRSE.Open “SELECT * FROM Essay WHERE Applicant='”& objRS(“aName”)&”‘ “,objConn,1,2
Dim TotSum
While Not objRSE.EOF
count = count + 1
i_select = Right(count,1)
select case i_select
case “1”,”3″,”5″,”7″,”9″
SumEss = ((objRSE(“Originality”))+(objRSE(“Clarity”))+(objRSE(“Content”))+(objRSE(“Neatness”))+(objRSE(“Grammar”))+(objRSE(“Punctuation”)))
case “2”,”4″,”6″,”8″
SumEss1 = ((objRSE(“Originality”))+(objRSE(“Clarity”))+(objRSE(“Content”))+(objRSE(“Neatness”))+(objRSE(“Grammar”))+(objRSE(“Punctuation”)))
end select
objRSE.MoveNext
Wend
Response.Write(FormatNumber((SumEss) + (SumEss1),0))
SET objRSE = Nothing
‘objRSE.Close
Response.Write(“</td>”)

‘/ total resume
Response.Write(“<td style=’text-align: right;’>”)
SET objRSR = Server.CreateObject(“ADODB.RecordSet”)
objRSR.Open “SELECT * FROM Resume WHERE Applicant='”& objRS(“aName”) &”‘ “,objConn,1,2
Dim TotSumR
While Not objRSR.EOF
count2 = count2 + 1
i_select2 = Right(count2,1)
select case i_select2
case “1”,”3″,”5″,”7″,”9″
SumRes = ((objRSR(“Presentation”))+(objRSR(“Adherence”))+(objRSR(“Activities”))+(objRSR(“Potential”)))
case “2”,”4″,”6″,”8″
SumRes1 = ((objRSR(“Presentation”))+(objRSR(“Adherence”))+(objRSR(“Activities”))+(objRSR(“Potential”)))
end select
objRSR.MoveNext
Wend
Response.Write(FormatNumber((SumRes) + (SumRes1),0))
SET objRSR = Nothing
Response.Write(“</td>”)
Response.Write(“<td style=’text-align: right;’>”)
Response.Write(FormatNumber((SumEss) + (SumEss1) + (SumRes) + (SumRes1),0))
Response.Write(“</td>”)
Response.Write(“</tr>”)
objRS.MoveNext
Wend
‘ close connections
SET objRS = nothing
SET objConn = nothing

Response.Write(“</table>”)

%>
</body>
</html>