I have created a form that inserts data into two separate tables in a SQL database. I used the instructions about "Inserting data from one page into two tables" from the Macromedia website with no problems. However, my problem is my child table may have multiple records for each parent. What do I have to add to the code below to make this work?? (one to many)


<%
'*** Insert Record: set variables
If (CStr(Request("MM_insert") <> "" Then

MM_editConnection = MM_EAMR_STRING
MM_editTable = "dbo.tblEAMR_Form"
MM_editRedirectUrl = "thanks.asp"
MM_fieldsStr = "ef_LogNbr|value|ef_PreparedDate|value|program|value|ef_Deliverable|value|ef_AssyNbr|value|ef_NeedDate|value|ef_PreparedByFirstName|value|ef_PreparedByLastName|value|ef_PreparedExtension|value|firstname|value|lastname|value|extension|value|ef_CID|value|ef_LaborCID|value|ef_Comments|value|ef_ProjEngFirstName|value|ef_ProjEngLastName|value|ef_ProgMgrFirstName|value|ef_ProgMgrLastName|value|ef_CompEngFirstName|value|ef_CompEngLastName|value"
MM_columnsStr = "ef_LogNbr|',none,''|ef_PreparedDate|',none,''|ef_Program|',none,''|ef_Deliverable|',none,''|ef_AssyNbr|',none,''|ef_NeedDate|',none,''|ef_PreparedByFirstName|',none,''|ef_PreparedByLastName|',none,''|ef_PreparedExtension|',none,''|ef_PlannerFirstName|',none,''|ef_PlannerLastName|',none,''|ef_PlannerExtension|',none,''|ef_CID|',none,''|ef_LaborCID|',none,''|ef_Comments|',none,''|ef_ProjEngFirstName|',none,''|ef_ProjEngLastName|',none,''|ef_ProgMgrFirstName|',none,''|ef_ProgMgrLastName|',none,''|ef_CompEngFirstName|',none,''|ef_CompEngLastName|',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

MM_editTable2 = "dbo.tblEAMR_Detail"
MM_fieldsStr2 = "ed_LogNbr|value|ed_ItemNbr|value|ed_MRPPartNbr|value|ed_PartDesc|value|ed_VendorName|value|ed_QtyPerAssy|value|ed_UofM|value|ed_LeadTime|value|ed_UnitPrice|value|ed_Qty|value|ed_Total|value"
MM_columnsStr2 = "ed_LogNbr|',none,''|ed_ItemNbr|',none,''|ed_MRPPartNbr|',none,''|ed_PartDesc|',none,''|ed_VendorName|',none,''|ed_QtyPerAssy|none,none,NULL|ed_UofM|',none,''|ed_LeadTime|',none,''|ed_UnitPrice|none,none,NULL|ed_Qty|none,none,NULL|ed_Total|none,none,NULL"

' 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

' 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
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 & ""



' 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
'code for second insert execution
MM_editCmd.CommandText = MM_editQuery2
MM_editCmd.Execute
'end of code for second insert execution
MM_editCmd.ActiveConnection.Close



' send cdo email after MM insert
Set objCDO = Server.CreateObject("Cdonts.NewMail"
objCDO.BodyFormat =1 ' 1 is a plain text, 0 "zero" is a HTML
objCDO.MailFormat = 1 ' 1 is a plain text, 0 "zero" is mime format
objCDO.To = " "
objCDO.From = " "
objCDO.Subject = "New EAMR has been created"
objCDO.Body = "EAMR number "& request.form("ef_LogNbr" &" has been created."
objCDO.Send
Set objCDO = Nothing
' end

If (MM_editRedirectUrl <> "" Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If
%>

Personally I would forget trying to modify the messy DW Insert Record code and instead use Command (Stored Procedure) >> Insert.

You can then insert the first record to the first table and use a loop to insert as many records as required to the second table.

The following articlles may give you a few pointers:
www.drdev.net/article01.htm
www.drdev.net/article11.asp

Regards

Owen.

-------------------------------------------------------------------------------------------
Used programming books and web development software for sale (UK only): www.tdsf.co.uk/tdsfdemo/Shop.htm

Developer services and tutorials: www.drdev.net

Multiple Parameter UD4 / Access 2000 Search Tutorial: www.tdsf.co.uk/tdsfdemo/