Good evening all, I have devleoped

[ChristianX]

Good evening all,
I have devleoped a quiz system that i will be using on my new site. I have the quiz script wrote, however, there are a number of quizzes that a user may take. These are stored in a MS Access database, each with a unique TestID. The quiz script at the moment works grand, however i have hardcoded the TestID in, which though it works it not efficient. What i want is for the user to select a book they have read froma drop down list box (which popoluates from the database Book Tabel), and upon submittance of this another list box appears which popoluates with tests associated to that book (via foreign key BookID in the Test Table).

Essentailly what i need to do is when the user clicks on the test they want to do(from the 2nd list box) the quiz page will load with the appropriate test.

Here is what i have created so far, but have run up against a brick wall! Can anyone be the saviour?! Thanks guys for your time.

bookdropdown.asp (which will provide drop boxes)

<!--#include file=&quot;Clssfd.asp&quot;-->
<%
Dim rsBook, rsTest
Dim iRequestedID

' Some basic checking and cleaning of the input
iRequestedID = Trim(Request.QueryString(&quot;BookID&quot)
iRequestedID = Replace(iRequestedID, &quot;'&quot;, &quot;''&quot

If IsNumeric(iRequestedID) Then
iRequestedID = CInt(iRequestedID)
Else
iRequestedID = 0
End If

' The form links back to this same file passing back the id
%>

<p>To add a question: Select the book that the question relates to</p>
<form action=&quot;bookdropdown.asp&quot; method=&quot;GET&quot;>

<%
' Build our query for select box 1
strSQL= &quot;SELECT * FROM Book ORDER BY Title&quot;

Set rsBook = Server.CreateObject(&quot;ADODB.Recordset&quot
rsBook.Open strSQL, objConn

' build our first drop down with lists of books
If Not rsBook.EOF Then
rsBook.MoveFirst
%>

<SELECT NAME=&quot;BookID&quot;><option>Select Book</option>
<%
' Loop through books
Do While Not rsBook.EOF
Response.Write &quot;<option value=&quot;&quot;&quot;
Response.Write rsBook.Fields(&quot;BookID&quot
Response.Write &quot;&quot;&quot;&quot;
If rsBook.Fields(&quot;BookID&quot = CInt(iRequestedID) Then
Response.Write &quot;selected=&quot;&quot;true&quot;&quot;&quot;
End If
Response.Write &quot;>&quot;
Response.Write Trim(rsBook.Fields(&quot;Title&quot)
Response.Write &quot;</option>&quot; & vbCrLf

' Move to next record
rsBook.MoveNext
Loop
%>
</select>
<%
End If
rsBook.Close
Set rsBook = Nothing

' If a request for a specific id comes in, then build second select box
If iRequestedID <> 0 Then
strSQL = &quot;SELECT * FROM Test WHERE BookId = &quot; _
& iRequestedID & &quot; ORDER BY TestName&quot;


Set rsTest = Server.CreateObject(&quot;ADODB.Recordset&quot
rsTest.Open strSQL, objConn, adOpenForwardOnly, adLockOptimistic, adCmdText

' Build our drop down box of the tests
If Not rsTest.EOF Then
rsTest.MoveFirst
%>
<select name=&quot;TestID&quot;><option>Select the test to add the question to</option>
<% ' Loop through tests
Do While Not rsTest.EOF
Response.Write &quot;<option>&quot;
Response.Write Trim(rsTest.Fields(&quot;TestName&quot)
Response.Write &quot;</option>&quot; & vbCrLf

' Move to next record
rsTest.MoveNext
Loop
%>
</select>
<%
End If

' Close ADO objects we're finished with and free DB variables
rsTest.Close
Set rsTest = Nothing
End If
%>
<input type=&quot;submit&quot; value=&quot;Submit&quot; />
</form>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Then here is the quiz.asp page (Note where i hard coded the testID 'Const TestID = 4'

<!--#include file=&quot;Clssfd.asp&quot;-->
<%
' Takes a integer parameter and converts it to the appropriate letter
Function GetLetterFromAnswerNumber(iInput)
Dim strTemp
Select Case iInput
Case 0
strTemp = &quot;A&quot;
Case 1
strTemp = &quot;B&quot;
Case 2
strTemp = &quot;C&quot;
Case 3
strTemp = &quot;D&quot;
Case 4
strTemp = &quot;E&quot;
Case 5
strTemp = &quot;F&quot;
Case 6
strTemp = &quot;G&quot;
Case 7
strTemp = &quot;H&quot;
Case 8
strTemp = &quot;I&quot;
Case 9
strTemp = &quot;J&quot;
End Select
GetLetterFromAnswerNumber = strTemp
End Function

' Create a fuction
Function GetAnswerFromAnswerString(iQuestionID, strAnswers)
Dim strTemp
Dim iOffset
' I use InStrRev since I want to retrieve the last entered value
' in case they went back and changed their mind.

' Find the location of the question number we want to use
iOffset = InStrRev(strAnswers, &quot;|&quot; & iQuestionID & &quot;|&quot;, -1, 1)

' Get our answer by using the offset we just found and then moving
' right the length of the question indicator to arrive at the
' appropriate letter
strTemp = Mid(strAnswers, iOffset + Len(&quot;|&quot; & iQuestionID & &quot;|&quot, 1)

' There's no way it should be anything else, but to be sure we
' convert it to a string and make sure it's uppercase
GetAnswerFromAnswerString = UCase(CStr(strTemp))
End Function
%>

<%

' Lets you run multiple Test from one DB by separating by TestID!
Const TestID = 4


' Now to all our other variables!
Dim cnnTest, rsTest 'DB objects if we use the DB for the info

Dim I 'our standard (improperly named) looping variable
Dim iNumberOfQuestion 'the number of questions in the test
Dim iQuestionID 'the question we're currently on
Dim strQuestionText 'text of the question to be asked
Dim aAnswers 'array of choices for the question to be asked
'if we hard code, then I also use it for the
'correct answers when I go to grade the quiz
Dim strAnswers 'stores the question numbers and response choices
'seperated by |'s
Dim iScore 'so we know how well the user did
Dim bAbort 'added after I had finished to account for closed sessions
Dim strResults 'another late addition for the each question breakdown!

bAbort = False 'set it to false since we only want to abort in certain cases



' If this is the first call to the quiz then init everything
' o/w retrieve values we need. We check by looking for the
' Question ID from the querystring.
If Request.QueryString(&quot;qid&quot = &quot;&quot; Then
' Retrieve and Set the Quiz Info

' Code to use DB!
' Create DB connection and connect to the DB
Set cnnTest = Server.CreateObject(&quot;ADODB.Connection&quot
cnnTest.Open objConn

' Create RS and query DB for quiz info
Set rsTest = Server.CreateObject(&quot;ADODB.Recordset&quot
rsTest.Open &quot;SELECT * FROM Test WHERE TestID=&quot; & TestID & &quot;;&quot;, cnnTest

' Set our session vars
Session(&quot;TestName&quot = CStr(rsTest.Fields(&quot;TestName&quot.Value)
Session(&quot;NumberOfQuestions&quot = CInt(rsTest.Fields(&quot;NumberOfQuestions&quot.Value)
Session(&quot;PercentageToPass&quot = CInt(rsTest.Fields(&quot;PercentageToPass&quot.Value)

' Close and dispose of our DB objects
rsTest.Close
Set rsTest = Nothing
cnnTest.Close
Set cnnTest = Nothing

' Set our question indicator to 1 and init our answer string
iQuestionID = 1
Session(&quot;AnswerString&quot = &quot;|&quot;
Else
'Check to be sure we've still got a session!
If Session(&quot;AnswerString&quot = &quot;&quot; Then
Response.Write &quot;I'm sorry, but you've taken too long. You can start over by &quot;
Response.Write &quot;clicking <A HREF=&quot;&quot;&quot; & Request.ServerVariables(&quot;URL&quot & &quot;&quot;&quot;>here</A>.&quot;
'to get the script to complete declared and set a Flag
'Response.End
bAbort = True
End If

' Get the number of the question we're processing
iQuestionID = CInt(Request.QueryString(&quot;qid&quot)

' Log selected answer to last question
Session(&quot;AnswerString&quot = Session(&quot;AnswerString&quot & iQuestionID & &quot;|&quot; & _
GetLetterFromAnswerNumber(CInt(Request.QueryString(&quot;sa&quot)) & &quot;|&quot;

' Increment question identifier
iQuestionID = iQuestionID + 1
End If

' If session has expired then skip all the code.
' Equivalently, only run all the code if it hasn't!
If Not bAbort Then

' Set this to a local variable to avoid accessing the collection each time
' This isn't required, but makes it easier for me to access and
' supposedly speeds it up... I'm not sure how much, but it can't hurt!
iNumberOfQuestions = Session(&quot;NumberOfQuestions&quot

' Check to see it the quiz is over. If so then show results, o/w
' ask the next question
If iQuestionID > iNumberOfQuestions Then
' Process results and show end quiz status report

' Done for the same reason as for iNumberOfQuestions a few lines above
strAnswers = Session(&quot;AnswerString&quot

' Useful for debugging
'Response.Write strAnswers & &quot;<BR>&quot; & vbCrLf & &quot;<BR>&quot; & vbCrLf

' Bug hunting once again... I didn't even come across any real bugs on this trip!
' Could you imagine the ammo I'd take if it was real hunting I was doing!
'For I = 1 to iNumberOfQuestions
' Response.Write GetAnswerFromAnswerString(I, strAnswers) & &quot;<BR>&quot; & vbCrLf
'Next 'I

' Retrieve Correct Answers and compare to the entered ones

' Code to use DB!
' Create DB connection and connect to the DB
Set cnnTest = Server.CreateObject(&quot;ADODB.Connection&quot
cnnTest.Open objConn

' Create RS and query DB for quiz info
Set rsTest = Server.CreateObject(&quot;ADODB.Recordset&quot
' Specify 3, 1 (Static, Read Only)
rsTest.Open &quot;SELECT * FROM Question WHERE TestID=&quot; & TestID & _
&quot; ORDER BY QuestionID;&quot;, cnnTest, 3, 1

iScore = 0
I = 1
Do While Not rsTest.EOF
If UCase(CStr(rsTest.Fields(&quot;CorrectAnswer&quot.Value)) = _
GetAnswerFromAnswerString(I, strAnswers) Then

iScore = iScore + 1
' This and the Else could be used to output a
' correctness status for each question
' Also useful for bug hunting!
'Response.Write &quot;Right&quot; & &quot;<BR>&quot; & vbCrLf
Else
'Response.Write &quot;Wrong&quot; & &quot;<BR>&quot; & vbCrLf
strResults = strResults & I & &quot;, &quot;
End If
I = I + 1
rsTest.MoveNext
Loop

' Close and dispose of our DB objects
rsTest.Close
Set rsTest = Nothing
cnnTest.Close
Set cnnTest = Nothing

' Convert score to a percentage rounded to the whole number
iScore = Round((iScore / iNumberOfQuestions) * 100)
%>
<FONT SIZE=&quot;+2&quot;><B><%= Session(&quot;TestName&quot %></B></FONT><BR>
<BR>
<%
If iScore >= Session(&quot;PercentageToPass&quot Then
Response.Write &quot;Congratulations! You've passed the quiz with a score of &quot;
Response.Write iScore & &quot;%.<BR>&quot; & vbCrLf
Else
Response.Write &quot;Sorry! You needed to achieve a score of &quot;
Response.Write Session(&quot;PercentageToPass&quot & &quot;% or higher to pass. &quot;
Response.Write &quot;Unfortunately, your score was only &quot; & iScore & &quot;%. &quot;
Response.Write &quot;You can take the test again by clicking <A HREF=&quot;&quot;&quot;
Response.Write Request.ServerVariables(&quot;URL&quot & &quot;&quot;&quot;>here</A>.<BR>&quot; & vbCrLf
End If
Response.Write &quot;<BR>&quot; & vbCrLf
If Len(strResults) <> 0 Then
Response.Write &quot;You missed the following questions: &quot; & Left(strResults, Len(strResults) - 2)
Response.Write &quot;<BR>&quot; & vbCrLf
End If
'Response.Write iScore & &quot;%&quot;

' This is also where you could log the results if you wanted to.
' In it's simplest form, you would simply log strAnswers to a file,
' but you could format little &quot;report cards&quot; or log the result to a
' separate data source.
Else
' Retrieve and Set the Question Info

' Code to use DB!
' Create DB connection and connect to the DB
Set cnnTest = Server.CreateObject(&quot;ADODB.Connection&quot
cnnTest.Open objConn

' Create RS and query DB for quiz info
Set rsTest = Server.CreateObject(&quot;ADODB.Recordset&quot
rsTest.Open &quot;SELECT * FROM Question WHERE TestID=&quot; _
& TestID & &quot; AND QuestionID=&quot; & iQuestionID & &quot;;&quot;, cnnTest

' Set our question info
strQuestionText = CStr(rsTest.Fields(&quot;Stem&quot.Value)

' Get an array of answers
aAnswers = Array( _
CStr(rsTest.Fields(&quot;AnswerA&quot.Value & &quot;&quot, _
CStr(rsTest.Fields(&quot;AnswerB&quot.Value & &quot;&quot, _
CStr(rsTest.Fields(&quot;AnswerC&quot.Value & &quot;&quot, _
CStr(rsTest.Fields(&quot;AnswerD&quot.Value & &quot;&quot, _
CStr(rsTest.Fields(&quot;AnswerE&quot.Value & &quot;&quot, _
CStr(rsTest.Fields(&quot;AnswerF&quot.Value & &quot;&quot, _
CStr(rsTest.Fields(&quot;AnswerG&quot.Value & &quot;&quot, _
CStr(rsTest.Fields(&quot;AnswerH&quot.Value & &quot;&quot, _
CStr(rsTest.Fields(&quot;AnswerI&quot.Value & &quot;&quot, _
CStr(rsTest.Fields(&quot;AnswerJ&quot.Value & &quot;&quot)

' This is probably bad coding style, but too bad... it works!
For I = LBound(aAnswers) To UBound(aAnswers)
If aAnswers(I) = &quot;&quot; Then
ReDim Preserve aAnswers(I - 1)
Exit For
End If
Next ' I

' Close and dispose of our DB objects
rsTest.Close
Set rsTest = Nothing
cnnTest.Close
Set cnnTest = Nothing

' Now that we've got the variables set...
' show the appropriate question and choices
%>

<FONT SIZE=&quot;+2&quot;><B><%= Session(&quot;TestName&quot %></B></FONT><BR>

<BR>

Progress Indicator:
<%
Const BAR_LENGTH = 160
If iQuestionID = 1 Then
' Since a 0 width is ignored by the browsers we need to remove the image altogether!
Response.Write &quot;<IMG SRC=&quot;&quot;./images/spacer_red.gif&quot;&quot; HEIGHT=&quot;&quot;10&quot;&quot; WIDTH=&quot;&quot;&quot;
Response.Write BAR_LENGTH
Response.Write &quot;&quot;&quot;><BR>&quot;
Else
Response.Write &quot;<IMG SRC=&quot;&quot;./images/spacer_blue.gif&quot;&quot; HEIGHT=&quot;&quot;10&quot;&quot; WIDTH=&quot;&quot;&quot;
Response.Write (BAR_LENGTH / iNumberOfQuestions) * (iQuestionID - 1)
Response.Write &quot;&quot;&quot;>&quot;
Response.Write &quot;<IMG SRC=&quot;&quot;./images/spacer_red.gif&quot;&quot; HEIGHT=&quot;&quot;10&quot;&quot; WIDTH=&quot;&quot;&quot;
Response.Write (BAR_LENGTH / iNumberOfQuestions) * (iNumberOfQuestions - (iQuestionID - 1))
Response.Write &quot;&quot;&quot;><BR>&quot;
End If
%>
Question <%= iQuestionID %> of <%= iNumberOfQuestions %><BR>

<BR>

<STRONG><%= iQuestionID %>.</STRONG>&nbsp;&nbsp;<%= strQuestionText %><BR>

<BR>

<STRONG>Choices:</STRONG>

<OL TYPE=&quot;A&quot;>
<%
For I = LBound(aAnswers) to UBound(aAnswers)
Response.Write &quot;<LI><A HREF=&quot;&quot;&quot; & Request.ServerVariables(&quot;URL&quot
Response.Write &quot;?qid=&quot; & iQuestionID & &quot;&sa=&quot; & I & &quot;&quot;&quot;>&quot;
Response.Write aAnswers(I) & &quot;</A></LI>&quot; & vbCrLf
Next 'I
%>
</OL>
<%
End If
End If 'bAbort
%>

Can anyone end the head scratching for me?!