Inserting data from one page into two tables
Introduction
UltraDev's Insert Record server behavior was designed to insert a record into one table. The standard alternatives to inserting records into more than one table are 1) build separate pages for each insert, 2) build the page by hand, or 3) use stored procedures.
This alternative shows how to modify UltraDev code to accomplish two inserts on the same page without using stored procedures. Although some hand manipulation of the UltraDev generated code is involved, it is a much simpler alternative to hand coding the insert behaviors from scratch. Expanding on the concepts explained in this TechNote, a page could be built which inserts data into any number of different tables.
The instructions below assume both tables are in the same database. Extra instructions are provided at the end of this TechNote for using tables in different databases.
Please understand that since this method involves manipulation of UltraDev generated code, technical support is not available for troubleshooting. However, the methodology provided here has been tested and will work if applied correctly.
Also note the example here is for ASP-VBScript. The same concepts apply for pages using JavaScript, ColdFusion or JSP. These concepts can also be applied to Update and Delete pages.
Methodology
The approach is to build two separate insert record pages, and then integrate the code as explained below to allow the two insert behaviors to run from the same page.
Step 1
First, build the insert page you wish to use, with a form and all input objects for both inserts. Add an Insert Record server behavior for one of the two tables. At this point, do not assign the input objects for the second table to any Insert Record server behavior. Make sure to name all input objects (textfields, etc.) in a logical manner, this will be an important issue in the next step.
Step 2
Next, build a "dummy" page with the same input objects and an Insert Record server behavior for the second table. Make sure the input objects have the same name as on the page built in step one!
At this point, you should test both insert pages to insure they each work on their own before proceeding to the next step.
Step 3
The next step is to modify the code on the "dummy" page. You will then integrate the modified code into the real insert page.
Examine the "dummy" insert page's source code. Find the following line:
' *** Insert Record: set variables
Only some of the code below this line is used. To make the second insert work using UltraDev's code, you must differentiate between the variables used in the code blocks for each insert. Following the example below, change the name of variables starting withMM_ to make them unique. In this example, a 2 has been added to the end of the variable name.
Note that MM_Insert has not been changed, you don't need it. Also note that two lines have been commented out (theMM_editConnection and MM_editRedirectUrl lines), you don't need them either (as long as we're only using one database).
' *** Insert Record: set variables If (CStr(Request("MM_insert")) <> "") Then ' MM_editConnection = MM_connCompass_STRING MM_editTable2 = "MEMBERS" ' MM_editRedirectUrl = "insDummy.asp" MM_fieldsStr2 = "txtMembFName|value|txtMembLName|value|txtMembUName|value|txtMembPword|value" MM_columnsStr2 = "FIRSTNAME|',none,''|LASTNAME|',none,''|USERNAME|',none,''|PASSWORD|',none,''" ' create the MM_fields and MM_columns arrays MM_fields2 = Split(MM_fieldsStr2, "|") MM_columns2 = Split(MM_columnsStr2, "|") ' set the form values For i = LBound(MM_fields2) To UBound(MM_fields2) Step 2 MM_fields2(i+1) = CStr(Request.Form(MM_fields2(i))) Next
Step 4
Copy all the code from the MM_editTable2 line through the Next for the set the form values section. Remember, you don't need the If (CStr(Request("MM_insert")) <> "") Then line or the MM_editConnection line (as long as you're only using one database). Be sure to either comment out theMM_editRedirectUrl line as shown above (single apostrophe) or remove it.
Step 5
Go into the real insert page and paste the modified code from the "dummy" page into its source. It must be pasted within the same section it came from on the "dummy" page. You can paste it anywhere within the If/Then loop. For convenience, paste it right below theIf (CStr(Request("MM_insert")) <> "") Then line.
Step 6
Go back to the "dummy" page. Perform the same manipulation as above for the section starting with the following line.
' *** Insert Record: construct a sql insert statement and execute it
Do the same thing again, and change the name of all variables starting with MM_ to make them unique. Below is the applicable section of code, again the MM_ variable names have already been modified by adding a 2 to the end. Note the other variables (FormVal, Delim, etc.) do not need to be modified. Modifying them won't hurt, as long as you don't miss any!
' create the SQL insert statement MM_tableValues2 = "" MM_dbValues2 = "" For i = LBound(MM_fields2) To UBound(MM_fields2) Step 2 FormVal = MM_fields2(i+1) MM_typeArray2 = Split(MM_columns2(i+1),",") Delim = MM_typeArray2(0) If (Delim = "none") Then Delim = "" AltVal = MM_typeArray2(1) If (AltVal = "none") Then AltVal = "" EmptyVal = MM_typeArray2(2) If (EmptyVal = "none") Then EmptyVal = "" If (FormVal = "") Then FormVal = EmptyVal Else If (AltVal <> "") Then FormVal = AltVal ElseIf (Delim = "'") Then ' escape quotes FormVal = "'"& Replace(FormVal,"'","''") &"'" Else FormVal = Delim + FormVal + Delim End If End If If (i <> LBound(MM_fields2)) Then MM_tableValues2 = MM_tableValues2&"," MM_dbValues2 = MM_dbValues2&"," End if MM_tableValues2 = MM_tableValues2& MM_columns2(i) MM_dbValues2 = MM_dbValues2& FormVal Next MM_editQuery2 = "insert into "& MM_editTable2&" ("& MM_tableValues2&") values ("& MM_dbValues2&")"
Step 7
Again, copy the modified code from the "dummy" page and paste it into the real insert page. Make sure to paste it below these lines:
' *** Insert Record: construct a sql insert statement and execute it If (CStr(Request("MM_insert")) <> "") Then
And above these lines:
If (Not MM_abortEdit) Then ' execute the insert
Also, don't break up the existing code block (that looks the same as this one). Again, for convenience, paste it right below the If (CStr(Request("MM_insert")) <> "") Then line.
Step 8
Finally, modify the actual insert execution code. In the real insert page, find the following code block:
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 <> "") Then Response.Redirect(MM_editRedirectUrl) End If End If
Modify it as follows:
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' Code for second insert execution MM_editCmd.CommandText = MM_editQuery2 MM_editCmd.Execute ' End of code for second insert execution MM_editCmd.ActiveConnection.Close If (MM_editRedirectUrl <> "") Then Response.Redirect(MM_editRedirectUrl) End If End If
The following lines were inserted:
' Code for second insert execution MM_editCmd.CommandText = MM_editQuery2 MM_editCmd.Executeode for second insert execution ' End of code for second insert execution
This code must be inserted below the firstMM_editCmd.Execute line and above theMM_editCmd.ActiveConnection.Close line. It's the same lines as the original code, exceptMM_editQuery2, which has the 2 added on the end. This must match theMM_editQuery2 variable name at the bottom of the second modified code block.
If these instructions have been followed correctly, the page will now insert form data from the real page into two tables in the same database.
Inserting data into different databases
In order to insert data from one page into tables in different databases, all of the above instructions apply with a few exceptions.
 |
A reference to the second database's connection file must be inserted into the real insert page. Assuming a functioning "dummy" page was built, line 2 of the source code of the "dummy" page is as follows, referring to your connection file name instead ofconnCompass.asp: <!--#include --> Copy this line and paste it underneath the similar code on line 2 of the real insert page. Be sure not to paste it inside of any server side code tags (<% %>) |
| |
Follow the instructions in step 3, with the following exceptions. Do not comment out the MM_editConnection line. Make sure the MM_editConnection line refers to the correct connection. If it comes from a working "dummy" page, it is correct. It must be the same variable name defined in the connection file referred to above. Modify the MM_editConnection variable in the same manner as the other variables (in this example, change it toMM_editConnection2). Copy the MM_editConnection line along with the other code to the real insert page. |
| |
In step 8, instead of modifying the actual insert execution code in the real insert page, copy the section of the code block shown below and paste it below the original. Be sure to paste it below the original code block, andabove the
Once again, modify all the MM_ variables in the same manner as above. In this example a 2 was added to the end of the variable name. ' execute the insert Set MM_editCmd2 = Server.CreateObject("ADODB.Command") MM_editCmd2.ActiveConnection = MM_editConnection2 MM_editCmd2.CommandText = MM_editQuery2 MM_editCmd2.Execute MM_editCmd2.ActiveConnection.Close
|
Code Example
The source code below is from a page which uses the Compass Travel database supplied with the UltraDev 4 tutorial and inserts records into the Employee and Members tables.
This code can be copied and pasted into the Code View of a test page (overwrite all source code in the test page). Save the test page as ins2tbls.asp. It requires a DSN named CompassTravel which points to the Compass Travel database, and a connection in the site named connCompass. If you have completed the tutorial, you can save ins2tbls.asp in the tutorial site (remember this code is for the ASP server model) and you should already have the correct DSN and connection defined.
To find the code inserted from the "dummy" page, search forinsDummy.asp in the source code.
<%@LANGUAGE="VBSCRIPT"%><!--#include --><% ' *** Edit Operations: declare variables MM_editAction = CStr(Request("URL")) If (Request.QueryString <> "") Then MM_editAction = MM_editAction &"?"& Request.QueryString End If ' boolean to abort record edit MM_abortEdit = false ' query string to execute MM_editQuery = "" %><% ' *** Insert Record: set variables If (CStr(Request("MM_insert")) <> "") Then ' Set variables for second insert from insDummy.asp ' MM_editConnection = MM_connCompass_STRING MM_editTable2 = "MEMBERS" ' MM_editRedirectUrl = "insDummy.asp" MM_fieldsStr2 = "txtMembFName|value|txtMembLName|value|txtMembUName|value|txtMembPword|value" MM_columnsStr2 = "FIRSTNAME|',none,''|LASTNAME|',none,''|USERNAME|',none,''|PASSWORD|',none,''" ' create the MM_fields and MM_columns arrays MM_fields2 = Split(MM_fieldsStr2, "|") MM_columns2 = Split(MM_columnsStr2, "|") ' set the form values For i = LBound(MM_fields2) To UBound(MM_fields2) Step 2 MM_fields2(i+1) = CStr(Request.Form(MM_fields2(i))) Next ' End of code copied from insDummy.asp MM_editConnection = MM_connCompass_STRING MM_editTable = "EMPLOYEES" MM_editRedirectUrl = "ins2tbls.asp" MM_fieldsStr = "txtEmpFName|value|txtEmpLName|value|txtEmpSDate|value|txtEmpPhone|value" MM_columnsStr = "FIRSTNAME|',none,''|LASTNAME|',none,''|STARTDATE|#,none,NULL|PHONE|',none,''" ' create the MM_fields and MM_columns arrays MM_fields = Split(MM_fieldsStr, "|") MM_columns = Split(MM_columnsStr, "|") ' 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 <> "" And Request.QueryString <> "") Then If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then MM_editRedirectUrl = MM_editRedirectUrl &"?"& Request.QueryString Else MM_editRedirectUrl = MM_editRedirectUrl &"&"& Request.QueryString End If End If End If %><% ' *** Insert Record: construct a sql insert statement and execute it If (CStr(Request("MM_insert")) <> "") Then ' create the sql insert statement from insDummy.asp MM_tableValues2 = "" MM_dbValues2 = "" For i = LBound(MM_fields2) To UBound(MM_fields2) Step 2 FormVal = MM_fields2(i+1) MM_typeArray2 = Split(MM_columns2(i+1),",") Delim = MM_typeArray2(0) If (Delim = "none") Then Delim = "" AltVal = MM_typeArray2(1) If (AltVal = "none") Then AltVal = "" EmptyVal = MM_typeArray2(2) If (EmptyVal = "none") Then EmptyVal = "" If (FormVal = "") Then FormVal = EmptyVal Else If (AltVal <> "") Then FormVal = AltVal ElseIf (Delim = "'") Then ' escape quotes FormVal = "'"& Replace(FormVal,"'","''") &"'" Else FormVal = Delim + FormVal + Delim End If End If If (i <> LBound(MM_fields2)) Then MM_tableValues2 = MM_tableValues2 &"," MM_dbValues2 = MM_dbValues2 &"," End if MM_tableValues2 = MM_tableValues2 & MM_columns2(i) MM_dbValues2 = MM_dbValues2 & FormVal Next MM_editQuery2 = "insert into "& MM_editTable2 &" ("& MM_tableValues2 &") values ("& MM_dbValues2 &")" ' End of code copied from insDummy.asp ' create the sql insert statement MM_tableValues = "" MM_dbValues = "" For i = LBound(MM_fields) To UBound(MM_fields) Step 2 FormVal = MM_fields(i+1) MM_typeArray = Split(MM_columns(i+1),",") Delim = MM_typeArray(0) If (Delim = "none") Then Delim = "" AltVal = MM_typeArray(1) If (AltVal = "none") Then AltVal = "" EmptyVal = MM_typeArray(2) If (EmptyVal = "none") Then EmptyVal = "" If (FormVal = "") Then FormVal = EmptyVal Else If (AltVal <> "") Then FormVal = AltVal ElseIf (Delim = "'") Then ' escape quotes FormVal = "'"& Replace(FormVal,"'","''") &"'" Else FormVal = Delim + FormVal + Delim End If End If If (i <> LBound(MM_fields)) Then MM_tableValues = MM_tableValues &"," MM_dbValues = MM_dbValues &"," End if MM_tableValues = MM_tableValues & MM_columns(i) MM_dbValues = MM_dbValues & 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 ' Added execute code for second insert (insDummy.asp) MM_editCmd.CommandText = MM_editQuery2 MM_editCmd.Execute ' End of added code MM_editCmd.ActiveConnection.Close If (MM_editRedirectUrl <> "") Then Response.Redirect(MM_editRedirectUrl) End If End If End If %><html><head><title>Insert into 2 Tables!</title><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"></head><body text="#000000"><p ><b><font >Inserting Data into Two Tables from One Page using UltraDev</font></b></p><table ><tr><td><p>This example shows how form data can be inserted into two tables following the TechNote instructions. It uses the Compass Travel database (DSN=CompassTravel, connection name = connCompass), and inserts records into the Employees and Members tables.</p><p>The page was built as an insert page for the Employee data, and the Member data insert code was integrated from a "dummy" insert page.</p></td></tr></table><form name="form1" method="POST" action="<%=MM_editAction%>"><table ><tr><td ><div ><b>Employee Data</b></div></td></tr><tr><td >First Name</td><td ><div ><input type="text" name="txtEmpFName"></div></td></tr><tr><td >Last Name</td><td ><div ><input type="text" name="txtEmpLName"></div></td></tr><tr><td >Start Date</td><td ><div ><input type="text" name="txtEmpSDate"></div></td></tr><tr><td >Phone Number</td><td ><div ><input type="text" name="txtEmpPhone"></div></td></tr></table><br><table ><tr><td ><div ><b>Member Data</b></div></td></tr><tr><td >First Name</td><td ><div ><input type="text" name="txtMembFName"></div></td></tr><tr><td >Last Name</td><td ><div ><input type="text" name="txtMembLName"></div></td></tr><tr><td >Username</td><td ><div ><input type="text" name="txtMembUName"></div></td></tr><tr><td >Password</td><td ><div ><input type="text" name="txtMembPword"></div></td></tr></table><br><table ><tr><td><div ><input type="submit" name="Submit" value="Insert Data"></div></td></tr></table><input type="hidden" name="MM_insert" value="true"></form><p> </p></body></html>
This content requires Flash
To view this content, JavaScript must be enabled, and you need the latest version of the Adobe Flash Player.
Download the free Flash Player now!
