<%'This page has three states. First, it diplays all class names and times in a drop... if request.querystring("action")="stepone" then response.write "

Step One: choose a class

" SQL="SELECT [class_datetime_tbl].[class_datetime_id], [class_datetime_tbl].[class_datetime], [class_tbl].[class_name]" SQL = SQL & "FROM class_tbl INNER JOIN class_datetime_tbl ON [class_tbl].[class_id] =[class_datetime_tbl].[class_id] WHERE [class_datetime_tbl].[class_datetime] >= NOW() ORDER BY [class_datetime_tbl].[class_datetime];" SET CONN=server.createObject("ADODB.connection") CONN.open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\") & "\data\NothingToIt.mdb;UID=;PWD=;" SET RS=server.CreateObject("ADODB.recordset") RS.Open SQL, CONN, 2 rs.movefirst response.write "" While not RS.EOF response.write "" rs.movenext WEND response.write "
"&rs("class_name")&" on "& rs("class_datetime")&"
" rs.close conn.close set rs=nothing set conn=nothing end if 'end step one if request.querystring("action")= "steptwo" then session("classVar") = request.querystring("classname") session("datetime")= request.querystring("datetime") response.write "

Currently assigned staff for "&session("classVar")&" on "&session("datetime")&":

" SQL="SELECT [staff_assignment_tbl].[staff_assignment_id], [staff_tbl].[staff_name], [staff_tbl].[staff_phone], [staff_tbl].[staff_email]" SQL = SQL & "FROM staff_tbl INNER JOIN staff_assignment_tbl ON [staff_tbl].[staff_id] =[staff_assignment_tbl].[staff_id] WHERE class_datetime_id="&request.querystring("dateid")&";" SET CONN=server.createObject("ADODB.connection") CONN.open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\") & "\data\NothingToIt.mdb;UID=;PWD=;" SET RS=server.CreateObject("ADODB.recordset") RS.Open SQL, CONN, 2 Response.write "" While not rs.eof Response.write "" rs.movenext WEND Response.write "
"&rs("staff_name")&""&rs("staff_phone")&""&rs("staff_email")&"Delete


" rs.close conn.close set rs=nothing set conn=nothing response.write "

Step Two: choose an employee

" response.write "

" 'Now lets get the list of employees.. SQL="SELECT * from staff_tbl" SET CONN=server.createObject("ADODB.connection") CONN.open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\") & "\data\NothingToIt.mdb;UID=;PWD=;" SET RS=server.CreateObject("ADODB.recordset") RS.Open SQL, CONN, 2 Response.write "" rs.close conn.close set rs=nothing set conn=nothing response.write "" response.write "   " response.write "
" End if 'end of steptwo if request.querystring("action")="stepthree" THEN 'insert the new record into the assignment_tbl if request.querystring("subaction") = "add" THEN SQL = "INSERT INTO staff_assignment_tbl (staff_id, class_datetime_id) Values('"&request.form("id")&"','"&request.form("dateid")&"');" SET CONN=server.createobject("ADODB.connection") Conn.open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\") & "\data\NothingToIt.mdb;UID=;PWD=;" SET COMM=server.createobject("ADODB.command") comm.activeconnection = CONN comm.commandtext = SQL comm.commandtype = adCmdText comm.execute conn.close set conn = nothing SET comm = nothing response.write "

Currently assigned staff for "&session("classVar")&" on "&session("datetime")&":

" end if if request.querystring("subaction") = "delete" THEN SQL = "DELETE * FROM staff_assignment_tbl WHERE staff_assignment_id = "&request.querystring("id")&";" SET CONN=server.createobject("ADODB.connection") Conn.open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\") & "\data\NothingToIt.mdb;UID=;PWD=;" SET COMM=server.createobject("ADODB.command") comm.activeconnection = CONN comm.commandtext = SQL comm.commandtype = adCmdText comm.execute conn.close set conn = nothing SET comm = nothing response.write "

Currently assigned staff for "&session("classVar")&" on "&session("datetime")&":

" end if 'now spit it all back so we can see we added the record SQL="SELECT [staff_assignment_tbl].[staff_assignment_id], [staff_tbl].[staff_name], [staff_tbl].[staff_phone], [staff_tbl].[staff_email]" SQL = SQL & "FROM staff_tbl INNER JOIN staff_assignment_tbl ON [staff_tbl].[staff_id] =[staff_assignment_tbl].[staff_id] WHERE class_datetime_id="&request("dateid")&";" SET CONN=server.createObject("ADODB.connection") CONN.open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\") & "\data\NothingToIt.mdb;UID=;PWD=;" SET RS=server.CreateObject("ADODB.recordset") RS.Open SQL, CONN, 2 Response.write "" While not rs.eof Response.write "" rs.movenext WEND Response.write "
"&rs("staff_name")&""&rs("staff_phone")&""&rs("staff_email")&"
" rs.close conn.close set rs=nothing set conn=nothing end if 'end of step three function CleanString(sqlValue) CleanString = replace(sqlValue, "'", "''") end function function cleanHTMLString(sqlValue) cleanHTMLString = replace(sqlValue, "'", "'") end function %>