10 Replies Latest reply on Oct 31, 2006 1:51 PM by 2Goode

    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.
        • 1. Re: Input form problem
          dave.cozens Level 1
          What's the error message, and what's the text of the generated query that fails?
          • 2. Re: Input form problem
            2Goode Level 1
            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>
            • 3. Re: Input form problem
              dave.cozens Level 1
              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>
              • 4. Re: Input form problem
                2Goode Level 1
                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>
                • 5. Re: Input form problem
                  dave.cozens Level 1
                  OK. Now the data you're trying to enter is too long for the column.
                  • 6. Re: Input form problem
                    2Goode Level 1
                    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.
                    • 7. Re: Input form problem
                      dave.cozens Level 1
                      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?
                      • 8. Re: Input form problem
                        2Goode Level 1
                        The data is linked by an ID(auto). Is there anything else I can provide you?

                        • 9. Re: Input form problem
                          dave.cozens Level 1
                          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>
                          • 10. Re: Input form problem
                            2Goode Level 1

                            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.