Primary key ID ... Foreign key iD ???

[2Goode]

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>

[Dan_Bracuk]

Set up a many to many relationship between patients and diagnoses.

[2Goode]

It's already set to a many to many relationship.

[Dan_Bracuk]

Oops, sorry. I got fooled by the two tables with very similar names.

After you insert your patient record, you want to get the id of the field you just inserted. I could tell you one way that's generic, but others who work with mssql a lot could tell you a better way.

[2Goode]

generic is fine with me. I just need something that works.

Thank you.

[Dan_Bracuk]

select max(id)
from yourtable
where as many conditions as you can think of are met

[2Goode]

Ok that didn't work. Most likely due to my lack of knowledge when it comes to this.
I've been doing some research on Stored Prcedures, and thought it might be the answer to my problems.

Here is the code I came up with.

First is the Stored Procedure (on the SQL Server):

CREATE PROCEDURE spInsertDiagnosis
@ID integer,
@DSMIV varchar(25)

AS

insert into tblDiagnosis (ID, DSMIV) values (@ID, @DSMIV)

GO

Second, is the CF on my process page

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

NOTE: The above CF code replaced the below on my process page (ref to first post).

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

PROBLEM now is, in doing this now nothing gets inserted into the Diagnosis table. Obviously I do not know how to correctly insert the stored procedure into my process page.

[philh]

Where is "ID" coming from? Shouldn't you scope that variable the same way that you scope "DSMIV"? What do you get when you output the variables on the process page?

I don't see anything blatantly wrong with your CFSTOREDPROC code.

Does the SP work when you call it in Query Analyzer?

[2Goode]

The "ID" is coming from or should I say being inserted into the "tblDiagnosis" table. It's the foreign key that is linked to the primary key ID of the tblPatients table.
When I change it to output, nothing happens.

When I run the SP in QA:
@ID integer,
@DSMIV varchar(25)

AS

insert into tblDiagnosis (ID, DSMIV) values (@ID, @DSMIV)

GO
I get "Must declare the variable '@ID'."

Thank you for replying