Prev: Null Values in COM Collections
Next: Problem with date comparsion in QoQ
From: 2Goode on 30 Oct 2006 14:44
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.

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

<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
<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>

</cfif>
</cfquery>
<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>

From: dave.cozens on 30 Oct 2006 14:44
What's the error message, and what's the text of the generated query that fails?
From: 2Goode on 30 Oct 2006 14:50
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>

From: dave.cozens on 30 Oct 2006 15:20
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>


From: 2Goode on 30 Oct 2006 16:02
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>

 |  Next  |  Last
Pages: 1 2 3
Prev: Null Values in COM Collections
Next: Problem with date comparsion in QoQ