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>
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
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.
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
I checked and it is 10. But even when select two DSMIV codes that are ten or less I still get the error message.
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
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
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.
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
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.
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