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

[2Goode]

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">
.
.
.
<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>
.
.
.
</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>

[Guest]

quote:

---

INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_tblDiagnosis_tlkpDiagnosis'. The conflict occurred in database 'Psychiatry', table 'tlkpDiagnosis', column 'DSMIV'.

---

You have a foreign_key constraint on tblDiagnosis.DSMIV. That means you can't enter a value in the DSMIV field unless
there is a matching value in a record in the tlkpDiagnosis table.

So, in this code, you need to add something similar to the bold text:

<cfloop list="#FORM.DSMIV#" index="diagnosis">
<cfif IsDefined("FORM.DSMIV") AND #FORM.DSMIV# NEQ "">
<cfquery datasource="dsnPsychiatry">
insert into dbo.tlkpDiagnosis (MSMIV,other fields that are NOT NULL)
VALUES (<cfqueryparam value="#FORM.DSMIV#" cfsqltype="cf_sql_clob" maxlength="50">,other fields that are NOT NULL)
</cfquery>
<cfquery datasource="dsnPsychiatry">
INSERT INTO dbo.tblDiagnosis (DSMIV)
VALUES (<cfqueryparam value="#FORM.DSMIV#" cfsqltype="cf_sql_clob" maxlength="50">)
</cfquery>
</cfif>
</cfloop>

P.S.: This has nothing to do with multiple selections. It's a problem with referential integrity in the database. Search for "foreign key constraint" or "referential integrity" on Answers.com.

[2Goode]

Thank you for replying.
"Basically, A foreign key constraint specifies that the data in a foreign key must match the data in the primary key of the linked table." Yeah I know. But, this is how the database is setup: The tblPatients holds the primary key (ID: auto number) that tblDiagnosis' foreign key is linked to. The tlkDiagnosis table houses all possible Diagnosis. It is linked to the tblDiagnosis table by DSMIV. I have two problems I can select more the one diagnosis without getting an error. Also if I select one diagnosis the data is put in the tables (patients and tblDiagnosis), BUT the ID (foreign key) feild in the tblDiagnosis table is left empty thus left orphaned with no way to tight it back the data in the patient table.