Add a record to the parent table first.

Get the PK value of this new record.

Then add a record to the child table.



strSQL = "INSERT INTO ParentTable(...) VALUES (...)"

strSQL = "SELECT @@Identity"

strSQL = "INSERT INTO ChildTable(...) VALUES(...)"



Always leave referential integrity on...



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Silas Reyes" <sciseven@h...>

Subject: [access_asp] Trouble adding record, please help





: Below is the ERROR, I get:

: *************************************************

: Microsoft JET Database Engine error '80040e21'

:

: You cannot add or change a record because a related record is required in

: table 'tblSchoolDetail'.

:

: /cps/addnewProc.asp, line 120

: *************************************************

:

: NOTES:

: I get this error when I check Referential Integrity on the table

: relationships.

:

: If I uncheck Referential Integrity, I am able to insert the record but

: when I look at the tables in Access, the joined fields are 0. For example,

: tblSchoolDetail.school_detail_id = 1 but in

: tblSchoolInfo.school_detail_id, it is 0 when it should be 1 as in the

: tblSchoolDetail table.

:

: ***************CODE*****************************

:

: <%

: Option Explicit

: Dim strConnect

: %>

: <!-- #include file="strConnect.asp" -->

: <!-- #include file="adovbs.inc" -->

:

: <%

: Dim strSQL          'SQL command string to execute

: Dim objRs 'The ADODB recordset object

:

: strSQL = "SELECT tblSchoolDetail.school_detail_id,

: tblSchoolInfo.school_info_id

: FROM tblSchoolDetail INNER JOIN tblSchoolInfo ON

: tblSchoolDetail.school_detail_id = tblSchoolInfo.school_detail_id;"

:

: Set objRs = Server.CreateObject("ADODB.Recordset")

: objRs.Open strSQL, strConnect, adOpenStatic, adLockOptimistic, adCmdText

:

: objRs.AddNew 'add a new record

:

: 'Records to add from form

: objRs("school_info_id") = Request.Form("SchoolInfoID")

: objRs("school_detail_id") = Request.Form("SchoolDetailID")

:

:

: objRs.Update

:

: objRs.Close

: Set objRs = Nothing

: objConn.Close

: Set objConn = Nothing

: %>



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Hi Ken,



Can you please elaborate on selecting the primary key from the new parent 

table record?

How am I going to use it to insert values to the child table?





> Add a record to the parent table first.

> Get the PK value of this new record.

> Then add a record to the child table.

> 

> strSQL = "INSERT INTO ParentTable(...) VALUES (...)"

> strSQL = "SELECT @@Identity"

> strSQL = "INSERT INTO ChildTable(...) VALUES(...)"

> 

> Always leave referential integrity on...

> 

> Cheers

> Ken

> 

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> From: "Silas Reyes" <sciseven@h...>

> Subject: [access_asp] Trouble adding record, please help

> 

> 

> : Below is the ERROR, I get:

> : *************************************************

> : Microsoft JET Database Engine error '80040e21'

> :

> : You cannot add or change a record because a related record is required 

in

> : table 'tblSchoolDetail'.

> :

> : /cps/addnewProc.asp, line 120

> : *************************************************

> :

> : NOTES:

> : I get this error when I check Referential Integrity on the table

> : relationships.

> :

> : If I uncheck Referential Integrity, I am able to insert the record but

> : when I look at the tables in Access, the joined fields are 0. For 

example,

> : tblSchoolDetail.school_detail_id = 1 but in

> : tblSchoolInfo.school_detail_id, it is 0 when it should be 1 as in the

> : tblSchoolDetail table.

> :

> : ***************CODE*****************************

> :

> : <%

> : Option Explicit

> : Dim strConnect

> : %>

> : <!-- #include file="strConnect.asp" -->

> : <!-- #include file="adovbs.inc" -->

> :

> : <%

> : Dim strSQL          'SQL command string to execute

> : Dim objRs 'The ADODB recordset object

> :

> : strSQL = "SELECT tblSchoolDetail.school_detail_id,

> : tblSchoolInfo.school_info_id

> : FROM tblSchoolDetail INNER JOIN tblSchoolInfo ON

> : tblSchoolDetail.school_detail_id = tblSchoolInfo.school_detail_id;"

> :

> : Set objRs = Server.CreateObject("ADODB.Recordset")

> : objRs.Open strSQL, strConnect, adOpenStatic, adLockOptimistic, 

adCmdText

> :

> : objRs.AddNew 'add a new record

> :

> : 'Records to add from form

> : objRs("school_info_id") = Request.Form("SchoolInfoID")

> : objRs("school_detail_id") = Request.Form("SchoolDetailID")

> :

> :

> : objRs.Update

> :

> : objRs.Close

> : Set objRs = Nothing

> : objConn.Close

> : Set objConn = Nothing

> : %>

> 

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Silas Reyes" <sciseven@h...>

Subject: [access_asp] Re: Trouble adding record, please help





: Can you please elaborate on selecting the primary key

: from the new parent table record?

: How am I going to use it to insert values to the child table?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



I have code to get an autonumber (if that's what you are using as a primary

key) here:

www.adopenstatic.com/experiments/fastestautonumber.asp



Basically you write an INSERT INTO SQL statement to insert the record into

the parent table. Then you execute a SELECT @@Identity statement to get the

value of the autonumber. Then you use this as part of your second INSERT

INTO statement.



<%

strSQL = "SELECT @@Identity"

Set objRS = objConn.Execute strSQL,,adCmdText



intNewID = objRS(0)



strSQL = "INSERT INTO table2 (ForeignKey) VALUES (" & intNewID & ")"

objConn.Execute strSQL,,adCmdText+adExecuteNoRecords

%>



Cheers

Ken