1 / 37

Web Surveys: Capturing Data Using ASP and Access

Web Surveys: Capturing Data Using ASP and Access. SUNY AIRPO Presentation, 6/17/2004. Jeff Gerken. Office of Institutional Research, UAB 321 University at Albany, SUNY 1400 Washington Ave. Albany, NY 12222 phone: (518) 437-4794 e-mail: jgerken@uamail.albany.edu.

daniel_millan
Download Presentation

Web Surveys: Capturing Data Using ASP and Access

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Web Surveys: Capturing Data Using ASP and Access SUNY AIRPO Presentation, 6/17/2004 Jeff Gerken Office of Institutional Research, UAB 321 University at Albany, SUNY 1400 Washington Ave. Albany, NY 12222 phone: (518) 437-4794 e-mail: jgerken@uamail.albany.edu

  2. Special Thanks for helpful ideas to: Mark A. Palladino Research Specialist Office of Institutional Research, Planning and Assessment Drexel University http://www.drexel.edu/provost/ir/neair and http://www.4guysfromrolla.com/webtech/faq/faqtoc.shtml

  3. Web links to the survey: Manually to type in an ID http://web.albany.edu/ir/airpo/default.htm or With a hotlink in an email already containing the ID https://web.albany.edu/ir/surveys/airpo/idcheck.asp?surveyid=airpo25

  4. Welcome screen default.htm Manually Enter ID Email with ID hotlink OR default.asp still in idcheck.asp ID error ID check idcheck.asp Cookies error error_cookies.asp Survey Instrument Data insert survey.asp datainsert.asp Thanks thanks.asp

  5. Folder structure --Folder with Access database airpo.mdb --Upsized version of Access database link only --Server folder with default entry page default.htm --Server secure folder with --Enter ID default.asp --ID check idcheck.asp --If no cookies error_cookies.asp --Survey instrument survey.asp --Insert data datainsert.asp --Thank you thanks.asp

  6. default.htm

  7. default.asp

  8. idcheck.asp

  9. survey.asp

  10. thanks.asp

  11. default.htm <FORM name="LoginForm" action="https://web.albany.edu/ir/surveys/airpo/default.asp" LANGUAGE="JavaScript" enctype="multipart/form-data"> <CENTER> <input type="submit" value="Click Here to Go to the Survey"> </CENTER> </form>

  12. default.asp <FORM name="LoginForm" action="idcheck.asp?<%=Request.QueryString%>" method = "get" LANGUAGE="JavaScript" > <CENTER> <INPUT type = "text" name="surveyid" maxlength="13" length="13" size="13"> <INPUT type="submit" value = "Submit"> <input type="reset" value="Reset"> </CENTER> </form>

  13. idcheck.asp (part 1 of 3) <% SQLcommand = "SELECT surveyid, IsVisit, Visit_date from ssn_airpo where surveyid = '" & Request.QueryString("surveyid") & "'" Set rs = Server.createObject("ADODB.Recordset") rs.open SQLcommand,"DSN=IRSurv; UID=IRSurv; PWD=*******",0,3 ' check if the surveyid is in the database, EOF means End Of File IF rs.EOF THEN ' surveyid not found in the database rs.Close %> <HTML> You have not keyed in your Survey-ID yet. <FORM name="mesg" action="default.asp"> <input type = "submit" value ="Manual Survey-ID Entry"></FORM> </HTML>

  14. idcheck.asp (part 2 of 3) <% ELSE ' found the student, next check if already visited. if rs.Fields("IsVisit") = No then ' new to the survey, write cookie, go to next page Dim id2pass id2pass=Request.QueryString("surveyid") 'write id to cookie Response.Cookies("UserName")=id2pass %> <HTML> Your Survey-ID Number has been verified<br>Please click the button below to go to the survey.</H3> <form NAME="LoginForm" action="survey.asp?" METHOD=POST LANGUAGE="JavaScript" > <center><input type=submit value = "AIRPO Institutional Research Survey"> </form> </HTML>

  15. idcheck.asp (part 3 of 3) <% else %> <HTML> Our records show that you have already submitted your responses on <%=rs.Fields("Visit_Date")%> </HTML> <% rs.Close end if%> <% END IF %>

  16. Survey.asp (part 1 of 2) <% Dim id2pass id2pass=Request.Cookies("UserName") if id2pass = "" then response.redirect("error_cookies.asp") %> <% else %>

  17. Survey.asp (part 2 of 2) <FORM NAME="oiss" ACTION="datainsert.asp" METHOD="Post"> College/University: <input type="text" name="college" size="35" maxlength="75"> Have you been accepted yet? <input type="radio" name="accepted" value="1"> Yes <input type="radio" name="accepted" value="0"> No Click the SUBMIT button to send your responses <input type="submit" value="Submit" name="submit"> </form> <% end if %>

  18. datainsert.asp (page 1 of 3) <% Set ServerConn = Server.createObject("ADODB.Connection") ServerConn.open "DSN=IRSurv; UID=IRSurv; PWD=****** Set ServerCmd = Server.CreateObject("ADODB.Command") Set ServerCmd.ActiveConnection = ServerConn Dim id2pass id2pass=Request.Cookies("UserName") Dim surveyid surveyid = id2pass if surveyid = "" then surveyid = "_LOSTSURVEYID_" end if

  19. datainsert.asp (page 2 of 3) college= Smoothquotes(Request("college")) accepted= Request("accepted") fincomm= Smoothquotes(Request("fincomm")) Function smoothquotes (text) smoothquotes = replace (text,"'","''") End Function FieldList="" FieldList=FieldList & "[surveyid]," FieldList=FieldList & "[college]," FieldList=FieldList & "[accepted]," FieldList=FieldList & "[fincomm]" SQLCommand= "" SQLCommand= SQLCommand & "INSERT INTO response_airpo" SQLCommand= SQLCommand & "(" & FieldList & ")" SQLCommand= SQLCommand & "values (" SQLCommand= SQLCommand & "'"& surveyid &"'," SQLCommand= SQLCommand & "'"& college &"'," SQLCommand= SQLCommand & "'"& accepted &"'," SQLCommand= SQLCommand & "'"& fincomm &"')" ServerCmd.CommandText = SQLCommand ServerCmd.CommandType = 1 ServerCmd.Execute

  20. datainsert.asp (page 3 of 3) ' reset the visit_date and IsVisit fields in the table ssn_database, so every user ' may just have one record in the system SQLCommand = "" SQLCommand = "UPDATE ssn_airpo set IsVisit = 1, Visit_Date = Getdate(), IPAddress = '" & request.ServerVariables("remote_host") & "' where surveyid ='" & Request.Cookies("UserName") & "'" ServerCmd.CommandText = SQLCommand ServerCmd.CommandType = 1 ServerCmd.Execute Set ServerCmd = Nothing ServerConn.Close set ServerConn = Nothing 'Reinitialize the surveyid in the cookie to protect against multiple submissions didit = "" didit="_REPEAT_" & id2pass 'write id to cookie Response.Cookies("UserName")=didit 'Redirect to the final thanks page Response.redirect("Thanks.asp") %>

  21. thanks.asp <% session("StudentId") = "" %>

More Related