**Inserting w/a Twist**

Hey guys, what's going on? I just signed up today so please forgive me if I sound redundant or am missing information. I have a slight problem that hopefully you guys can answer.

Background Info
I have three tables: Material, Units, and UnitMaterials. In my Material table, I have a MaterialId field which is an identity (automatically generated) and in my Unit table, I have a UnitId which is also an identity. The UnitMaterials table is a table where it links these two identities together i.e. putting that material in one or multiple units. Both of these fields must exist in order for this table to work.

Problem
I have a form where the user enters in the material information they want put into the database. That works fine. In the form though, there is no field asking for the MaterialId because it gets automatically generated. Once the user enters in this information, they are prompted with a select box where they can select mulitple units and therefore put that material in those units. My problem is when the user picks these units, the UnitMaterials table is not updated. The material gets added but it does not get placed into a unit.

Here Is Some Of My Code Which Hopefully You Guys Can Find The Problem With

<<<The Form Page For Inserting The Material>>>

<form action="newMSDS_action.cfm" method="post" name="materials" onsubmit='return checkForm();'>
<table border="0" cellpadding="2" cellspacing="2" bgcolor="">
<tr valign="baseline" bgcolor="#FFFFFF">
</tr>
<tr valign="baseline">
<td nowrap align="right"><div align="left" class="style7">* Material:</div></td>
<td colspan="3"><input name="material" type="text" value="" size="50"></td>
</tr>
<tr valign="baseline">
<td nowrap align="right"><div align="left" class="style7 style17">* Manufacturer:</div></td>
<td colspan="3"><input name="mfg" type="text" value="" size="50"></td>
</tr>
<tr valign="baseline">
<td nowrap align="right"><div align="left" class="style7 style17">* Issue Date:</div></td>
<td width="73%"><input name="issueDate" type="text" id="issue date" value="" size="50"></td>
</tr>
<tr valign="baseline">
<td nowrap align="right"><div align="left" class="style7 style17">Shelf Life:</div></td>
<td colspan="3"><input name="shelfLife" type="text" value="" size="50"></td>
</tr>
<INPUT type="hidden" name="materialId" value="<cfoutput>#material.MaterialId#</cfoutput> ">
<td width="27%"><input name="submit" type="submit" value="Insert MSDS"></td>
<td width="73%"><input name="Cancel" type="button" onClick="javascript:document.location='newMSDS.cfm'" value="Cancel"/></td>
</tr>
</table>
</form>

<<<The Action Page For Inserting The Material>>>

<CFQUERY datasource="****" NAME="InsertMaterials">
INSERT INTO dbo.Material (
Material,
Mfg,
IssueDate,
ShelfLife
)
VALUES (
'#Form.Material#',
'#Form.Mfg#',
'#DateFormat(Form.IssueDate,"mm/dd/yyyy")#',
'#Form.ShelfLife#'
)
</CFQUERY>

<cflocation url="newMSDS.cfm?material=#Form.material#">

<<<The Form That Prompts The User With A Select Box Of Units Once The Material Form Is Complete>>>

<cfform action="unitPlacement_action.cfm" method="post" name="unitPlacement">
<table border="0" cellpadding="2" cellspacing="2" bgcolor="">
<tr valign="baseline" bgcolor="#FFFFFF">
</tr>
<tr valign="baseline">
<td nowrap align="right"><div align="left" class="style7"><strong>Units:</strong></div></td>
<td colspan="3"><cfselect name="UnitId"
query="unit"
value="UnitId"
display="Unit"
required="yes"
multiple="yes"
size="4"
>
</cfselect>
</td>
</tr>
<td width="27%"></td>
<td width="27%"><input name="select" type="submit" value="Select Units"></td>
<INPUT type="hidden" name="materialId" value="<cfoutput>#material.MaterialId#</cfoutput>">
</table>
</cfform>

<<<The Action Page For Once The User Selects His Units>>>

<cfif not isDefined("Form.unitPlacement")>
<cflocation url="newMSDS.cfm">
</cfif>

<cfquery name="unitMaterials" datasource="****">
SELECT *
FROM dbo.UnitMaterials
WHERE MaterialId='#Form.materialId#' AND UnitId='#Form.UnitId#'

INSERT INTO dbo.UnitMaterials (
UnitId,
MaterialId
)
VALUES (
'#Form.UnitId#',
'#Form.MaterialId#'
)
</cfquery>
<cflocation url="newMSDS.cfm?unit=#Form.UnitId#">

I know I need a lot of code help so please, ANY HELP WOULD BE GREATLY APPRECIATED!!! Thanks so much guys!

~ Jazz

Can you run these queries form the command line (or your SQL IDE)?

Are you getting an error? If so, what is it?

It looks like your query "unitMaterials" has two problems to me. The first is that if the user picks more than one item from the select box, you'll need to loop over that query and do an insert for each item they picked. And second, where are you getting the "material.MaterialId" and/or "unitID" variables?

On my new material form page, I have created two queries as follows:

<cfquery name="unit" datasource="****">
SELECT *
FROM dbo.Unit
ORDER BY Unit.Unit
</cfquery>

<cfquery name="material" datasource="****">
SELECT *
FROM dbo.Material
</cfquery>

Using these queries and the form, is where I get my "unitID" and "material.MaterialId" variables. This is the way I thought I can get my MaterialId that was just created and pass it to the action form to add it to the units via unitId. Im sure it is probably wrong or a better way to do it...Im just so lost! You mentioned using a loop over the query...I know you mean to use a <cfloop> but am not sure how to implement it. I also dont know what you mean when you say "Can you run these queries form the command line (or your SQL IDE)?" When the units are selected for the material and clicked to submit...all that happens is that it goes back to the add a material page...so it basically does not do anything. I hope this information helps and that we can work to find the solution! Thanks for the prompt response.