Input form problem

[2Goode]

Ok I have created a input form, which is suppose to input data into two tables on on an SQL server. One field (a list) allows for multiple enters (the only field that goes to the 2nd table). The problem; I get an error message when I select more then one selection in the list field. The code is below. Thank you for any help.

[dave_cozens]

What's the error message, and what's the text of the generated query that fails?

[2Goode]

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'.

Please try the following:

* Enable Robust Exception Information to provide greater detail about the source of errors. In the Administrator, click Debugging & Logging > Debugging Settings, and select the Robust Exception Information option.
* Check the ColdFusion documentation to verify that you are using the correct syntax.
* Search the Knowledge Base to find a solution to your problem.

Browser Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.7) Gecko/20060909 Firefox/1.5.0.7
Remote Address 127.0.0.1
Referrer http://localhost/psychdatabase/log.cfm
Date/Time 30-Oct-06 01:46 PM
_______
<cfquery datasource="dsnPsychiatry">
INSERT INTO dbo.tblDiagnosis (DSMIV)
VALUES (
<cfif IsDefined("FORM.DSMIV") AND #FORM.DSMIV# NEQ "">
<cfqueryparam value="#FORM.DSMIV#" cfsqltype="cf_sql_clob" maxlength="10">
<cfelse>
''
</cfif>
)
</cfquery>

[dave_cozens]

OK, you've got a foreign key constraint error. Do you have admin access to the database? If so, you need to view the table properties and get the details of the foreign key.

What's the structure of the tblDiagnosis table?

It may just be that you're trying to insert no data (see the CFELSE)

Try this:-
<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="10">
)
</cfquery>
</cfif>

[2Goode]

I tried your code and I get this message now.

" Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]String or binary data would be truncated."

The structure of the table is "ID" (foreign key) and "DSMIV"

This is the code now with your addition.

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

<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>
<cflocation url="thanks.cfm">
</cfif>
<cfquery name="rsLog" datasource="dsnPsychiatry">
SELECT NewContinuous, ResidentID, ResidentInitial, Age, Race, Sex, EmploymentStatus, HP, MedCheck, Therapy30Minute, Therapy60Minute, TherapyGroup, ECT, Inpatient, Outpatient, EmergencyRoom
FROM dbo.tblPatients
</cfquery>
<cfquery name="rsPatientsDiagnosis" datasource="dsnPsychiatry">
SELECT *
FROM dbo.tblDiagnosis
</cfquery>
<cfquery name="rsPatientMeds" datasource="dsnPsychiatry">
SELECT *
FROM dbo.tblMedications
</cfquery>
<cfquery name="rsMasterDiagnosis" datasource="dsnPsychiatry">
SELECT *
FROM dbo.tlkpDiagnosis
ORDER BY DSMIV ASC
</cfquery>
<cfquery name="rsMasterMeds" datasource="dsnPsychiatry">
SELECT GenericName, TradeName
FROM dbo.tlkpMedications
ORDER BY TradeName ASC
</cfquery>

[dave_cozens]

OK. Now the data you're trying to enter is too long for the column.

[2Goode]

Ok, but the data I'm trying to enter are Diagnosis for patients (from a list field) and a patient can have more then one diagnosis. if I enter more then one I get an error message of some sort. I thinking there has to be some loop code used. I just don't know where and how.

[dave_cozens]

I'm not sure. You also don't appear to be inserting any data that is relational. How is this data linked to the patient record?

[2Goode]

The data is linked by an ID(auto). Is there anything else I can provide you?

[dave_cozens]

But I don't see where the ID is in dbo.tblDiagnosis, since you're only inserting DSMIV?

As far as looping over diagnoses, assuming the delimiter is a comma: -

<cfloop list="#FORM.DSMIV#" index="diagnosis">
<!--- insert query here--->
</cfloop>

[2Goode]

The ID is the FK from the patient table. If that makes sense?
Below is how I did the 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="#FORM.DSMIV#" cfsqltype="cf_sql_clob" maxlength="50">

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

BUT I get this below error message when I submit two from the list.

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]String or binary data would be truncated.