Insert multiple new records in one stage

[martakeithy]

Hello,
I am using Ultradev to develop an on-line quiz. There are two tables involved in the quiz itself: One containing the questions and answers, and one for storing the response of the quiz taker, together with their unique id.
I have a page with navigation bars at the bottom, displaying a recordset containing around 10 records taken from the question table. Each page displays one of the ten questions, and the next question is then moved to via the navigation bar.
On the same page, for each question I have a form which inserts the quiz-taker's response. This form is one created using a wizard. The problem is that each answer to each question has to be submitted separately using the form created, whereas I would like to submit all of the answers together, at the end, when the quiz-taker has selected answers to all ten records.
Can anyone suggest a method by which I can stick in a submit button which will insert all ten new records.
Thank you for any help.
Regards
Marta Keithy

 

[ToddWW]

Well Marta,

Here's how I would do that. First I would initiate a Session dictionary object. Basically, that's a fancy array that can be stored to a session variable. You do that like this.

set Session("inserts") = Server.CreateObject("Scripting.Dictionary")

You do that before anything.

Then, instead of ..
Conn.Execute strSQL which adds a single record,
You add the SQL string to the Dictionary Object instead.
Session("inserts&quot.Add strSQL, ""

Then on a single page, something like this.

dim arrSQL
arrSQL = Session("inserts").Keys

Conn.BeginTrans
FOR i = 0 TO Session("inserts").Count - 1
  Conn.Execute arrSQL(i)
NEXT
IF Conn.Errors.Count = 0
  Conn.CommitTrans
  Response.Redirect "somesuccesspage.asp"
ELSE
  Conn.RollbackTrans
  Response.Redirect "somefailurepage.asp"
END IF

ToddWW

 

[SashiRaveendran]

You could put 10 hidden fields on the 10 different question pages.

1.When Question X is answered, assign answer to hidden field X then move to page X+1

2.On page X+1 read all 10 hidden field values and assign them back to the hidden fields on this page (X+1)

repeat steps 1 and 2 for each question page

Finally, you should have submit button on the last question page that submits the 10 hidden field values to the server

 

[martakeithy]

I have tried to alter the manner in which the data is sent to the database, by using the scripting dictionary. However I am getting confused by the code written by Ultradev. I have included the code below <%@LANGUAGE=&quot;VBSCRIPT&quot;%>
<!--#include file=&quot;../Connections/CNDS.asp&quot; -->
<%
' *** Edit Operations: declare variables
set Session(&quot;inserts&quot = Server.CreateObject(&quot;Scripting.Dictionary&quot
MM_editAction = CStr(Request(&quot;URL&quot)
If (Request.QueryString <> &quot;&quot Then
MM_editAction = MM_editAction & &quot;?&quot; & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = &quot;&quot;
%>
<%
' *** Insert Record: set variables

If (CStr(Request(&quot;MM_insert&quot) <> &quot;&quot Then

MM_editConnection = MM_CNDS_STRING
MM_editTable = &quot;Marks&quot;
MM_editRedirectUrl = &quot;StudentAmend.asp&quot;
MM_fieldsStr = &quot;Choicea|value|Choiceb|value|Choicec|value|Choiced|value|MatriculationNo|value|QuestionID|value&quot;
MM_columnsStr = &quot;Choicea|none,Yes,No|Choiceb|none,Yes,No|Choicec|none,Yes,No|Choiced|none,Yes,No|MatriculationNo|none,none,NULL|QuestionID|none,none,NULL&quot;

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, &quot;|&quot
MM_columns = Split(MM_columnsStr, &quot;|&quot

' set the form values
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(i+1) = CStr(Request.Form(MM_fields(i)))
Next

' append the query string to the redirect URL
If (MM_editRedirectUrl <> &quot;&quot; And Request.QueryString <> &quot;&quot Then
If (InStr(1, MM_editRedirectUrl, &quot;?&quot;, vbTextCompare) = 0 And Request.QueryString <> &quot;&quot Then
MM_editRedirectUrl = MM_editRedirectUrl & &quot;?&quot; & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & &quot;&&quot; & Request.QueryString
End If
End If

End If
%>
<%
' *** Insert Record: construct a sql insert statement and execute it

If (CStr(Request(&quot;MM_insert&quot) <> &quot;&quot Then

' create the sql insert statement
MM_tableValues = &quot;&quot;
MM_dbValues = &quot;&quot;
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
FormVal = MM_fields(i+1)
MM_typeArray = Split(MM_columns(i+1),&quot;,&quot
Delim = MM_typeArray(0)
If (Delim = &quot;none&quot Then Delim = &quot;&quot;
AltVal = MM_typeArray(1)
If (AltVal = &quot;none&quot Then AltVal = &quot;&quot;
EmptyVal = MM_typeArray(2)
If (EmptyVal = &quot;none&quot Then EmptyVal = &quot;&quot;
If (FormVal = &quot;&quot Then
FormVal = EmptyVal
Else
If (AltVal <> &quot;&quot Then
FormVal = AltVal
ElseIf (Delim = &quot;'&quot Then ' escape quotes
FormVal = &quot;'&quot; & Replace(FormVal,&quot;'&quot;,&quot;''&quot & &quot;'&quot;
Else
FormVal = Delim + FormVal + Delim
End If
End If
If (i <> LBound(MM_fields)) Then
MM_tableValues = MM_tableValues & &quot;,&quot;
MM_dbValues = MM_dbValues & &quot;,&quot;
End if
MM_tableValues = MM_tableValues & MM_columns(i)
MM_dbValues = MM_dbValues & FormVal
Next
MM_editQuery = &quot;insert into &quot; & MM_editTable & &quot; (&quot; & MM_tableValues & &quot values (&quot; & MM_dbValues & &quot&quot;

If (Not MM_abortEdit) Then
' execute the insert
Set MM_editCmd = Server.CreateObject(&quot;ADODB.Command&quot
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

If (MM_editRedirectUrl <> &quot;&quot Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If
%>
<%
Dim getQuestion
Dim selectQuestion__MMColParam
selectQuestion__MMColParam = &quot;1&quot;
if (Request.QueryString(&quot;UnitNo&quot <> &quot;&quot then selectQuestion__MMColParam = Request.QueryString(&quot;UnitNo&quot
%>
<%
set selectQuestion = Server.CreateObject(&quot;ADODB.Recordset&quot
selectQuestion.ActiveConnection = MM_CNDS_STRING
selectQuestion.Source = &quot;SELECT QuestionID, Question, Answera, Answerb, Answerc, Answerd, UnitNo FROM Question WHERE UnitNo = &quot; + Replace(selectQuestion__MMColParam, &quot;'&quot;, &quot;''&quot + &quot;&quot;
selectQuestion.CursorType = 0
selectQuestion.CursorLocation = 2
selectQuestion.LockType = 3
selectQuestion.Open()
selectQuestion_numRows = 0
%>
<%
' *** Recordset Stats, Move To Record, and Go To Record: declare stats variables

' set the record count
selectQuestion_total = selectQuestion.RecordCount

' set the number of rows displayed on this page
If (selectQuestion_numRows < 0) Then
selectQuestion_numRows = selectQuestion_total
Elseif (selectQuestion_numRows = 0) Then
selectQuestion_numRows = 1
End If

' set the first and last displayed record
selectQuestion_first = 1
selectQuestion_last = selectQuestion_first + selectQuestion_numRows - 1

' if we have the correct record count, check the other stats
If (selectQuestion_total <> -1) Then
If (selectQuestion_first > selectQuestion_total) Then selectQuestion_first = selectQuestion_total
If (selectQuestion_last > selectQuestion_total) Then selectQuestion_last = selectQuestion_total
If (selectQuestion_numRows > selectQuestion_total) Then selectQuestion_numRows = selectQuestion_total
End If
%>
<%
' *** Move To Record and Go To Record: declare variables

Set MM_rs = selectQuestion
MM_rsCount = selectQuestion_total
MM_size = selectQuestion_numRows
MM_uniqueCol = &quot;&quot;
MM_paramName = &quot;&quot;
MM_offset = 0
MM_atTotal = false
MM_paramIsDefined = false
If (MM_paramName <> &quot;&quot Then
MM_paramIsDefined = (Request.QueryString(MM_paramName) <> &quot;&quot
End If
%>
<%
' *** Move To Record: handle 'index' or 'offset' parameter

if (Not MM_paramIsDefined And MM_rsCount <> 0) then

' use index parameter if defined, otherwise use offset parameter
r = Request.QueryString(&quot;index&quot
If r = &quot;&quot; Then r = Request.QueryString(&quot;offset&quot
If r <> &quot;&quot; Then MM_offset = Int(r)

' if we have a record count, check if we are past the end of the recordset
If (MM_rsCount <> -1) Then
If (MM_offset >= MM_rsCount Or MM_offset = -1) Then ' past end or move last
If ((MM_rsCount Mod MM_size) > 0) Then ' last page not a full repeat region
MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
Else
MM_offset = MM_rsCount - MM_size
End If
End If
End If

' move the cursor to the selected record
i = 0
While ((Not MM_rs.EOF) And (i < MM_offset Or MM_offset = -1))
MM_rs.MoveNext
i = i + 1
Wend
If (MM_rs.EOF) Then MM_offset = i ' set MM_offset to the last possible record

End If
%>
<%
' *** Move To Record: if we dont know the record count, check the display range

If (MM_rsCount = -1) Then

' walk to the end of the display range for this page
i = MM_offset
While (Not MM_rs.EOF And (MM_size < 0 Or i < MM_offset + MM_size))
MM_rs.MoveNext
i = i + 1
Wend

' if we walked off the end of the recordset, set MM_rsCount and MM_size
If (MM_rs.EOF) Then
MM_rsCount = i
If (MM_size < 0 Or MM_size > MM_rsCount) Then MM_size = MM_rsCount
End If

' if we walked off the end, set the offset based on page size
If (MM_rs.EOF And Not MM_paramIsDefined) Then
If (MM_offset > MM_rsCount - MM_size Or MM_offset = -1) Then
If ((MM_rsCount Mod MM_size) > 0) Then
MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
Else
MM_offset = MM_rsCount - MM_size
End If
End If
End If

' reset the cursor to the beginning
If (MM_rs.CursorType > 0) Then
MM_rs.MoveFirst
Else
MM_rs.Requery
End If

' move the cursor to the selected record
i = 0
While (Not MM_rs.EOF And i < MM_offset)
MM_rs.MoveNext
i = i + 1
Wend
End If
%>
<%
' *** Move To Record: update recordset stats

' set the first and last displayed record
selectQuestion_first = MM_offset + 1
selectQuestion_last = MM_offset + MM_size
If (MM_rsCount <> -1) Then
If (selectQuestion_first > MM_rsCount) Then selectQuestion_first = MM_rsCount
If (selectQuestion_last > MM_rsCount) Then selectQuestion_last = MM_rsCount
End If

' set the boolean used by hide region to check if we are on the last record
MM_atTotal = (MM_rsCount <> -1 And MM_offset + MM_size >= MM_rsCount)
%>
<%
' *** Go To Record and Move To Record: create strings for maintaining URL and Form parameters

' create the list of parameters which should not be maintained
MM_removeList = &quot;&index=&quot;
If (MM_paramName <> &quot;&quot Then MM_removeList = MM_removeList & &quot;&&quot; & MM_paramName & &quot;=&quot;
MM_keepURL=&quot;&quot;:MM_keepForm=&quot;&quot;:MM_keepBoth=&quot;&quot;:MM_keepNone=&quot;&quot;

' add the URL parameters to the MM_keepURL string
For Each Item In Request.QueryString
NextItem = &quot;&&quot; & Item & &quot;=&quot;
If (InStr(1,MM_removeList,NextItem,1) = 0) Then
MM_keepURL = MM_keepURL & NextItem & Server.URLencode(Request.QueryString(Item))
End If
Next

' add the Form variables to the MM_keepForm string
For Each Item In Request.Form
NextItem = &quot;&&quot; & Item & &quot;=&quot;
If (InStr(1,MM_removeList,NextItem,1) = 0) Then
MM_keepForm = MM_keepForm & NextItem & Server.URLencode(Request.Form(Item))
End If
Next

' create the Form + URL string and remove the intial '&' from each of the strings
MM_keepBoth = MM_keepURL & MM_keepForm
if (MM_keepBoth <> &quot;&quot Then MM_keepBoth = Right(MM_keepBoth, Len(MM_keepBoth) - 1)
if (MM_keepURL <> &quot;&quot Then MM_keepURL = Right(MM_keepURL, Len(MM_keepURL) - 1)
if (MM_keepForm <> &quot;&quot Then MM_keepForm = Right(MM_keepForm, Len(MM_keepForm) - 1)

' a utility function used for adding additional parameters to these strings
Function MM_joinChar(firstItem)
If (firstItem <> &quot;&quot Then
MM_joinChar = &quot;&&quot;
Else
MM_joinChar = &quot;&quot;
End If
End Function
%>
<%
' *** Move To Record: set the strings for the first, last, next, and previous links

MM_keepMove = MM_keepBoth
MM_moveParam = &quot;index&quot;

' if the page has a repeated region, remove 'offset' from the maintained parameters
If (MM_size > 0) Then
MM_moveParam = &quot;offset&quot;
If (MM_keepMove <> &quot;&quot Then
params = Split(MM_keepMove, &quot;&&quot
MM_keepMove = &quot;&quot;
For i = 0 To UBound(params)
nextItem = Left(params(i), InStr(params(i),&quot;=&quot - 1)
If (StrComp(nextItem,MM_moveParam,1) <> 0) Then
MM_keepMove = MM_keepMove & &quot;&&quot; & params(i)
End If
Next
If (MM_keepMove <> &quot;&quot Then
MM_keepMove = Right(MM_keepMove, Len(MM_keepMove) - 1)
End If
End If
End If

' set the strings for the move to links
If (MM_keepMove <> &quot;&quot Then MM_keepMove = MM_keepMove & &quot;&&quot;
urlStr = Request.ServerVariables(&quot;URL&quot & &quot;?&quot; & MM_keepMove & MM_moveParam & &quot;=&quot;
MM_moveFirst = urlStr & &quot;0&quot;
MM_moveLast = urlStr & &quot;-1&quot;
MM_moveNext = urlStr & Cstr(MM_offset + MM_size)
prev = MM_offset - MM_size
If (prev < 0) Then prev = 0
MM_movePrev = urlStr & Cstr(prev)
%>
<html>
<head>
<title>Results/Question Page</title>
<meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=iso-8859-1&quot;>
</head>
<body bgcolor=&quot;#FFFFFF&quot; text=&quot;#000000&quot;>
<p>&nbsp;
</p>
<form ACTION=&quot;<%=MM_editAction%>&quot; METHOD=&quot;POST&quot; name=&quot;form1&quot;>
<p>Unit Number <%=(selectQuestion.Fields.Item(&quot;UnitNo&quot.Value)%></p>
<div align=&quot;center&quot;>
<table width=&quot;75%&quot; border=&quot;1&quot; cellspacing=&quot;3&quot; cellpadding=&quot;3&quot; height=&quot;544&quot;>
<tr align=&quot;center&quot; bordercolor=&quot;#FFFFFF&quot; valign=&quot;top&quot;>
<td width=&quot;1&quot; height=&quot;40&quot; bordercolor=&quot;#CCCCCC&quot;>
<p><font size=&quot;3&quot;><%=(selectQuestion.Fields.Item(&quot;Question&quot.Value)%></font></p>
</td>
<td width=&quot;12&quot; height=&quot;12&quot; rowspan=&quot;5&quot; valign=&quot;top&quot;>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<table align=&quot;center&quot; height=&quot;274&quot;>
<tr valign=&quot;middle&quot;>
<td nowrap align=&quot;right&quot; height=&quot;66&quot; width=&quot;103&quot; valign=&quot;bottom&quot;>
<div align=&quot;center&quot;><font size=&quot;3&quot;>
<input type=&quot;checkbox&quot; name=&quot;Choicea&quot; value=1 >
</font></div>
</td>
<td height=&quot;66&quot; width=&quot;169&quot; valign=&quot;bottom&quot;> <font size=&quot;3&quot;> </font></td>
</tr>
<tr valign=&quot;middle&quot;>
<td nowrap align=&quot;right&quot; height=&quot;62&quot; width=&quot;103&quot; valign=&quot;bottom&quot;>
<div align=&quot;center&quot;><font size=&quot;3&quot;>
<input type=&quot;checkbox&quot; name=&quot;Choiceb&quot; value=1 >
</font></div>
</td>
<td height=&quot;66&quot; width=&quot;169&quot; valign=&quot;bottom&quot;> <font size=&quot;3&quot;> </font></td>
</tr>
<tr valign=&quot;baseline&quot;>
<td nowrap align=&quot;right&quot; height=&quot;62&quot; valign=&quot;bottom&quot; width=&quot;103&quot;>
<div align=&quot;center&quot;><font size=&quot;3&quot;>
<input type=&quot;checkbox&quot; name=&quot;Choicec&quot; value=1 >
</font></div>
</td>
<td height=&quot;66&quot; width=&quot;169&quot; valign=&quot;bottom&quot;> <font size=&quot;3&quot;> </font></td>
</tr>
<tr valign=&quot;baseline&quot;>
<td nowrap align=&quot;right&quot; height=&quot;62&quot; valign=&quot;bottom&quot; width=&quot;103&quot;>
<div align=&quot;center&quot;><font size=&quot;3&quot;>
<input type=&quot;checkbox&quot; name=&quot;Choiced&quot; value=1 >
</font></div>
</td>
<td height=&quot;33&quot; width=&quot;169&quot; valign=&quot;bottom&quot;> <font size=&quot;3&quot;> </font></td>
</tr>
<tr valign=&quot;baseline&quot;>
<td nowrap align=&quot;right&quot; height=&quot;35&quot; valign=&quot;top&quot; width=&quot;103&quot;>&nbsp;</td>
<td height=&quot;35&quot; width=&quot;169&quot;> <font size=&quot;3&quot;>
<input type=&quot;hidden&quot; name=&quot;MatriculationNo&quot; value=&quot;<%= Session(&quot;MM_Matriculation&quot %>&quot; size=&quot;32&quot;>
</font></td>
</tr>
<tr valign=&quot;baseline&quot;>
<td nowrap align=&quot;right&quot; height=&quot;37&quot; valign=&quot;top&quot; width=&quot;103&quot;>&nbsp;</td>
<td height=&quot;37&quot; width=&quot;169&quot;> <font size=&quot;3&quot;>
<input type=&quot;hidden&quot; name=&quot;QuestionID&quot; value=&quot;<%=(selectQuestion.Fields.Item(&quot;QuestionID&quot.Value)%>&quot; size=&quot;32&quot;>
</font></td>
</tr>
</table>
<p>
</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p> <font size=&quot;3&quot;>
<input type=&quot;hidden&quot; name=&quot;MM_insert&quot; value=&quot;true&quot;>
</font></p>
<p>&nbsp; </p>
</td>
</tr>
<tr valign=&quot;top&quot;>
<td width=&quot;49%&quot; height=&quot;53&quot;>
<p><%=(selectQuestion.Fields.Item(&quot;Answera&quot.Value)%></p>
<p>&nbsp;</p>
</td>
</tr>
<tr valign=&quot;top&quot;>
<td width=&quot;49%&quot; height=&quot;59&quot;>
<p><%=(selectQuestion.Fields.Item(&quot;Answerb&quot.Value)%></p>
<p>&nbsp;</p>
</td>
</tr>
<tr>
<td width=&quot;49%&quot; height=&quot;71&quot;><%=(selectQuestion.Fields.Item(&quot;Answerc&quot.Value)%></td>
</tr>
<tr valign=&quot;top&quot;>
<td width=&quot;49%&quot; height=&quot;444&quot;>
<p><%=(selectQuestion.Fields.Item(&quot;Answerd&quot.Value)%></p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
</td>
</tr>
</table>
<input type=&quot;submit&quot; value=&quot;Insert Record&quot; name=&quot;submit&quot;>
</div>
<p align=&quot;center&quot;><%= Session(&quot;MM_Matriculation&quot %> </p>
<div align=&quot;center&quot;>
<table border=&quot;0&quot; width=&quot;50%&quot; align=&quot;center&quot;>
<tr>
<td width=&quot;23%&quot; align=&quot;center&quot;>
<div align=&quot;center&quot;>
<% If MM_offset <> 0 Then %>
<a href=&quot;<%=MM_moveFirst%>&quot;><img src=&quot;../OtherPages/First.gif&quot; border=0></a>
<% End If ' end MM_offset <> 0 %>
</div>
</td>
<td width=&quot;31%&quot; align=&quot;center&quot;>
<% If MM_offset <> 0 Then %>
<a href=&quot;<%=MM_movePrev%>&quot;><img src=&quot;../OtherPages/Previous.gif&quot; border=0></a>
<% End If ' end MM_offset <> 0 %>
</td>
<td width=&quot;23%&quot; align=&quot;center&quot;>
<% If Not MM_atTotal Then %>
<a href=&quot;<%=MM_moveNext%>&quot;><img src=&quot;../OtherPages/Next.gif&quot; border=0></a>
<% End If ' end Not MM_atTotal %>
</td>
<td width=&quot;23%&quot; align=&quot;center&quot;>
<% If Not MM_atTotal Then %>
<a href=&quot;<%=MM_moveLast%>&quot;><img src=&quot;../OtherPages/Last.gif&quot; border=0></a>
<% End If ' end Not MM_atTotal %>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
<%
selectQuestion.Close()
%>
I would be very grateful is someone could look at the insert statement and indicate how I can have the selections made by the quiz-taker, into the array, so that all the answers can be inserted at once.
Any ideas