<%@ Language=VBScript %> <% option explicit %> Get Students Scores <% 'this is a more complex example of using SQL dim conn,rs dim fname,connstr,sql 'first, get a list of students... Set conn = Server.CreateObject("ADODB.Connection") fname = server.mappath("students.mdb") connstr = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & fname conn.open connstr,"","" Set rs = Server.CreateObject("ADODB.Recordset") ' Substitute in form parameters into the query string sql = "select studentid, lastname, firstname from students order by lastname;" rs.Open sql, conn, 1, 1 %>

Student Scores

 

Select an item from the list:

 

<% 'find the students scores from another table... if request.form("listbox") <> "" then 'run only if something selected 'start out by getting the total score... dim totpoints sql = "select sum(maximumpoints) from assignments;" rs.open sql, conn, 1,1 totpoints = rs.fields(0) 'get the returned data rs.close dim stid,stname 'which student are we talking about... stid = request.form("listbox") 'get the student name sql = "select lastname, firstname from students where studentid = " & stid & ";" rs.open sql, conn, 1, 1 stname = rs.fields("firstname") & " " & rs.fields("lastname") rs.close 'note use of calculations in SQL to extract information sql = "select sum(score) from results where studentid = " & stid & ";" rs.Open sql, conn, 1, 1 'now the fun part--put an output string together 'note the use of the date function... response.write (stname & " -- score to date (" & date() & ") is: " & rs.fields(0).value & " of " & totpoints) rs.close conn.close set rs = nothing set conn = nothing end if %>