Help with Tough Stuff - Inserting into Multiple Tables

Dev Shed Forums Sponsor:

SlickEdit: Code in over 40 languages across 7 platforms. SlickEdit’s unmatched power, speed, and flexibility allows even the most accomplished developers to write better code faster. Download a free trial today!

March 1st, 2002, 09:39 PM

BigAppa

Junior Member

Join Date: Feb 2002

Posts: 0

Time spent in forums: < 1 sec
Reputation Power: 0

Help with Tough Stuff - Inserting into Multiple Tables

Hi,

I'm trying to create an asp web page with multiple tables that insert into multiple tables in a database I created. I tried making a simple test page to see if it works. But when I hit the submit button, only one of my tables ("Sweet2") and the 2 fields with in it ("Spot3" & "Spot4" get filled with the test data I entered into the fields. The first table ("Sweet1") is empty.

Is it possible to submit to multiple database tables from one page? If so how? What am I doing wrong here?

PS. The simple database was created with Access and I mocked up the basic page and script using Dreamweaver UltraDev then edited the code to include a loop.

Any help would be very greatly appreciated cuz this thing is driving me nuts. Thanks!

<%@LANGUAGE="JAVASCRIPT"%>

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

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

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

// query string to execute
var MM_editQuery = "";

%>

<%
// *** Insert Record: set variables

if (String(Request("MM_insert")) != "undefined") {

var MM_editConnection = MM_conSweetSpot_STRING;
var MM_editRedirectUrl = "SweetSpot_Successful.asp";

for (var nCount = 0; nCount < 2; nCount++)
{//myLoop

if (nCount == 0)
{
var MM_editTable = "Sweet1";
var MM_fieldsStr = "tfSpot1|value|tfSpot2|value";
var MM_columnsStr = "Spot1|',none,''|Spot2|',none,''";
}
else
{
var MM_editTable = "Sweet2";
var MM_fieldsStr = "tfSpot3|value|tfSpot4|value";
var MM_columnsStr = "Spot3|',none,''|Spot4|',none,''";
}

// 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.length > 0) {
MM_editRedirectUrl += ((MM_editRedirectUrl.indexOf('?') == -1)?"?":"&") + Request.QueryString;
}
}
%>

<%
// *** Insert Record: construct a sql insert statement and execute it

if (String(Request("MM_insert")) != "undefined") {

// create the sql insert statement
var MM_tableValues = "", MM_dbValues = "";
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_tableValues += ((i != 0) ? "," : "") + MM_columns[i];
MM_dbValues += ((i != 0) ? "," : "") + formVal;
}

MM_editQuery = "insert into " + MM_editTable + " (" + MM_tableValues + ") values (" + MM_dbValues + ")";

} //myLoop

if (!MM_abortEdit)
{
// execute the insert
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);
}
}

}

%>

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=euc-kr">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<form name="form1" method="POST" action="<%=MM_editAction%>">
<table width="75%" border="1">
<tr>
<td width="50%">
<input type="text" name="tfSpot1" size="30">
</td>
<td width="50%">
<input type="text" name="tfSpot2" size="30">
</td>
</tr>
</table>
<p> </p>
<table width="75%" border="1">
<tr>
<td>
<input type="text" name="tfSpot3" size="30">
</td>
<td>
<input type="text" name="tfSpot4" size="30">
</td>
</tr>
</table>
<p> </p>
<p>
<input type="submit" name="btnSubmit" value="Submit">
</p>
<input type="hidden" name="MM_insert" value="true">
</form>
</body>
</html>

March 2nd, 2002, 04:49 AM

pabloj

Modding: Oracle MsSQL Firebird

Dev Shed God 6th Plane (7500 - 7999 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 7,620

Time spent in forums: 2 Months 2 Weeks 4 Days 1 h 57 m 46 sec
Reputation Power: 259

Hello!!, hello!, anyone in there?
You said

Quote:

I'm trying to create an asp web page ....

but this is Java Servlets & JSP forum!

__________________
My blog about OpenSource Databases
PDF tutorials about OSS databases, DBMonster ...
Please contribute to Open Source Development, fill bug reports!!!
Developer Shed eSupport
Commented my.ini/my.cnf (PLEASE ADD YOUR OWN CONFIG TRICK)
An introduction to database normalization
Natural or Surrogate key
Custom ordering for your results
Correlated and uncorrelated subqueries
Don't turn your outer joins into inner joins

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: