Primary key... Foreign Key???

[JLG2]

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.

FORM PAGE CODE:

<form method="POST" name="form1" action="thanks.cfm">
<table>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>New Continuous:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="NewContinuous" value="N" >
New Patient</td>
<td></tr>
<tr>
<td><input type="radio" name="NewContinuous" value="C" >
Continuous</td>
<td></tr>
</table> </tr>
<tr valign="baseline" bgcolor="#CCCCCC">
<td align="right" valign="middle" nowrap>Resident ID:</td>
<td><input name="ResidentID" type="text" value="" size="15" maxlength="10"></td>
</tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>Resident Initial:</td>
<td><input name="ResidentInitial" type="text" value="" size="1" maxlength="1"></td>
</tr>
<tr valign="baseline" bgcolor="#CCCCCC">
<td align="right" valign="middle" nowrap>Age:</td>
<td><input name="Age" type="text" value="" size="3" maxlength="3"></td>
</tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>Race:</td>
<td><input name="Race" type="text" value="" size="1" maxlength="1"></td>
</tr>
<tr valign="baseline" bgcolor="#CCCCCC">
<td align="right" valign="middle" nowrap>Sex:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="Sex" value="M" >
Male</td>
<td></tr>
<tr>
<td><input type="radio" name="Sex" value="F" >
Female</td>
<td></tr>
</table> </tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>Employment Status:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="EmploymentStatus" value="E" >
Employed</td>
<td></tr>
<tr>
<td><input type="radio" name="EmploymentStatus" value="Un" >
Unemployed</td>
<td></tr>
<tr>
<td><input type="radio" name="EmploymentStatus" value="Dis" >
Disabled</td>
<td></tr>
</table> </tr>
<tr valign="baseline" bgcolor="#CCCCCC">
<td align="right" valign="middle" nowrap>DSMIV<br />(Multiples selections allowed) </td>
<td valign="baseline"><select name="DSMIV" size="10" multiple="multiple">
<cfoutput query="rsMasterDiagnosis">
<option value="#rsMasterDiagnosis.DSMIV#">#rsMasterDiagnosis.DSMIV#</option>
</cfoutput>
</select>
</tr>
<tr valign="baseline" bgcolor="#CCCCCC">
<td align="right" valign="middle" nowrap>&nbsp;</td>
<td valign="baseline"></tr>
<tr valign="baseline" bgcolor="#CCCCCC">
<td align="right" valign="middle" nowrap>HP:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="HP" value="-1" >
Yes</td>
<td></tr>
<tr>
<td><input type="radio" name="HP" value="0" >
No</td>
<td></tr>
</table> </tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>Med Check:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="MedCheck" value="-1" >
Yes</td>
<td></tr>
<tr>
<td><input type="radio" name="MedCheck" value="0" >
No</td>
<td></tr>
</table> </tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>Therapy 30 Minute:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="Therapy30Minute" value="-1" >
Yes</td>
<td></tr>
<tr>
<td><input type="radio" name="Therapy30Minute" value="0" >
No</td>
<td></tr>
</table> </tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>Therapy 60 Minute:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="Therapy60Minute" value="-1" >
Yes</td>
<td></tr>
<tr>
<td><input type="radio" name="Therapy60Minute" value="0" >
No</td>
<td></tr>
</table> </tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>Therapy Group:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="TherapyGroup" value="-1" >
Yes</td>
<td></tr>
<tr>
<td><input type="radio" name="TherapyGroup" value="0" >
No</td>
<td></tr>
</table> </tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>ECT:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="ECT" value="-1" >
Yes</td>
<td></tr>
<tr>
<td><input type="radio" name="ECT" value="0" >
No</td>
<td></tr>
</table> </tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>Inpatient:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="Inpatient" value="-1" >
Yes</td>
<td></tr>
<tr>
<td><input type="radio" name="Inpatient" value="0" >
No</td>
<td></tr>
</table> </tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>Out patient:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="Outpatient" value="-1" >
Yes</td>
<td></tr>
<tr>
<td><input type="radio" name="Outpatient" value="0" >
No</td>
<td></tr>
</table> </tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>Emergency Room:</td>
<td valign="baseline"><table>
<tr>
<td><input type="radio" name="EmergencyRoom" value="-1" >
Yes</td>
<td></tr>
<tr>
<td><input type="radio" name="EmergencyRoom" value="0" >
No</td>
<td></tr>
</table> </tr>
<tr valign="baseline">
<td align="right" valign="middle" nowrap>&nbsp;</td>
<td><input name="Submit" type="submit" id="Submit" value="Submit Log">
<label>
<input name="Reset" type="reset" id="Reset" value="Reset" />
</label></td>
</tr>
</table>
<input type="hidden" name="MM_InsertRecord" value="form1">
</form>


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>

[seamus.hogan]

Wrap all of the database writes in a <cftransaction> then in between writing to tblpatients and tblDiagnosis find out the last inserted id.

<cfif IsDefined("FORM.EmergencyRoom") AND #FORM.EmergencyRoom# NEQ "">
<cfqueryparam value="#FORM.EmergencyRoom#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
)

SELECT Scope_Identity () as newID
</cfquery>
<cfset newID = insertRecord.newID>

<cfloop list="#FORM.DSMIV#" index="diagnosis">
<cfif IsDefined("FORM.DSMIV") AND #FORM.DSMIV# NEQ "">

<cfquery datasource="dsnPsychiatry">
INSERT INTO dbo.tblDiagnosis (DSMIV, ID)

VALUES (
<cfqueryparam value="#diagnosis#" cfsqltype="cf_sql_clob" maxlength="50">
,
<cfqueryparam value="#newID#" cfsqltype="cf_sql_int" maxlength="10">

)
</cfquery>
</cfif>
</cfloop>

</cfif>

Hope this helps

Seamus

[JLG2]

Yes it does help. Thank you. But now I'm getting this error message:
"Element NEWID is undefined in INSERTRECORD"

[datropics]

Ok I'm guessing here but try the following:

1) Is INSERTRECORD an actual query?
2) NewID is a function of MSSQL - maybe something's happening there

[seamus.hogan]

Thanks datropics.

try changing the cfquery to <cfquery datasource="dsnPsychiatry" name="insertRecord">

I assumed you were using MSSQL if not let me know.

Hope this helps

Seamus

[JLG2]

Sorry for the late reply. Holidays and just had my third little girl.
Yes I am using MSSQL
I made the change and still get the same error, "Element NEWID is undefined in INSERTRECORD"

SELECT Scope_Identity () as newID
</cfquery>
<cfset newID = insertRecord.newID>
<cfloop list="#FORM.DSMIV#" index="diagnosis">
<cfif IsDefined("FORM.DSMIV") AND #FORM.DSMIV# NEQ "">

<cfquery datasource="dsnPsychiatry" name="insertRecord">
INSERT INTO dbo.tblDiagnosis (DSMIV, ID)

VALUES (
<cfqueryparam value="#diagnosis#" cfsqltype="cf_sql_clob" maxlength="50">
<cfqueryparam value="#newID#" cfsqltype="cf_sql_int" maxlength="10">

)

</cfquery>
</cfif>
</cfloop>
</cfif>

[datropics]

hmmm - you drive a hard bargin JLG2. Ok, can you do stored procedures?

1. Define your stored procedure, ensure that you have an OUTPUT variable
2. Update the value of your output variable
3. Call your stored procedure
4. Use the returned value of your stored procedure (OUTPUT variable)


1. Define
CREATE PROCEDURE [dbo].[proc_Process] @var1 var1dataType, @var2 var2dtaType, @returnVal uniqueidentifier OUTPUT AS
begin
you typically would have lines like the following for you T-SQL:
a) set @returnVal = newID()
b) insert into blah blah (recordID, blah, blah, blah)
c) values (@returnVal, blah, blah, blah)
end
go

2) @returnVal has it's value...

3 Call stored procedure
Now the thing to understand is that you need to send the variable values in the SAME order as you did in your stored procedure...

<cfstoredproc datasource="blahblah" username="blahblah" password = "blahblah" procedure="proc_Process">
<cfprocparam cfsqltype="blah" type="in" value="blahblah">
<cfprocparam cfsqltype="blah" type="in" value="blahblah">
<cfprocparam cfsqltype="uniqueidentifer" type="OUT" variable="NewID"> <!--- you may want to put an actual unique identifier here...not sure didn't test this --->

</cfstoredproc>

4. Use the returned value...
<cfloop list="#FORM.DSMIV#" index="diagnosis">
<cfif IsDefined("FORM.DSMIV") AND #FORM.DSMIV# NEQ "">
<cfquery datasource="dsnPsychiatry" name="insertRecord">
INSERT INTO dbo.tblDiagnosis (DSMIV, ID)
VALUES (
<cfqueryparam value="#diagnosis#" cfsqltype="cf_sql_clob" maxlength="50">
<cfqueryparam value="#newID#" cfsqltype="cf_sql_int" maxlength="10">

)

</cfquery>
</cfif>
</cfloop>

You may need to check microsoft's MSDN for further explanation of NewID and stored procedures.

I highly recommend using stored procedures (much much faster)

you'd be surprised of the kind of stuff you can do man!

Let me know how this works!