CF Form problem. Using a list option set for multiple selections

[JLG2]

I need a form that has a list in it that allows multiple selections. The current form I have give me the below error message:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_tblDiagnosis_tlkpDiagnosis'. The conflict occurred in database 'Psychiatry', table 'tlkpDiagnosis', column 'DSMIV'.


Here is the form code:

<form method="POST" name="form1" action="thanks.cfm">
<table>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>New Continuous:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="NewContinuous" value="N" >
New Patient</td>
<td></tr>
<tr>
<td><input type="radio" name="NewContinuous" value="C" >
Continuous</td>
<td></tr>
</table> </tr>
<tr valign="baseline" bgcolor="#CCCCCC">
<td align="right" valign="middle" nowrap>Resident ID:</td>
<td><input name="ResidentID" type="text" value="" size="15" maxlength="10"></td>
</tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>Resident Initial:</td>
<td><input name="ResidentInitial" type="text" value="" size="1" maxlength="1"></td>
</tr>
<tr valign="baseline" bgcolor="#CCCCCC">
<td align="right" valign="middle" nowrap>Age:</td>
<td><input name="Age" type="text" value="" size="3" maxlength="3"></td>
</tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>Race:</td>
<td><input name="Race" type="text" value="" size="1" maxlength="1"></td>
</tr>
<tr valign="baseline" bgcolor="#CCCCCC">
<td align="right" valign="middle" nowrap>Sex:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="Sex" value="M" >
Male</td>
<td></tr>
<tr>
<td><input type="radio" name="Sex" value="F" >
Female</td>
<td></tr>
</table> </tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>Employment Status:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="EmploymentStatus" value="E" >
Employed</td>
<td></tr>
<tr>
<td><input type="radio" name="EmploymentStatus" value="Un" >
Unemployed</td>
<td></tr>
<tr>
<td><input type="radio" name="EmploymentStatus" value="Dis" >
Disabled</td>
<td></tr>
</table> </tr>
<tr valign="baseline" bgcolor="#CCCCCC">
<td align="right" valign="middle" nowrap>DSMIV<br />(Multiples selections allowed) </td>
<td valign="baseline"><select name="DSMIV" size="10" multiple="multiple">
<cfoutput query="rsMasterDiagnosis">
<option value="#rsMasterDiagnosis.DSMIV#">#rsMasterDiagnosis.DSMIV#</option>
</cfoutput>
</select>
</tr>
<tr valign="baseline" bgcolor="#CCCCCC">
<td align="right" valign="middle" nowrap>&nbsp;</td>
<td valign="baseline"></tr>
<tr valign="baseline" bgcolor="#CCCCCC">
<td align="right" valign="middle" nowrap>HP:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="HP" value="-1" >
Yes</td>
<td></tr>
<tr>
<td><input type="radio" name="HP" value="0" >
No</td>
<td></tr>
</table> </tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>Med Check:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="MedCheck" value="-1" >
Yes</td>
<td></tr>
<tr>
<td><input type="radio" name="MedCheck" value="0" >
No</td>
<td></tr>
</table> </tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>Therapy 30 Minute:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="Therapy30Minute" value="-1" >
Yes</td>
<td></tr>
<tr>
<td><input type="radio" name="Therapy30Minute" value="0" >
No</td>
<td></tr>
</table> </tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>Therapy 60 Minute:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="Therapy60Minute" value="-1" >
Yes</td>
<td></tr>
<tr>
<td><input type="radio" name="Therapy60Minute" value="0" >
No</td>
<td></tr>
</table> </tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>Therapy Group:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="TherapyGroup" value="-1" >
Yes</td>
<td></tr>
<tr>
<td><input type="radio" name="TherapyGroup" value="0" >
No</td>
<td></tr>
</table> </tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>ECT:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="ECT" value="-1" >
Yes</td>
<td></tr>
<tr>
<td><input type="radio" name="ECT" value="0" >
No</td>
<td></tr>
</table> </tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>Inpatient:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="Inpatient" value="-1" >
Yes</td>
<td></tr>
<tr>
<td><input type="radio" name="Inpatient" value="0" >
No</td>
<td></tr>
</table> </tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>Out patient:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="Outpatient" value="-1" >
Yes</td>
<td></tr>
<tr>
<td><input type="radio" name="Outpatient" value="0" >
No</td>
<td></tr>
</table> </tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>Emergency Room:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="EmergencyRoom" value="-1" >
Yes</td>
<td></tr>
<tr>
<td><input type="radio" name="EmergencyRoom" value="0" >
No</td>
<td></tr>
</table> </tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>&nbsp;</td>
<td><input name="Submit" type="submit" id="Submit" value="Submit Log">
<label>
<input name="Reset" type="reset" id="Reset" value="Reset" />
</label></td>
</tr>
</table>
<input type="hidden" name="MM_InsertRecord" value="form1">
</form>

Here is the process page code:

<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
<cfquery datasource="dsnPsychiatry">
INSERT INTO dbo.tblPatients (NewContinuous, ResidentID, ResidentInitial, Age, Race, Sex, EmploymentStatus, HP, MedCheck, Therapy30Minute, Therapy60Minute, TherapyGroup, ECT, Inpatient, Outpatient, EmergencyRoom)

VALUES (


<cfif IsDefined("FORM.NewContinuous") AND #FORM.NewContinuous# NEQ "">
<cfqueryparam value="#FORM.NewContinuous#" cfsqltype="cf_sql_clob" maxlength="1">
<cfelse>
''
</cfif>
,
<cfif IsDefined("FORM.ResidentID") AND #FORM.ResidentID# NEQ "">
<cfqueryparam value="#FORM.ResidentID#" cfsqltype="cf_sql_clob" maxlength="10">
<cfelse>
''
</cfif>
,
<cfif IsDefined("FORM.ResidentInitial") AND #FORM.ResidentInitial# NEQ "">
<cfqueryparam value="#FORM.ResidentInitial#" cfsqltype="cf_sql_clob" maxlength="1">
<cfelse>
''
</cfif>
,
<cfif IsDefined("FORM.Age") AND #FORM.Age# NEQ "">
<cfqueryparam value="#FORM.Age#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.Race") AND #FORM.Race# NEQ "">
<cfqueryparam value="#FORM.Race#" cfsqltype="cf_sql_clob" maxlength="1">
<cfelse>
''
</cfif>
,
<cfif IsDefined("FORM.Sex") AND #FORM.Sex# NEQ "">
<cfqueryparam value="#FORM.Sex#" cfsqltype="cf_sql_clob" maxlength="1">
<cfelse>
''
</cfif>
,
<cfif IsDefined("FORM.EmploymentStatus") AND #FORM.EmploymentStatus# NEQ "">
<cfqueryparam value="#FORM.EmploymentStatus#" cfsqltype="cf_sql_clob" maxlength="10">
<cfelse>
''
</cfif>
,
<cfif IsDefined("FORM.HP") AND #FORM.HP# NEQ "">
<cfqueryparam value="#FORM.HP#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.MedCheck") AND #FORM.MedCheck# NEQ "">
<cfqueryparam value="#FORM.MedCheck#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.Therapy30Minute") AND #FORM.Therapy30Minute# NEQ "">
<cfqueryparam value="#FORM.Therapy30Minute#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.Therapy60Minute") AND #FORM.Therapy60Minute# NEQ "">
<cfqueryparam value="#FORM.Therapy60Minute#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.TherapyGroup") AND #FORM.TherapyGroup# NEQ "">
<cfqueryparam value="#FORM.TherapyGroup#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.ECT") AND #FORM.ECT# NEQ "">
<cfqueryparam value="#FORM.ECT#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.Inpatient") AND #FORM.Inpatient# NEQ "">
<cfqueryparam value="#FORM.Inpatient#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.Outpatient") AND #FORM.Outpatient# NEQ "">
<cfqueryparam value="#FORM.Outpatient#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.EmergencyRoom") AND #FORM.EmergencyRoom# NEQ "">
<cfqueryparam value="#FORM.EmergencyRoom#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
)
</cfquery>
<cfloop list="#FORM.DSMIV#" index="diagnosis">
<cfif IsDefined("FORM.DSMIV") AND #FORM.DSMIV# NEQ "">
<cfquery datasource="dsnPsychiatry">
INSERT INTO dbo.tblDiagnosis (DSMIV)

VALUES (
<cfqueryparam value="#FORM.DSMIV#" cfsqltype="cf_sql_clob" maxlength="50">

)
</cfquery>
</cfif>
</cfloop>
<cflocation url="thanks.cfm">
</cfif>

[datropics]

Make the following change in your loop:

<cfloop list="#FORM.DSMIV#" index="diagnosis">
<cfif IsDefined("FORM.DSMIV") AND #FORM.DSMIV# NEQ "">
<cfquery datasource="dsnPsychiatry">
INSERT INTO dbo.tblDiagnosis (DSMIV)

VALUES (
<cfqueryparam value="#diagnosis#" cfsqltype="cf_sql_clob" maxlength="50">

)
</cfquery>
</cfif>
</cfloop>

[JLG2]

Thank you very much for your help.

I have one other problem that I hope is as easy to fix.

Let me start out by telling you basically how this data base is setup:
The tblPatients table holds the primary key (ID: auto number) that tblDiagnosis' table's foreign key (ID) is linked to.

I am able to input data into both tblPatients table and the tblDiagnosis table, BUT the ID (foreign key) field in the tblDiagnosis table is left empty thus left orphaned with no way to tight it back the data in the patient table. I know I must have let something out in my code.

[datropics]

hmm you may need to look at the most recent insert id and then take that id and include in your last insert statement- I believe sql server has a getID function or at least something to that effect - I'll do a little more research and let you know...