<%
'test point
'For Each item In Request.Form
' Response.Write item & ": " & Request.Form(item) & "
"
'Next
dim token
Dim objconn
Set objconn = Server.CreateObject("ADODB.Connection")
objconn.ConnectionString = "Provider=SQLOLEDB;Server=capdbsvr;Database=ulive;User Id=webenquirer;Password=0nline3nq;"
objconn.Open
'define SQL strings
strCountry = "select distinct vc_code, substring(vc_name,6, 50) as vc_name, vc_context from caps_valid_codes where vc_domain='country' order by vc_context desc"
strPrompt = "select distinct vc_code, vc_name from caps_valid_codes where vc_domain='source' and (vc_ref='enquiry1' or vc_ref is null) order by vc_name"
strTitle = "select distinct vc_code, vc_name, vc_sequence from caps_valid_codes where vc_domain='title' and (vc_context='normal') order by vc_sequence"
'DEFINE CONSTANTS
Const adDBTimeStamp = 135
Const adVarchar = 200
Const adBoolean = 11
Const adParamInput = &H0001
Const adParamOutput = &H0002
if request.Form("findalumnus") = "true" then
'retrieve student details if found
surname = Request.form("txtSurname")
forename = Request.form("txtForename")
dob = Request.form("txtDOB")
studyingsurname = request.Form("txtStudyingSurname")
'look for alumnus based on search details
'define command object
Set comFindAlumnus = Server.CreateObject("ADODB.Command")
Set comFindAlumnus.ActiveConnection = objconn
With comFindAlumnus
.CommandText = "en_FindAlumnus"
.CommandType = 4 'stored procedure
.Parameters.Append .CreateParameter("@surname", adVarChar, adParamInput, 255, surname)
.Parameters.Append .CreateParameter("@studyingsurname", adVarChar, adParamInput, 255, studyingsurname)
.Parameters.Append .CreateParameter("@forename", adVarChar, adParamInput, 255, forename)
.Parameters.Append .CreateParameter("@dob", adVarChar, adParamInput, 10, dob)
.Parameters.Append .CreateParameter("@studentref", adVarChar, adParamInput, 10, studentref)
.Parameters.Append .CreateParameter("@graduationyear", adVarChar, adParamInput, 10, graduationyear)
'response.Write("executing findalumnus
")
'exec SP
Set rstAlumnus = Server.CreateObject("ADODB.Recordset")
set rstAlumnus = .Execute
if rstAlumnus.EOF then
'no record found - prompt to try again, or continue to save details anyway
'response.Write("no alumnus found
")
noresult = true
studentref="notfound"
else
'response.Write("found someone!")
noresult = false
'retrieve search results
studentref = rstAlumnus("studentref")
title = rstAlumnus("title")
forename = rstAlumnus("forenames")
surname = rstAlumnus("surname")
courseref = rstAlumnus("courseref")
coursename = rstAlumnus("coursename")
yearofentry = rstAlumnus("yearofentry")
graduationyear = rstAlumnus("graduationyear")
street = rstAlumnus("street")
town = rstAlumnus("town")
county = rstAlumnus("county")
postcode = rstAlumnus("postcode")
email = rstAlumnus("email")
phone = rstAlumnus("phone")
end if
End With
'clean up
rstAlumnus.close
set comFindAlumnus = nothing
elseif request.form("txtStudentRef") <> "" and request.querystring("fValid") = "1" then
'response.Write("creating the alumni update record
")
'retrieve form variables
dim strSurname, strForename, strGender, strDOB, strPostCode, strAddress, strCountry, strEmailAddress, strPhoneNumber, strSubjects, strBrochures, strPrompt, strNotes, strPromotionCode, strLevelOfStudy
strStudentRef = Request.form("txtStudentRef")
strSurname = Request.form("txtSurname")
strForename = Request.form("txtForename")
strGender = Request.form("radGender")
strDOB = Request.form("txtDOB")
strPostCode = Request.form("txtPostCode")
strAddress = Request.form("txtStreet") & Chr(13) & Chr(10) & Request.form("txtTown") & Chr(13) & Chr(10) & Request.form("txtCounty")
strCountry = Request.form("cmbCountry")
strEmailAddress = Request.form("txtEmailAddress")
strPhoneNumber = Request.form("txtPhoneNumber")
strTitle = Request.Form("cmbTitle")
strcourse=Request.Form("txtCourse")
stryearofentry=Request.Form("txtYearOfEntry")
strgraduationyear=Request.Form("txtGraduationYear")
fsharestory = Request.Form("cmbstory")
frecruitment = Request.Form("cmbrecruitment")
fdiscounts = Request.Form("cmbdiscount")
fnewsletter = Request.Form("cmbnewsletter")
fevents = Request.Form("cmbevents")
fempengagement = Request.Form("cmbempengagement")
if request.form("chkstory_consent") <> "" then
fconsent_story="Y"
else
fconsent_story = "N"
end if
if request.form("chkshare_consent") <> "" then
fconsent_sharedetails="Y"
else
fconsent_sharedetails = "N"
end if
if request.form("chkNoMailing") <> "" then
agreeterms = true
else
agreeterms = false
end if
if request.form("chkEmail") <> "" then
comms_email= true
else
comms_email= false
end if
if request.form("chkPost") <> "" then
comms_post= true
else
comms_post= false
end if
if request.form("chkSms") <> "" then
comms_sms= true
else
comms_sms= false
end if
if request.form("chkPhone") <> "" then
comms_phone= true
else
comms_phone= false
end if
strPrompt = Request.form("cmbPrompt")
strNotes = "" 'Request.form("txtEnquiry")
'2019 grad specific fields
dim grad_interested, grad_notinterestedreason, grad_guests
'grad 2019 specific fields
if request.form("grad_notinterestedreason") <> "" then
grad_notinterestedreason= request.form("grad_notinterestedreason")
End If
if request.form("grad_interested") <> "" then
grad_interested = request.form("grad_interested")
End If
if request.form("grad_guests") <> "" then
grad_guests= request.form("grad_guests")
End If
dim tmp
'define command object
Set comEnquiry = Server.CreateObject("ADODB.Command")
Set comEnquiry.ActiveConnection = objconn
With comEnquiry
.CommandText = "en_createalumniupdate"
.CommandType = 4 'stored procedure
.Parameters.Append .CreateParameter("@studentref", adVarChar, adParamInput, 50, strStudentRef)
.Parameters.Append .CreateParameter("@surname", adVarChar, adParamInput, 255, strSurname)
.Parameters.Append .CreateParameter("@forename", adVarChar, adParamInput, 255, strForename)
.Parameters.Append .CreateParameter("@dob", adDBTimeStamp, adParamInput, , strDOB)
.Parameters.Append .CreateParameter("@postcode", adVarChar, adParamInput, 20, strPostcode)
.Parameters.Append .CreateParameter("@address", adVarChar, adParamInput, 255, strAddress)
.Parameters.Append .CreateParameter("@country", adVarChar, adParamInput, 5, strCountry)
.Parameters.Append .CreateParameter("@emailaddress", adVarChar, adParamInput, 55, strEmailAddress)
.Parameters.Append .CreateParameter("@telephonenumber", adVarChar, adParamInput, 255, strPhoneNumber)
.Parameters.Append .CreateParameter("@prompt", adVarChar, adParamInput, 10, strPrompt)
.Parameters.Append .CreateParameter("@notes", adVarChar, adParamInput, 255, strNotes)
.Parameters.Append .CreateParameter("@course", adVarChar, adParamInput, 255, strcourse)
.Parameters.Append .CreateParameter("@yearofentry", adVarChar, adParamInput, 20, stryearofentry)
.Parameters.Append .CreateParameter("@graduationyear", adVarChar, adParamInput, 20, strgraduationyear)
.Parameters.Append .CreateParameter("@int_recruitment", adVarChar, adParamInput, 1, frecruitment)
.Parameters.Append .CreateParameter("@int_events", adVarChar, adParamInput, 255,fevents)
.Parameters.Append .CreateParameter("@int_newsletter", adVarChar, adParamInput, 1, fnewsletter)
.Parameters.Append .CreateParameter("@int_empengagement", adVarChar, adParamInput, 1, fempengagement)
.Parameters.Append .CreateParameter("@int_discounts", adVarChar, adParamInput, 1, fdiscounts)
.Parameters.Append .CreateParameter("@int_sharestory", adVarChar, adParamInput, 1, fsharestory)
' .Parameters.Append .CreateParameter("@consent_story", adVarChar, adParamInput, 1, fconsent_story)
' .Parameters.Append .CreateParameter("@consent_sharedetails", adVarChar, adParamInput, 1, fconsent_sharedetails)
.Parameters.Append .CreateParameter("@agreeterms", adBoolean, adParamInput, , agreeterms)
.Parameters.Append .CreateParameter("@comms_email", adBoolean, adParamInput, , comms_email)
.Parameters.Append .CreateParameter("@comms_post", adBoolean, adParamInput, , comms_post)
.Parameters.Append .CreateParameter("@comms_sms", adBoolean, adParamInput, , comms_sms)
.Parameters.Append .CreateParameter("@comms_phone", adBoolean, adParamInput, , comms_phone)
'2019 grad specific elements
.Parameters.Append .CreateParameter("@interestedingraduating", adVarChar, adParamInput, 1, grad_interested)
.Parameters.Append .CreateParameter("@notinterestedreason", adVarChar, adParamInput, 255, grad_notinterestedreason)
.Parameters.Append .CreateParameter("@guests", adVarChar, adParamInput, 1, grad_guests)
'response.Write("executing the update
")
'exec SP
.Execute tmp
End With
'clean up
set comEnquiry = nothing
end if
%>
<%
'stage 1 - present initial search form to alumnus
if request.form("findalumus") = "" and request.QueryString("search") = "" and request.Form("txtStudentRef") = "" and request.QueryString("fValid") = "" then
%>
Leaving Marjon isn't goodbye, it's hello to our alumni family.
Whether you've just graduated or have been away from us for a while, our alumni family will help to keep you involved. You'll have access to a range of benefits and services, be able to get involved in
inspiring projects and attend exclusive events.
Keep your Marjon memories alive and stay in touch:
Join our official Marjon Alumni Facebook
Follow us on Twitter @Marjonuni
Contact fellow alumni on Marjon Alumni LinkedIn group
Update your contact details using the form below. Please provide the following information (where possible) to enable us to identify your study records.
Are you searching for information, but can't find what you are looking for? Get in touch and we will do our best to help, just email alumni@marjon.ac.uk
<%
'stage 2 - attempted to find alumnus
elseif request.querystring("search") = "1" then
'tried to find someone!
%>
<%
if noresult = true then
%>
We were not able to find your study record using the details you provided. Please record your full details below and we will ensure our records are updated.
<%
else
%>
We have located your study details using the information you have provided. Please ensure the details we have are accurate and current.
<%
end if
%>
<%
Else
'provide feedback on sucessful submission
%>
Thank you for updating your details and welcome to our Alumni family.
Keep informed of Alumni events via the University Alumni web pages.
<%
End if
'destroy connection object
objconn.Close
%>
