|
|
Eating
Behaviours Survey
<%if request.form("Submit") <> "Submit" then
%>
<%else
%>
<%
'add to database
dim uscore(19)
dim frmfields
dim dbfields
dim vbfields(30)
'--define the field names in the form and the database (order is important)
frmfields = Array("Q1PART1","Q1PART2","Q1PART3","Q1PART4","Q1PART5","Q1PART6","Q2PART1","Q2PART2","Q2PART3","Q2PART4","Q2PART5","Q2PART6","Q2PART7","Q2PART8","Q3PART1","Q3PART2","Q3PART3","Q3PART4","Q3PART5","Q4","Q5","Q8","Completed","") 'must end with a blank
dbfields = Array("Q1PART1","Q1PART2","Q1PART3","Q1PART4","Q1PART5","Q1PART6","Q2PART1","Q2PART2","Q2PART3","Q2PART4","Q2PART5","Q2PART6","Q2PART7","Q2PART8","Q3PART1","Q3PART2","Q3PART3","Q3PART4","Q3PART5","Gender","Age","Country","CompletedBefore","Postcode","Weight","Height","EntryDate","")
'--the name of the database connection and the table to be added to
ADODBNAME = "eating-behaviours"
TABLENAME = "Table1"
'if you want the date of entry (in american format) include the line below
currentdate=Formatdatetime( now() ,2)
'--read the form fields (names in frmfields) into vb array
dim error
error=0
numfields=0
Do while frmfields(numfields)<>""
vbfields(numfields)=request.form(frmfields(numfields))
if vbfields(numfields)="" then error=1
if numfields < 19 then uscore(numfields+1)=vbfields(numfields)
numfields = numfields + 1
loop
'IF NOT FILLED IN
if request.form("Q8")="Australia" and request.form("Q9")="" then error=1
if request.form("Q6kg")="" and request.form("Q6lbs")="" then error=1
if request.form("Q7cm")="" and request.form("Q7ft")="" then error=1
if error=0 then
'--create additional fields in vb array (eg the entry date etc)
Postcode=request.form("Q9")
if Postcode="" then Postcode=" "
vbfields(numfields)=Postcode
numfields=numfields+1
kg=request.form("Q6kg")
if kg="" then
lbs=request.form("Q6lbs")
vbfields(numfields)=lbs*.4536
numfields = numfields + 1 'always increment the counter when adding a field/s
end if
if kg<>"" then
vbfields(numfields)=kg
numfields = numfields + 1 'always increment the counter when adding a field/s
end if
cm=request.form("Q7cm")
if cm="" then
ft=request.form("Q7ft")
inch=request.form("Q7in")
if inch="" then inch=0
ft=ft*0.3048
inch=inch*2.540
vbfields(numfields)=(ft*100)+inch
numfields = numfields + 1
end if
if cm<>"" then
vbfields(numfields)=cm
numfields = numfields + 1
end if
vbfields(numfields)=currentdate
numfields = numfields + 1 'always increment the counter when adding a field/s
'create the insert statement
InsertStmt = "INSERT INTO "
InsertStmt = InsertStmt & TABLENAME
InsertStmt = InsertStmt & "("
' add all the database field names to the insert into statement
count=0
Do While dbfields(count) <> ""
if count<>0 then InsertStmt = InsertStmt & ","
InsertStmt = InsertStmt & dbfields(count)
count=count+1
loop
InsertStmt = InsertStmt & ")"
'create the Values statement
'NOTE: IF THE VALUES ARE NOT OF TYPE TEXT THEN THIS WILL NEED TO BE RE-WRITTEN
ValueStmt = " VALUES ( '"
' add all the values to the insert into statement
count=0
Do while count0 then ValueStmt = ValueStmt & ",'"
ValueStmt = ValueStmt & vbfields(count)
ValueStmt = ValueStmt & "'"
count=count+1
loop
ValueStmt = ValueStmt & ")"
'combine to make the whole statement
SQLStmt = InsertStmt & ValueStmt
'response.write SQLStmt
'start the connection, execute the sql statment and finally close the connection
Set Conn = Server.CreateObject("ADODB.Connection")
strConnect = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("/databases/eating-behaviours.mdb")
conn.open(strConnect)
Set RS = Conn.Execute(SQLStmt)
Conn.Close
response.write "Your questionnaire has been submitted"
end if
if error=1 then response.write "You did not complete the form. Please go back and resubmit."
if error<>1 then%>
<%'----DATABASE USAGE SCRIPT-----
'variables
DatabaseName="Eating Behaviours"
DateAccessed = Formatdatetime( now() ,2)
TimeAccessed = Formatdatetime( now() ,4)
'create SQL statement
SQL="INSERT INTO Table1 (DatabaseName, DateAccessed, TimeAccessed) "
SQL=SQL & "VALUES ( '" & DatabaseName & "', "
SQL=SQL & "'" & DateAccessed & "', "
SQL=SQL & "'" & TimeAccessed & "')"
'write to database
Set Conn = Server.CreateObject("ADODB.Connection")
strConnect = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("/databases/db-usage.mdb")
conn.open(strConnect)
Conn.Execute(SQL)
Conn.Close %>
The following graphs display
your score 
against the average response
<%
dim col(19)
dim total
dim score
Set Conn = Server.CreateObject("ADODB.Connection")
strConnect = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("/databases/eating-behaviours.mdb")
conn.open(strConnect)
sqlstmt="SELECT Table1.* FROM Table1"
Set RS = Conn.Execute(sqlstmt)
score=0
total=0
dim count
count=0
Do While count<20
col(count)=0
count=count+1
Loop
Do While Not RS.EOF
Comp=RS ("CompletedBefore")
if comp="No" then
total=total+1
'QUESTION 1
score=RS("Q1PART1")
col(1)=col(1)+score
score=RS("Q1PART2")
col(2)=col(2)+score
score=RS("Q1PART3")
col(3)=col(3)+score
score=RS("Q1PART4")
col(4)=col(4)+score
score=RS("Q1PART5")
col(5)=col(5)+score
score=RS("Q1PART6")
col(6)=col(6)+score
'QUESTION 2
score=RS("Q2PART1")
col(7)=col(7)+score
score=RS("Q2PART2")
col(8)=col(8)+score
score=RS("Q2PART3")
col(9)=col(9)+score
score=RS("Q2PART4")
col(10)=col(10)+score
score=RS("Q2PART5")
col(11)=col(11)+score
score=RS("Q2PART6")
col(12)=col(12)+score
score=RS("Q2PART7")
col(13)=col(13)+score
score=RS("Q2PART8")
col(14)=col(14)+score
'QUESTION 3
score=RS("Q3PART1")
col(15)=col(15)+score
score=RS("Q3PART2")
col(16)=col(16)+score
score=RS("Q3PART3")
col(17)=col(17)+score
score=RS("Q3PART4")
col(18)=col(18)+score
score=RS("Q3PART5")
col(19)=col(19)+score
end if
RS.MoveNext
Loop
Conn.Close
'total=100/total
col(1)=col(1)/total
col(2)=col(2)/total
col(3)=col(3)/total
col(4)=col(4)/total
col(5)=col(5)/total
col(6)=col(6)/total
col(7)=col(7)/total
col(8)=col(8)/total
col(9)=col(9)/total
col(10)=col(10)/total
col(11)=col(11)/total
col(12)=col(12)/total
col(13)=col(13)/total
col(14)=col(14)/total
col(15)=col(15)/total
col(16)=col(16)/total
col(17)=col(17)/total
col(18)=col(18)/total
col(19)=col(19)/total
%>
Q1. Do you ...
Q2. Answer each of the following
questions ..
Q3. How often do you ...
<%end if
end if%>
Last
updated: April 17, 2001.
|
