ColdFusion SQL server Form problem

[JLG2]

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.

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

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

[gigamike]

hi,

may we know what field is the list, any ways if you try inserting a record coming from a list with mulitple select, it should be in array, now let say you select 3 in the list field, how you gonna insert it to your SQL. 3 records ? or just 1 seprating them with comma (i.e. selected1, selected2, selected3) to fit in in one insert/record.

I hope this help,

mike


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.

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

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

[datropics]

What's the SQL error and where's the list?.

[JLG2]

I get this error message:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]String or binary data would be truncated.


Here is the CF cold for the list:


In the Header part

<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>
______
Body part
<select name="DSMIV" size="10" multiple="multiple">
<cfoutput query="rsMasterDiagnosis">
<option value="#rsMasterDiagnosis.DSMIV#">#rsMasterDiagnosis.DSMIV#</option>
</cfoutput>
</select>

THE DATA IN THE LIST IS COMING FROM A RECORDSET FROM A TABLE THAT HAVE ALL THE DIAGNOSIS IN IT.

[datropics]

hmm - check your field length in the db - may need to be increased OR ensure that the data that you're inserting is less than the length of the field in the db

[JLG2]

I checked and it is 10. But even when select two DSMIV codes that are ten or less I still get the error message.

[datropics]

dump the form <cfdump var="#form#"> and check the lengths of the field values - make sure their ok

it may be the previous query that's bumming-out

[JLG2]

Put "<cfdump var="#form#">" at the end of the form?

[datropics]

Right after
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">

Put <cfdump var="#form#">

What that will do is display in a table-like fashion the variables that in the Form

You can look at their values and see what is being passed to the DB

Are you on a development or production machine. If you're on a development machine you can turn debugging on and see the actual SQL statement that was sent to the DB - looking at the SQL statement you can check also to see what values are being sent

[JLG2]

This exception was on the report. It pasts to the db comma delimited.

Exceptions

11:32:45.045 - Database Exception - in C:\Inetpub\wwwroot\PsychDatabase\log2.cfm : line 114
Error Executing Database Query.

[JLG2]

line 114:
<cfqueryparam value="#FORM.DSMIV#" cfsqltype="cf_sql_clob" maxlength="50">

[datropics]

when you did a dump of FORM did you notice anything?

place the dump just before the cfquery tag

you may need to check your types as well

[JLG2]

My types?

The dump just showed me a bunch of stuff about the form
and the same Exception.

Exceptions

11:32:45.045 - Database Exception - in C:\Inetpub\wwwroot\PsychDatabase\log2.cfm : line 114
Error Executing Database Query.

[datropics]

as in your cfsqltype
take your form variable values and execute the SQL statement with them hardcoded - then check for your error - this should really help you narrow down the issue