Workshop: " & rsWorkshops("title") & " places on 6th. " & rsWorkshops("places1") & " (booked: " & rsWorkshops("booked1") & ") places on 7th. " & rsWorkshops("places2") & " (booked: " & rsWorkshops("booked2") & ")
" & vbcrlf num=0 response.write "Delegates attending on the 6th:
" & vbcrlf rem list delegates for this workshop/day rsDetails.Open "SELECT * FROM details where workshop6tham = '" & rsWorkshops("title") & "'", dbConn, 3 Do While Not rsDetails.EOF response.write " Booking reference: " & rsDetails("reference") & " Surname: " & rsDetails("surname") & " Forename: " & rsDetails("firstname") & " Organisation: " & rsDetails("organisation") & "Totals attending on the 6th: " & num & "
" & vbcrlf num=0 response.write "Delegates attending on the 7th:
" & vbcrlf rem list delegates for this workshop/day rsDetails.Open "SELECT * FROM details where workshop7tham = '" & rsWorkshops("title") & "'", dbConn, 3 Do While Not rsDetails.EOF response.write " Booking reference: " & rsDetails("reference") & " Surname: " & rsDetails("surname") & " Forename: " & rsDetails("firstname") & " Organisation: " & rsDetails("organisation") & "Totals attending on the 7th: " & num & "
" & vbcrlf rsWorkshops.MoveNext Loop rsWorkshops.close Response.Write("Workshop: " & rsWorkshops("title") & " places on 6th. " & rsWorkshops("places1") & " (booked: " & rsWorkshops("booked1") & ") places on 7th. " & rsWorkshops("places2") & " (booked: " & rsWorkshops("booked2") & ")
" & vbcrlf num=0 response.write "Delegates attending on the 6th:
" & vbcrlf rem list delegates for this workshop/day rsDetails.Open "SELECT * FROM details where workshop6thpm = '" & rsWorkshops("title") & "'", dbConn, 3 Do While Not rsDetails.EOF response.write " Booking reference: " & rsDetails("reference") & " Surname: " & rsDetails("surname") & " Forename: " & rsDetails("firstname") & " Organisation: " & rsDetails("organisation") & "Totals attending on the 6th: " & num & "
" & vbcrlf num=0 response.write "Delegates attending on the 7th:
" & vbcrlf rem list delegates for this workshop/day rsDetails.Open "SELECT * FROM details where workshop7thpm = '" & rsWorkshops("title") & "'", dbConn, 3 Do While Not rsDetails.EOF response.write " Booking reference: " & rsDetails("reference") & " Surname: " & rsDetails("surname") & " Forename: " & rsDetails("firstname") & " Organisation: " & rsDetails("organisation") & "Totals attending on the 7th: " & num & "
" & vbcrlf rsWorkshops.MoveNext Loop rsWorkshops.close Response.Write("Error reading delegate details
" & vbcrlf else response.write "Please confirm you want to delete this delegate: " & rsDetails("firstname") & " " & rsDetails("surname") & " (Reference: " & rsDetails("reference") & ")
" & vbcrlf response.write "" & vbcrlf end if end if if request("view") = "Confirm delete" then currworkshop6tham=request("currworkshop6tham") currworkshop6thpm=request("currworkshop6thpm") currworkshop7tham=request("currworkshop7tham") currworkshop7thpm=request("currworkshop7thpm") Set rsDetails = Server.CreateObject("ADODB.Recordset") query="DELETE FROM details where reference=" & request("delegate") on error resume next ' response.write "" & query & "
" dbConn.Execute query, recaffected if err<>0 then for each objerr in dbConn.Errors Response.Write("Error deleting delegate record - " & objerr.Description & "
") next else Response.Write("" & query & "
" on error resume next dbConn.Execute query,recaffected if err<>0 then for each objerr in dbConn.Errors Response.Write("Error updating old workshop (6th. am) record - " & objerr.Description & "
") next else Response.Write("Old workshop record (6th. am) updated
") end if end if if currworkshop6thpm <> "" then rem get record for 6th pm and decrease booking field rsWorkshops.Open "select * from pm_workshops where title='" & currworkshop6thpm & "'", dbConn, 3 currbookings=rsWorkshops("booked1") currbookings=currbookings-1 rem increment the booking figure rsWorkshops.close rem now update it query="update pm_workshops set booked1='" & currbookings & "' where title='" & currworkshop6thpm & "'" ' response.write "" & query & "
" on error resume next dbConn.Execute query,recaffected if err<>0 then for each objerr in dbConn.Errors Response.Write("Error updating old workshop (6th. pm) record - " & objerr.Description & "
") next else Response.Write("Old workshop record (6th. pm) updated
") end if end if if currworkshop7tham <> "" then rem get record for 7th am and decrease booking field rsWorkshops.Open "select * from am_workshops where title='" & currworkshop7tham & "'", dbConn, 3 currbookings=rsWorkshops("booked2") currbookings=currbookings-1 rem increment the booking figure rsWorkshops.close rem now update it query="update am_workshops set booked2='" & currbookings & "' where title='" & currworkshop7tham & "'" ' response.write "" & query & "
" on error resume next dbConn.Execute query,recaffected if err<>0 then for each objerr in dbConn.Errors Response.Write("Error updating old workshop (7th. am) record - " & objerr.Description & "
") next else Response.Write("Old workshop record (7th. am) updated
") end if end if if currworkshop7thpm <> "" then rem get record for 7th pm and decrease booking field rsWorkshops.Open "select * from pm_workshops where title='" & currworkshop7thpm & "'", dbConn, 3 currbookings=rsWorkshops("booked2") currbookings=currbookings-1 rem increment the booking figure rsWorkshops.close rem now update it query="update pm_workshops set booked2='" & currbookings & "' where title='" & currworkshop7thpm & "'" ' response.write "" & query & "
" on error resume next dbConn.Execute query,recaffected if err<>0 then for each objerr in dbConn.Errors Response.Write("Error updating old workshop (7th. pm) record - " & objerr.Description & "
") next else Response.Write("Old workshop record (7th. pm) updated
") end if end if Set rsWorkshops = Nothing end if end if if request("amend") = "Save changes" then rem get the form data userref=request("reference") surname=request("surname") surname=Replace(surname,"'","’") firstname=request("firstname") title=request("title") jobtitle=request("jobtitle") e2bn_lea=request("e2bn_lea") if e2bn_lea = "on" then e2bn_lea="1" else e2bn_lea="0" end if subject=request("subject") organisation=request("organisation") address1=request("address1") address2=request("address2") address3=request("address3") postcode=request("postcode") telephone=request("telephone") attending6=request("attending6") if attending6 = "on" then attending6="1" else attending6="0" end if attending7=request("attending7") if attending7 = "on" then attending7="1" else attending7="0" end if evening5=request("evening5") if evening5 = "on" then evening5="1" else evening5="0" end if evening6=request("evening6") if evening6 = "on" then evening6="1" else evening6="0" end if dinner=request("dinner") if dinner = "on" then dinner="1" else dinner="0" end if dinner5=request("dinner5") if dinner5 = "on" then dinner5="1" else dinner5="0" end if whole_conf=request("whole_conf") if whole_conf = "on" then whole_conf="1" else whole_conf="0" end if in_to=request("in_to") in_jobtitle=request("in_jobtitle") in_establishment=request("in_establishment") in_address1=request("in_address1") in_address2=request("in_address2") in_address3=request("in_address3") in_postcode=request("in_postcode") in_budget=request("in_budget") in_email=request("in_email") in_telephone=request("in_telephone") special=request("special") email=request("email") currworkshop6tham=request("currworkshop6tham") currworkshop6thpm=request("currworkshop6thpm") currworkshop7tham=request("currworkshop7tham") currworkshop7thpm=request("currworkshop7thpm") workshop6tham=request("workshop6tham") workshop6thpm=request("workshop6thpm") workshop7tham=request("workshop7tham") workshop7thpm=request("workshop7thpm") rem and update the delegates record parta="UPDATE details set surname='" & surname & "', firstname='" & firstname & "', title='" _ & title & "', jobtitle='" & jobtitle & "', e2bn_lea='" & e2bn_lea & "', subject='" & subject & "', " partb="organisation='" & organisation & "', address1='" & address1 & "', address2='" & address2 _ & "', address3='" & address3 & "',postcode='" & postcode & "', telephone='" & telephone _ & "', attending6='" & attending6 & "', attending7='" & attending7 & "', " partc="evening5='" & evening5 & "', evening6='" & evening6 & "', dinner='" & dinner & "', dinner5='" & dinner5 & "', whole_conf='" _ & whole_conf & "', in_to ='" & in_to & "', in_jobtitle='" & in_jobtitle & "', in_establishment='" _ & in_establishment & "', in_address1='" & in_address1 & "', " partd="in_address2='" & in_address2 & "', in_address3='" & in_address3 & "', in_postcode='" _ & in_postcode & "', in_budget='" & in_budget & "', in_email='" & in_email & "', in_telephone='" _ & in_telephone & "', special='" & special & "', email='" & email & "', workshop6tham='" & workshop6tham _ & "', workshop6thpm='" & workshop6thpm & "', workshop7tham='" & workshop7tham & "', workshop7thpm='" & workshop7thpm _ & "' where reference=" & userref query=parta & partb & partc & partd on error resume next ' response.write "" & query & "
" dbConn.Execute query, recaffected if err<>0 then for each objerr in dbConn.Errors Response.Write("Error updating delegate record - " & objerr.Description & "
") next else Response.Write("" & query & "
" on error resume next dbConn.Execute query,recaffected if err<>0 then for each objerr in dbConn.Errors Response.Write("Error updating new workshop (6th. am) record - " & objerr.Description & "
") next else Response.Write("New workshop record (6th. am) updated
") end if rem get record for 6th am and decrease booking field rsWorkshops.Open "select * from am_workshops where title='" & currworkshop6tham & "'", dbConn, 3 currbookings=rsWorkshops("booked1") currbookings=currbookings-1 rem increment the booking figure rsWorkshops.close rem now update it query="update am_workshops set booked1='" & currbookings & "' where title='" & currworkshop6tham & "'" ' response.write "" & query & "
" on error resume next dbConn.Execute query,recaffected if err<>0 then for each objerr in dbConn.Errors Response.Write("Error updating old workshop (6th. am) record - " & objerr.Description & "
") next else Response.Write("Old workshop record (6th. am) updated
") end if end if if workshop6thpm <> currworkshop6thpm then rem get record for 6th pm and increase booking field rsWorkshops.Open "select * from pm_workshops where title='" & workshop6thpm & "'", dbConn, 3 currbookings=rsWorkshops("booked1") currbookings=currbookings+1 rem increment the booking figure rsWorkshops.close rem now update it query="update pm_workshops set booked1='" & currbookings & "' where title='" & workshop6thpm & "'" ' response.write "" & query & "
" on error resume next dbConn.Execute query,recaffected if err<>0 then for each objerr in dbConn.Errors Response.Write("Error updating new workshop (6th. pm) record - " & objerr.Description & "
") next else Response.Write("New workshop record (6th. pm) updated
") end if rem get record for 6th pm and decrease booking field rsWorkshops.Open "select * from pm_workshops where title='" & currworkshop6thpm & "'", dbConn, 3 currbookings=rsWorkshops("booked1") currbookings=currbookings-1 rem increment the booking figure rsWorkshops.close rem now update it query="update pm_workshops set booked1='" & currbookings & "' where title='" & currworkshop6thpm & "'" ' response.write "" & query & "
" on error resume next dbConn.Execute query,recaffected if err<>0 then for each objerr in dbConn.Errors Response.Write("Error updating old workshop (6th. pm) record - " & objerr.Description & "
") next else Response.Write("Old workshop record (6th. pm) updated
") end if end if if workshop7tham <> currworkshop7tham then rem get record for 7th am and increase booking field rsWorkshops.Open "select * from am_workshops where title='" & workshop7tham & "'", dbConn, 3 currbookings=rsWorkshops("booked2") currbookings=currbookings+1 rem increment the booking figure rsWorkshops.close rem now update it query="update am_workshops set booked2='" & currbookings & "' where title='" & workshop7tham & "'" ' response.write "" & query & "
" on error resume next dbConn.Execute query,recaffected if err<>0 then for each objerr in dbConn.Errors Response.Write("Error updating new workshop (7th. am) record - " & objerr.Description & "
") next else Response.Write("New workshop record (7th. am) updated
") end if rem get record for 7th am and decrease booking field rsWorkshops.Open "select * from am_workshops where title='" & currworkshop7tham & "'", dbConn, 3 currbookings=rsWorkshops("booked2") currbookings=currbookings-1 rem increment the booking figure rsWorkshops.close rem now update it query="update am_workshops set booked2='" & currbookings & "' where title='" & currworkshop7tham & "'" ' response.write "" & query & "
" on error resume next dbConn.Execute query,recaffected if err<>0 then for each objerr in dbConn.Errors Response.Write("Error updating old workshop (7th. am) record - " & objerr.Description & "
") next else Response.Write("Old workshop record (7th. am) updated
") end if end if if workshop7thpm <> currworkshop7thpm then rem get record for 7th pm and increase booking field rsWorkshops.Open "select * from pm_workshops where title='" & workshop7thpm & "'", dbConn, 3 currbookings=rsWorkshops("booked2") currbookings=currbookings+1 rem increment the booking figure rsWorkshops.close rem now update it query="update pm_workshops set booked2='" & currbookings & "' where title='" & workshop7thpm & "'" ' response.write "" & query & "
" on error resume next dbConn.Execute query,recaffected if err<>0 then for each objerr in dbConn.Errors Response.Write("Error updating new workshop (7th. pm) record - " & objerr.Description & "
") next else Response.Write("New workshop record (7th. pm) updated
") end if rem get record for 7th pm and decrease booking field rsWorkshops.Open "select * from pm_workshops where title='" & currworkshop7thpm & "'", dbConn, 3 currbookings=rsWorkshops("booked2") currbookings=currbookings-1 rem increment the booking figure rsWorkshops.close rem now update it query="update pm_workshops set booked2='" & currbookings & "' where title='" & currworkshop7thpm & "'" ' response.write "" & query & "
" on error resume next dbConn.Execute query,recaffected if err<>0 then for each objerr in dbConn.Errors Response.Write("Error updating old workshop (7th. pm) record - " & objerr.Description & "
") next else Response.Write("Old workshop record (7th. pm) updated
") end if end if Set rsWorkshops = Nothing end if end if %> <% if request("view") = "View details" or request("view") = "Search" then Set rsDetails = Server.CreateObject("ADODB.Recordset") if request("view") = "View details" then rsDetails.Open "SELECT * FROM details where reference=" & request("delegate"), dbConn, 3 else rsDetails.Open "SELECT * FROM details where reference like '%" & request("searchString") & _ "%' or surname like '%" & request("searchString") & "%' or firstname like '%" & request("searchString") & _ "%'" , dbConn, 3 end if if rsDetails.EOF then response.write "Error reading delegate details
" & vbcrlf else rem work out and display total cost TotalCost=0 if rsDetails("e2bn_lea") = "True" then if rsDetails("evening5") = "True" then TotalCost=TotalCost+E2BNEvening5th end if if rsDetails("evening6") = "True" then TotalCost=TotalCost+E2BNEvening6th end if if rsDetails("whole_conf") = "True" then TotalCost=TotalCost+E2BNWholeConference end if else if rsDetails("attending6") = "True" then TotalCost=TotalCost+NonE2BNAtt6thDayDelegate end if if rsDetails("attending7") = "True" then TotalCost=TotalCost+NonE2BNAtt7thDayDelegate end if if rsDetails("evening5") = "True" then TotalCost=TotalCost+NonE2BNEvening5th end if if rsDetails("evening6") = "True" then TotalCost=TotalCost+NonE2BNEvening6th end if if rsDetails("dinner5") = "True" then TotalCost=TotalCost+NonE2BNMondayEveningDinner end if if rsDetails("dinner") = "True" then TotalCost=TotalCost+NonE2BNTuesdayEveningDinner end if if rsDetails("whole_conf") = "True" then TotalCost=TotalCost+NonE2BNWholeConference end if end if response.write "Total cost to this delegate is: £" & TotalCost & "
" & vbcrlf rem now display delegate details in form %> <% Set rsDetails = Nothing end if else %>Please select the delegate you require:
or search by reference, firstname or surname:
Available reports:
1. To export the data from this database to another application, copy the file e2bn1.mdb to a windows PC with MS Access on it and use the export facility in that program. (ie:- Click on the 'File' menu, then the 'Export' option.)
2. To produce custom reports do the same as in note 1, but click on the 'Reports' option in the central pane. (ie:- the one listing your database tables.) Then choose 'Design View' to create your report.
<% end if %> <% dbConn.Close Set dbConn = Nothing %>