Here is the setup. I have a database on a SQL server and have
created a form to input data into this database (with Dreamweaver
and CF).
Let me start out by telling you basically how this data base
is setup:
3 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.
-tlkpDiagnosis just house all the possible diagnosis.
I am able to input data into both tblPatients table and the
tblDiagnosis table, BUT the ID (foreign key) field in the
tblDiagnosis table is left empty thus left orphaned with no way to
tight it back to the data in the patient table. My code is below.
Thank you for any help you can provide me.
PROCESS PAGE 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>
)
</cfquery>
<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="#diagnosis#" cfsqltype="cf_sql_clob"
maxlength="50">
)
</cfquery>
</cfif>
</cfloop>
</cfif>