<font face='Arial'>
I want to create a form so that students at my school can register for classes. The data from the form would be added to an Access database from which the data would be imported into our student information system (SIS).

In order to meet requirements for our SIS, each course entry must be a separate record in the DB. The table has four fields: student ID, course ID, term and school year. Term and School Year would be set to a default entry for each record. An example...if one student enrolled in 8 courses, then 8 records would be inserted into the DB, each record would have all four fields completed (student ID, term and year would all be repeated).

I would like to create a form in which students select their courses from dynamically populated lists and then that data would be added to the Access DB. (I can handle the dynamic lists part)

I want to make it so that students select up to 8 courses on one form. Once the form is submitted, then one record would be created for each course. The student ID would have to be automatically submitted along with each course.

If a student only selects 5 courses, then only 5 records should be submitted.

I would appreciate any help I can get.

Thanks
Jay

</font id='Arial'>

OK, do you want me to him, haw, nudge and push...or would you like for me to simply write the code?

Option 2 would probably be easier for both of us...

Besides, being married to a teacher and being the resident webhead for a large metropolitan school system I am well aware of just how little time those in education have to devote to endeavors such as these and I'm always glad to help...

Edited by - on 02 Dec 2002 05:29:56

Dave

Thanks so much for your offer to help!

If you could give me a suggestion on the coding I'd be very appreciative.

I am envisioning the page having a series of checkboxes with one for each course. The label on each box would be the common name for the course and I could set each checked value to be the official course code that needs to be submitted into the DB.

Let me know if you need more info.

Once again, thanks for the help.

Jay

Sorry for the delay...many fires to put out after the long weekend...

The easiest way I know how to help someone along is to write the code and then comment the hell out of it...so that's what I did...the following is the entire code for a one page form/action combo...if you have any questions let me know.

&lt;%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%&gt;
&lt;%
' ::::: DECLARE OUR VARIABLES

Dim iCourse
Dim varCID
Dim rsRW

' ::::: CHECK TO SEE IF THIS IS INITIAL PAGE LOAD OR IF THE FORM HAS BEEN SUBMITTED

If Request.Form("doit" &lt;&gt; "" then


' ::::: IF THE FORM HAS BEEN SUBMITTED BUILD A FOR-TO-NEXT STATEMENT WHERE THE UPPER
' ::::: LIMIT (IN THIS CASE '8') IS THE NUMBER OF QUESTIONS IN THE FORM

For iCourse = 1 to 8

' ::::: BUILD A VARIABLE THAT EQUALS THE NAME OF THE FORM FIELD FOR YOUR COURSES
varCID = "c" & iCourse

' ::::: DETERMINE IF THE FORM FIELD [CHECKBOX] NAMED ICOURSE HAS BEEN CHECKED...
' ::::: IF IT HAS, OPEN THE RECORDSET, CREATE A NEW RECORD AND POPULATE THE FIELDS WITH THE
' ::::: DATA SUBMITTED IN THE FORM

If Request.Form(varcid) &lt;&gt; "" Then
Set rsRW = Server.CreateObject("ADODB.Recordset"
rsRW.ActiveConnection = "Driver={SQL Server};Server=your_server;Database=your_DB;Uid=user;Pwd=pw;"
rsRW.Source = "SELECT * FROM dbo.dmxzoneDEMO WHERE ID=0;"
rsRW.CursorType = 0
rsRW.CursorLocation = 2
rsRW.LockType = 3
rsRW.Open()
rsRW.AddNew
rsRW.Fields.Item("name".Value = Request.Form("name"
rsRW.Fields.Item("course".value = Request.Form(varCID)
rsRW.Update
rsRW.Close
set rsRW = Nothing
End If
Next

' :::::: THE NEXT LINE IS JUST THERE TO LET ME KNOW THE VBSCRIPT ABOVE HAS RUN

Response.Write "done"

End If

%&gt;

&lt;html&gt;
&lt;head&gt;
&lt;title&gt;Untitled Document&lt;/title&gt;
&lt;meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"&gt;
&lt;/head&gt;

&lt;body&gt;
&lt;!--
OBVIOUSLY THE VBSCRIPT ABOVE NEEDS FOR THE CHECKBOX NAMES IN THE FOLLOWING FORM TO BE NAMED
C AND A NUMBER AS IN C1 C2 C3 ET CETERA...AND THE VALUES OF THE CHECKBOXES ARE THE NAMES OF THE COURSES
--&gt;
&lt;form name="frmCourses" method="post" action="dmx.asp"&gt;
&lt;input name="name" type="text" id="name"&gt;
Name&lt;br&gt;
&lt;input name="c1" type="checkbox" id="c1" value="one"&gt;
Course 1&lt;br&gt;
&lt;input name="c2" type="checkbox" id="c2" value="two"&gt;
Course 2&lt;br&gt;
&lt;input name="c3" type="checkbox" id="c3" value="three"&gt;
Course 3&lt;br&gt;
&lt;input name="c4" type="checkbox" id="c4" value="four"&gt;
Course 4&lt;br&gt;
&lt;input name="c5" type="checkbox" id="c5" value="five"&gt;
Course 5&lt;br&gt;
&lt;input name="c6" type="checkbox" id="c6" value="six"&gt;
Course 6&lt;br&gt;
&lt;input name="c7" type="checkbox" id="c7" value="seven"&gt;
Course 7&lt;br&gt;
&lt;input name="c8" type="checkbox" id="c8" value="eight"&gt;
Course 8 &lt;br&gt;
&lt;input name="doit" type="hidden" id="doit" value="1"&gt;
&lt;br&gt;
&lt;input type="submit" name="Submit" value="Submit"&gt;
&lt;/form&gt;
&lt;/body&gt;
&lt;/html&gt;

Hope this helps.

Edited by - on 04 Dec 2002 03:56:17

Thank you for the code. This is VERY helpful. I'm starting on configuring our database now and will get to work on the ASP very soon.

You've helped me immensely and I truly appreciate it.

THANKS!

Hello Dave,

Good day. I am writing you becasue i saw how you helped someone back in 2002 and i depsrately need help myself so i was hoping you'll be able to give me some help.

I am retrieving some records from a recordset (which was made from two tables) within an access database and i want to be able to insert the records separately into another table in the database but what i get is everything get inserted into the database as one record(something like EmpID: (2,3,4) FirstName: (Oyeyemi, Gboya, Hilda) LastName: (Oliyide, Odulate, Dokubo)) instead of EmpID: (2) FirstName: (Oyeyemi) LastName: (oliyide) , EmpID: (3) FirstName: (Gboya) LastName: (Odulate), EmpID: (4) FirstName: (Hilda) LastName: (Dokubo) etc. I will paste the code i generated from dreamweaver below. I will be grateful if you can point out where the error is to me. Thank you in anticipation of your help.

&lt;%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%&gt;
&lt;%
' *** Restrict Access To Page: Grant or deny access to this page
MM_authorizedUsers=""
MM_authFailedURL="../loginrequired.asp"
MM_grantAccess=false
If Session("MM_Username" &lt;&gt; "" Then
If (true Or CStr(Session("MM_UserAuthorization")="" Or _
(InStr(1,MM_authorizedUsers,Session("MM_UserAuthorization")&gt;=1) Then
MM_grantAccess = true
End If
End If
If Not MM_grantAccess Then
MM_qsChar = "?"
If (InStr(1,MM_authFailedURL,"?" &gt;= 1) Then MM_qsChar = "&"
MM_referrer = Request.ServerVariables("URL"
if (Len(Request.QueryString()) &gt; 0) Then MM_referrer = MM_referrer & "?" & Request.QueryString()
MM_authFailedURL = MM_authFailedURL & MM_qsChar & "accessdenied=" & Server.URLEncode(MM_referrer)
Response.Redirect(MM_authFailedURL)
End If
%&gt;

&lt;%
' *** Edit Operations: declare variables

Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME")
If (Request.QueryString &lt;&gt; "" Then
MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%&gt;
&lt;%
' *** Insert Record: set variables

If (CStr(Request("MM_insert") = "book_engr" Then

MM_editConnection = "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\domains\zionwebhosting.com\wwwroot\olivehrm\data\prologic.mdb"
MM_editTable = "BookEngr"
MM_editRedirectUrl = "engrBook_success.asp"
MM_fieldsStr = "JobID|value|EmployeeID|value|firstName|value|lastName|value"
MM_columnsStr = "JobID|',none,''|EmployeeID|',none,''|FirstName|',none,''|LastName|',none,''"

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|"
MM_columns = Split(MM_columnsStr, "|"

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

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

End If
%&gt;
&lt;%
' *** Insert Record: construct a sql insert statement and execute it

Dim MM_tableValues
Dim MM_dbValues

If (CStr(Request("MM_insert") &lt;&gt; "" Then

' create the sql insert statement
MM_tableValues = ""
MM_dbValues = ""
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_formVal = MM_fields(MM_i+1)
MM_typeArray = Split(MM_columns(MM_i+1),","
MM_delim = MM_typeArray(0)
If (MM_delim = "none" Then MM_delim = ""
MM_altVal = MM_typeArray(1)
If (MM_altVal = "none" Then MM_altVal = ""
MM_emptyVal = MM_typeArray(2)
If (MM_emptyVal = "none" Then MM_emptyVal = ""
If (MM_formVal = "" Then
MM_formVal = MM_emptyVal
Else
If (MM_altVal &lt;&gt; "" Then
MM_formVal = MM_altVal
ElseIf (MM_delim = "'" Then ' escape quotes
MM_formVal = "'" & Replace(MM_formVal,"'","''" & "'"
Else
MM_formVal = MM_delim + MM_formVal + MM_delim
End If
End If
If (MM_i &lt;&gt; LBound(MM_fields)) Then
MM_tableValues = MM_tableValues & ","
MM_dbValues = MM_dbValues & ","
End If
MM_tableValues = MM_tableValues & MM_columns(MM_i)
MM_dbValues = MM_dbValues & MM_formVal
Next

MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & " values (" & MM_dbValues & ""


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

If (MM_editRedirectUrl &lt;&gt; "" Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If
%&gt;
&lt;%
Dim rsBookEngr__MMColParam
rsBookEngr__MMColParam = "1"
If (Request.QueryString("JobID" &lt;&gt; "" Then
rsBookEngr__MMColParam = Request.QueryString("JobID"
End If
%&gt;
&lt;%
Dim rsBookEngr
Dim rsBookEngr_numRows

Set rsBookEngr = Server.CreateObject("ADODB.Recordset"
rsBookEngr.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\domains\zionwebhosting.com\wwwroot\olivehrm\data\prologic.mdb"
rsBookEngr.Source = "SELECT JobID, EmployeeID, FirstName, LastName FROM JobBookings, Engineers WHERE JobID = " + Replace(rsBookEngr__MMColParam, "'", "''" + ""
rsBookEngr.CursorType = 0
rsBookEngr.CursorLocation = 2
rsBookEngr.LockType = 1
rsBookEngr.Open()

rsBookEngr_numRows = 0
%&gt;
&lt;%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = 10
Repeat1__index = 0
rsBookEngr_numRows = rsBookEngr_numRows + Repeat1__numRows
%&gt;
&lt;!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt;
&lt;html xmlns="www.w3.org/1999/xhtml"&gt;
&lt;head&gt;
&lt;meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /&gt;
&lt;title&gt;Book Job&lt;/title&gt;
&lt;style type="text/css"&gt;
&lt;!--
.style2 {
font-family: Georgia, "Times New Roman", Times, serif;
font-weight: bold;
}
--&gt;
&lt;/style&gt;
&lt;/head&gt;

&lt;body topmargin="0" leftmargin="0" rightmargin="0" bottommargin="0"&gt;
&lt;div align="center"&gt;
&lt;table width="700" height="359" border="1" align="center" cellpadding="4" cellspacing="0" bordercolor="#6699FF"&gt;
&lt;tr&gt;
&lt;td height="55" align="center" bgcolor="#6699FF"&gt;&lt;span class="style2"&gt;Human Resources Application &lt;/span&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td height="302"&gt;&lt;form ACTION="&lt;%=MM_editAction%&gt;" METHOD="POST" id="book_engr" name="book_engr".Value)%&gt;

&lt;div align="center"&gt;
&lt;p&gt;&lt;u&gt;&lt;strong&gt;Book Engineer For Job &lt;/strong&gt;&lt;/u&gt;&lt;br /&gt;
&lt;/p&gt;
&lt;table width="100%" border="1" cellpadding="2" cellspacing="0" bordercolor="#999966"&gt;
&lt;tr&gt;
&lt;td width="15%" bgcolor="#999966"&gt;&lt;strong&gt;JobID&lt;/strong&gt;&lt;/td&gt;
&lt;td width="19%" bgcolor="#999966"&gt;&lt;strong&gt;EmpolyeeID&lt;/strong&gt;&lt;/td&gt;
&lt;td width="23%" bgcolor="#999966"&gt;&lt;strong&gt;First Name &lt;/strong&gt;&lt;/td&gt;
&lt;td width="17%" bgcolor="#999966"&gt;&lt;strong&gt; Last Name &lt;/strong&gt;&lt;/td&gt;
&lt;td width="26%" bgcolor="#999966"&gt;&lt;strong&gt;Select Engineer &lt;/strong&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;%
While ((Repeat1__numRows &lt;&gt; 0) AND (NOT rsBookEngr.EOF))
%&gt;
&lt;tr&gt;

&lt;td&gt;&lt;label&gt;
&lt;input name="JobID" type="text" id="JobID" value="&lt;%=(rsBookEngr.Fields.Item("JobID".Value)%&gt;" /&gt;
&lt;/label&gt;&lt;/td&gt;
&lt;td&gt;&lt;label&gt;
&lt;input name="EmployeeID" type="text" id="EmployeeID" value="&lt;%=(rsBookEngr.Fields.Item("EmployeeID".Value)%&gt;" /&gt;
&lt;/label&gt;&lt;/td&gt;
&lt;td&gt;&lt;label&gt;
&lt;input name="firstName" type="text" id="firstName" value="&lt;%=(rsBookEngr.Fields.Item("FirstName".Value)%&gt;" /&gt;
&lt;/label&gt;&lt;/td&gt;
&lt;td&gt;&lt;label&gt;
&lt;input name="lastName" type="text" id="lastName" value="&lt;%=(rsBookEngr.Fields.Item("LastName".Value)%&gt;" /&gt;
&lt;/label&gt;&lt;/td&gt;
&lt;td align="center"&gt;&lt;label&gt;
&lt;input name="Insert" type="checkbox" id="Insert" value="Yes" /&gt;
&lt;/label&gt;&lt;/td&gt;

&lt;/tr&gt;
&lt;%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsBookEngr.MoveNext()
Wend
%&gt;
&lt;tr&gt;
&lt;td colspan="4"&gt;&nbsp;&lt;/td&gt;
&lt;td&gt;&lt;label&gt;
&lt;input type="submit" name="Submit" value="Book Engineers" /&gt;
&lt;/label&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;
&lt;p&gt;&lt;br /&gt;
&lt;/p&gt;
&lt;/div&gt;





&lt;input type="hidden" name="MM_insert" value="book_engr"&gt;
&lt;/form&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;
&lt;/div&gt;

&lt;/body&gt;
&lt;/html&gt;
&lt;%
rsBookEngr.Close()
Set rsBookEngr = Nothing
%&gt;

Looking forward to hearing from you.

Thanks a lot.

Oyeyemi

Hi there,
I'm looking more or less for the same solution, I'm trying to insert multiple rows filtered by a multi selection list:
www.dmxzone.com/ShowDetail.asp?NewsId=4233
Therefore the selection is dynamic.
from a web table form into multiple rows into an SQL table:
www.webthang.co.uk/goto/tutorials/000138/?cat=2&tut=138
www.dmxzone.com/forum/topic.asp?TOPIC_ID=30617&FORUM_ID=3&CAT_ID=2&Topic_Title=Insert+Multiple+Records+in+one+table+at+same+time&Forum_Title=Dreamweaver+UltraDev

I am almost there, the multi selection works like a dream, it's only the insert bit that doesn't work even though I'm not getting an error message...
www.dmxzone.com/forum/topic.asp?topic_id=42024
Did you find a solution? Thanks in advance
Ed

Edited by - edtrvl on 17 Sep 2008 12:36:53

Edited by - edtrvl on 17 Sep 2008 12:37:23