Adding Multiple Records to a Child Table

[wendyowens]

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??


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

MM_editConnection = MM_EAMR_STRING
MM_editTable = &quot;dbo.tblEAMR_Form&quot;
MM_editRedirectUrl = &quot;thanks.asp&quot;
MM_fieldsStr = &quot;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&quot;
MM_columnsStr = &quot;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,''&quot;

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, &quot;|&quot
MM_columns = Split(MM_columnsStr, &quot;|&quot

' 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 = &quot;dbo.tblEAMR_Detail&quot;
MM_fieldsStr2 = &quot;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&quot;
MM_columnsStr2 = &quot;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&quot;

' create the MM_fields and MM_columns arrays
MM_fields2 = Split(MM_fieldsStr2, &quot;|&quot
MM_columns2 = Split(MM_columnsStr2, &quot;|&quot

' 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 <> &quot;&quot; And Request.QueryString <> &quot;&quot Then
If (InStr(1, MM_editRedirectUrl, &quot;?&quot;, vbTextCompare) = 0 And Request.QueryString <> &quot;&quot Then
MM_editRedirectUrl = MM_editRedirectUrl & &quot;?&quot; & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & &quot;&&quot; & Request.QueryString
End If
End If

End If



' *** Insert Record: construct a sql insert statement and execute it
If (CStr(Request(&quot;MM_insert&quot) <> &quot;&quot Then

' create the sql insert statement
MM_tableValues2 = &quot;&quot;
MM_dbValues2 = &quot;&quot;
For i = LBound(MM_fields2) To UBound(MM_fields2) Step 2
FormVal = MM_fields2(i+1)
MM_typeArray2 = Split(MM_columns2(i+1),&quot;,&quot
Delim = MM_typeArray2(0)
If (Delim = &quot;none&quot Then Delim = &quot;&quot;
AltVal = MM_typeArray2(1)
If (AltVal = &quot;none&quot Then AltVal = &quot;&quot;
EmptyVal = MM_typeArray2(2)
If (EmptyVal = &quot;none&quot Then EmptyVal = &quot;&quot;
If (FormVal = &quot;&quot Then
FormVal = EmptyVal
Else
If (AltVal <> &quot;&quot Then
FormVal = AltVal
ElseIf (Delim = &quot;'&quot Then ' escape quotes
FormVal = &quot;'&quot; & Replace(FormVal,&quot;'&quot;,&quot;''&quot & &quot;'&quot;
Else
FormVal = Delim + FormVal + Delim
End If
End If
If (i <> LBound(MM_fields2)) Then
MM_tableValues2 = MM_tableValues2 & &quot;,&quot;
MM_dbValues2 = MM_dbValues2 & &quot;,&quot;
End if
MM_tableValues2 = MM_tableValues2 & MM_columns2(i)
MM_dbValues2 = MM_dbValues2 & FormVal
Next
MM_editQuery2 = &quot;insert into &quot; & MM_editTable2 & &quot; (&quot; & MM_tableValues2 & &quot values (&quot; & MM_dbValues2 & &quot&quot;



' create the sql insert statement
MM_tableValues = &quot;&quot;
MM_dbValues = &quot;&quot;
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
FormVal = MM_fields(i+1)
MM_typeArray = Split(MM_columns(i+1),&quot;,&quot
Delim = MM_typeArray(0)
If (Delim = &quot;none&quot Then Delim = &quot;&quot;
AltVal = MM_typeArray(1)
If (AltVal = &quot;none&quot Then AltVal = &quot;&quot;
EmptyVal = MM_typeArray(2)
If (EmptyVal = &quot;none&quot Then EmptyVal = &quot;&quot;
If (FormVal = &quot;&quot Then
FormVal = EmptyVal
Else
If (AltVal <> &quot;&quot Then
FormVal = AltVal
ElseIf (Delim = &quot;'&quot Then ' escape quotes
FormVal = &quot;'&quot; & Replace(FormVal,&quot;'&quot;,&quot;''&quot & &quot;'&quot;
Else
FormVal = Delim + FormVal + Delim
End If
End If
If (i <> LBound(MM_fields)) Then
MM_tableValues = MM_tableValues & &quot;,&quot;
MM_dbValues = MM_dbValues & &quot;,&quot;
End if
MM_tableValues = MM_tableValues & MM_columns(i)
MM_dbValues = MM_dbValues & FormVal
Next
MM_editQuery = &quot;insert into &quot; & MM_editTable & &quot; (&quot; & MM_tableValues & &quot values (&quot; & MM_dbValues & &quot&quot;



If (Not MM_abortEdit) Then
' execute the insert
Set MM_editCmd = Server.CreateObject(&quot;ADODB.Command&quot
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(&quot;Cdonts.NewMail&quot
objCDO.BodyFormat =1 ' 1 is a plain text, 0 &quot;zero&quot; is a HTML
objCDO.MailFormat = 1 ' 1 is a plain text, 0 &quot;zero&quot; is mime format
objCDO.To = &quot;wendy_l_owens@raytheon.com&quot;
objCDO.From = &quot;wendy_l_owens@raytheon.com&quot;
objCDO.Subject = &quot;New EAMR has been created&quot;
objCDO.Body = &quot;EAMR number &quot;& request.form(&quot;ef_LogNbr&quot &&quot; has been created.&quot;
objCDO.Send
Set objCDO = Nothing
' end

If (MM_editRedirectUrl <> &quot;&quot Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If
%>

 

[Microbe]

Are you saying you don't want multiple records in teh child table?

Elaborate on the problem please.

Steve Davis
hey.you@hahaha.com.au

http://www.hahaha.com.au

Me? I can't even spell ASP!

 

[wendyowens]

I DO want the ability to add multiple records in the child table for each parent.

This is my problem. If I just have one line item to enter into the child table for a parent, it inserts the data into both tables with no problem.

My problem is when I have more than one line item for the child table for a parent (a one to many scenario). The data for the parent table will enter into the parent table but the child data does not and I get the following error:

Microsoft OLE DB Provider for SQL Server error '80040e14'

There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

Any advise?