0 Replies Latest reply on Dec 5, 2006 1:12 PM by 2Goode

    Using Stored Procedures

    2Goode
      Below is the process page code (if you need to look at the form page code let me know).
      I am wanting the form to insert data into two tables:
      - tblPatients (which is the main table). Primary key is ID (an auto number field).
      - tblDiagnosis (which only houses an ID field and code field called DSMIV). The ID in the tblDiagnosis table is a foreign key and is linked to the tblPatients table’s ID.
      Currently as the code is below, when the form is submited data is inserted into the Patient table but nothing is inserted into the Diagnosis Table. The way the form is set up I want to be able to input multiple selections per submission.
      I am using a Store procedure on the Process page when it comes to the Diagnosis data. ANYONE PLEASE HELP!




      PROCESS PAGE CODE:

      <cfif IsDefined ("ID") AND #ID# NEQ "">
      <cfif IsDefined("FORM.DSMIV") AND #FORM.DSMIV# NEQ "">
      <CFSTOREDPROC procedure="dbo.spInsertDiagnosis" datasource="dsnPsychiatry">
      <CFPROCPARAM type="IN" dbvarname="@ID" value="#ID#" cfsqltype="CF_SQL_INTEGER">
      <CFPROCPARAM type="IN" dbvarname="@DSMIV" value="#DSMIV#" cfsqltype="CF_SQL_VARCHAR">
      </CFSTOREDPROC>
      </cfif>
      </cfif>



      <cfquery name="rsLog" datasource="dsnPsychiatry">
      SELECT *
      FROM dbo.tblPatients
      </cfquery>
      <cfquery name="rsPatientsDiagnosis" datasource="dsnPsychiatry">
      SELECT *
      FROM dbo.tblDiagnosis
      </cfquery>
      <cfquery name="rsPatientMeds" datasource="dsnPsychiatry">
      SELECT *
      FROM dbo.tblMedications
      </cfquery>
      <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
      <html xmlns=" http://www.w3.org/1999/xhtml"><!-- InstanceBegin template="/Templates/MainTemp.dwt" codeOutsideHTMLIsLocked="false" -->
      <head>
      <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
      <link href="psych_layout.css" rel="stylesheet" type="text/css" />
      <!-- InstanceBeginEditable name="doctitle" -->
      <title>KUSM-W: Psychiatry and Behavioral Sciences</title>
      <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>