Inserting Multiple Records into MSaccess Database

Hello,

Im using dreamweaver mx with msaccess databse and have come across a problem. I want to enter multiple records into the database but dreamweaver only allows me to enter one at a time.

Basically i want to update anywhere from 2-30 records at a time but ill use 5 as an example below, they are automatically generated from the database named i1 - i30.

I dont know how to edit dreamweaver code as its confusing and i dont know vbscript, i use javascript with asp and unfortunately its very hard to find any examples with javascript and asp.

Here is the form that i want to update:

<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<form name="form1" method="post" action="">
<input name="i1" type="text" id="i1" size="5" maxlength="2">
<br>
<br>
<input name="i2" type="text" id="i2" size="5" maxlength="2">
<br>
<br>
<input name="i3" type="text" id="i3" size="5" maxlength="2">
<br>
<br>
<input name="i4" type="text" id="i4" size="5" maxlength="2">
<br>
<br>
<input name="i5" type="text" id="i5" size="5" maxlength="2">
<br>
<br>
<input type="submit" name="Submit" value="Update">
</form>
</body>
</html>

Now here is the code that dreamweaver adds if i wanted to update the record. The only field in the table i would be updating would be the preference field and it would be a numeric value between 1-30.

<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>

<%
// *** Edit Operations: declare variables

// set the form action variable
var MM_editAction = Request.ServerVariables("SCRIPT_NAME");
if (Request.QueryString) {
MM_editAction += "?" + Request.QueryString;
}

// boolean to abort record edit
var MM_abortEdit = false;

// query string to execute
var MM_editQuery = "";
%>
<%
// *** Update Record: set variables

if (String(Request("MM_update")) == "form1" &&
String(Request("MM_recordId")) != "undefined") {

var MM_editConnection = MM_connection_STRING;
var MM_editTable = "tblWishList";
var MM_editColumn = "WishID";
var MM_recordId = "" + Request.Form("MM_recordId") + "";
var MM_editRedirectUrl = "myPage.asp";
var MM_fieldsStr = "i1|value";
var MM_columnsStr = "Preference|none,none,NULL";

// create the MM_fields and MM_columns arrays
var MM_fields = MM_fieldsStr.split("|");
var MM_columns = MM_columnsStr.split("|");

// set the form values
for (var i=0; i+1 < MM_fields.length; i+=2) {
MM_fields[i+1] = String(Request.Form(MM_fields[i]));
}

// append the query string to the redirect URL
if (MM_editRedirectUrl && Request.QueryString && Request.QueryString.Count > 0) {
MM_editRedirectUrl += ((MM_editRedirectUrl.indexOf('?') == -1)?"?":"&") + Request.QueryString;
}
}
%>
<%
// *** Update Record: construct a sql update statement and execute it

if (String(Request("MM_update")) != "undefined" &&
String(Request("MM_recordId")) != "undefined") {

// create the sql update statement
MM_editQuery = "update " + MM_editTable + " set ";
for (var i=0; i+1 < MM_fields.length; i+=2) {
var formVal = MM_fields[i+1];
var MM_typesArray = MM_columns[i+1].split(",");
var delim = (MM_typesArray[0] != "none") ? MM_typesArray[0] : "";
var altVal = (MM_typesArray[1] != "none") ? MM_typesArray[1] : "";
var emptyVal = (MM_typesArray[2] != "none") ? MM_typesArray[2] : "";
if (formVal == "" || formVal == "undefined") {
formVal = emptyVal;
} else {
if (altVal != "") {
formVal = altVal;
} else if (delim == "'") { // escape quotes
formVal = "'" + formVal.replace(/'/g,"''") + "'";
} else {
formVal = delim + formVal + delim;
}
}
MM_editQuery += ((i != 0) ? "," : "") + MM_columns[i] + " = " + formVal;
}
MM_editQuery += " where " + MM_editColumn + " = " + MM_recordId;

if (!MM_abortEdit) {
// execute the update
var 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) {
Response.Redirect(MM_editRedirectUrl);
}
}

}
%>
<%
var rsWishlist = Server.CreateObject("ADODB.Recordset");
rsWishlist.ActiveConnection = MM_connection_STRING;
rsWishlist.Source = "SELECT * FROM tblWishList";
rsWishlist.CursorType = 0;
rsWishlist.CursorLocation = 2;
rsWishlist.LockType = 1;
rsWishlist.Open();
var rsWishlist_numRows = 0;
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<form ACTION="<%=MM_editAction%>" METHOD="POST" name="form1">

<input name="i1" type="text" id="i1" size="5" maxlength="2">
<br>
<br>
<input type="submit" name="Submit" value="Update">
<input type="hidden" name="MM_update" value="form1">
<input type="hidden" name="MM_recordId" value="<%= rsWishlist.Fields.Item("WishID").Value %>">
</form>
</body>
</html>
<%
rsWishlist.Close();
%>

Now the problem is i dont understand how to edit that code for it to loop through the textboxes and update multiple records, keeping in mind that there wont be a set amount of textboxes, it could be 2,10,15,29 etc

I would be displaying the textboxes using a repeat region in dreamweaver as follows:

<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>

<%
var rsMyRecordset = Server.CreateObject("ADODB.Recordset");
rsMyRecordset.ActiveConnection = MM_connection_STRING;
rsMyRecordset.Source = "SELECT * FROM tblWishList";
rsMyRecordset.CursorType = 0;
rsMyRecordset.CursorLocation = 2;
rsMyRecordset.LockType = 1;
rsMyRecordset.Open();
var rsMyRecordset_numRows = 0;
%>
<%
var Repeat1__numRows = -1;
var Repeat1__index = 0;
rsMyRecordset_numRows += Repeat1__numRows;
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<form name="form1">
<%
var counter = 0;
while ((Repeat1__numRows-- != 0) && (!rsMyRecordset.EOF)) { %>
<input name="i<%=counter+1%>" type="text" id="i<%=counter+1%>" size="5" maxlength="2">
<%
counter ++;
Repeat1__index++;
rsMyRecordset.MoveNext();
}
%>
<br>
<br>
<input type="submit" name="Submit" value="Update">
</form>
</body>
</html>
<%
rsMyRecordset.Close();
%>

So i suppose you would probably set up an array inside the repeat region to hold the values of each textbox. Lastly ill include the hand written code i use to update records which might be helpful or might not be but ill include it all the same.

var adoConnection = Server.CreateObject("ADODB.Connection");
adoConnection.Open("provider=Microsoft.Jet.OLEDB.4.0; data source=c:\\home\\db\\myDatabase.mdb");
var SQL = "Update tblWishList SET Preference=" + String(Session("myPreference")) + " WHERE Preference= " + String(Session("oldPreference")) + " AND WishID = " + String(Session("myWishIdentity")) + " ";

adoConnection.Execute(SQL);
adoConnection.Close();
adoConnection = null;

So i would really appreciate it if anyone could help me in any way. Also if anyone knows of a good resource for asp and javascript tutorials as everywhere on the web seems to refer only to vbscript (and probably rightly so because asp and javascript is unusual)

Thanks in advance
Paul