Web Surveys: Capturing Data Using ASP and Access - PowerPoint PPT Presentation

Slide1 l.jpg
Download
1 / 37

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.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Slide1 l.jpg

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


Slide2 l.jpg

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


Slide3 l.jpg

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


Slide4 l.jpg

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


Slide5 l.jpg

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


Slide6 l.jpg

default.htm


Slide7 l.jpg

default.asp


Slide8 l.jpg

idcheck.asp


Slide9 l.jpg

survey.asp


Slide10 l.jpg

thanks.asp


Slide27 l.jpg

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>


Slide28 l.jpg

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>


Slide29 l.jpg

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>


Slide30 l.jpg

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>


Slide31 l.jpg

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

%>


Slide32 l.jpg

Survey.asp (part 1 of 2)

<%

Dim id2pass

id2pass=Request.Cookies("UserName")

if id2pass = "" then

response.redirect("error_cookies.asp")

%>

<% else %>


Slide33 l.jpg

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 %>


Slide34 l.jpg

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


Slide35 l.jpg

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


Slide36 l.jpg

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")

%>


Slide37 l.jpg

thanks.asp

<%

session("StudentId") = ""

%>


  • Login