#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2002
    Posts
    0
    Rep 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"%>
    <!--#include file="Connections/conSweetSpot.asp" -->
    <%
    // *** 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>&nbsp;</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>&nbsp;</p>
    <p>
    <input type="submit" name="btnSubmit" value="Submit">
    </p>
    <input type="hidden" name="MM_insert" value="true">
    </form>
    </body>
    </html>
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539

IMN logo majestic logo threadwatch logo seochat tools logo