Hi, 

I would really appreciate some help with this problem.

I'm having a lot of trouble updating my 'Jobseekers' tables from a form 

as 'Email' is the primary key in this table and a foreign key in CVtable. 

I've tried using a left join but i'm still getting an error



This is the error i'm getting:

"Microsoft OLE DB Provider for ODBC Drivers (0x80004005)

[Microsoft][ODBC Microsoft Access Driver] The record cannot be deleted or 

changed because table 'CVtable' includes related records."





This is my code: The variable names for the information being entered in 

the form are the same as the field names in the database



<%

'grab the form contents

Email = Request.Form("Email")

Street_Name = Request.Form("Street_Name")

Password = Request.Form("Password")

TownCity = Request.Form("TownCity")

County = Request.Form("County")

Phone = Request.Form("Phone")

Mobile_Number = Request.Form("Mobile_Number")







Set MyConn=Server.CreateObject("ADODB.Connection")

Set RS = Server.CreateObject("ADODB.Recordset")



MyConn.Open "testdatabase"



SQL = "UPDATE Jobseekers LEFT JOIN CVtable ON Jobseekers.Email = 

CVtable.Email SET  Jobseekers.Email = '" & request.form("Email")&"' , 

CVtable.Email = '" & request.form("Email")&"', Jobseekers.Street_Name='" & 

request.form("Street_Name")& "', Jobseekers.TownCity ='" & request.form

("TownCity") & "',Jobseekers.County = '" & request.form("County") 

& "',Jobseekers.Phone ='" & request.form("Phone") 

& "',Jobseekers.Mobile_Number = '" & request.form("Mobile_Number") & "', 

Jobseekers.Password ='" & request.form("Password") & "'"





Set RS = MyConn.Execute(SQL)



Response.Redirect "http://Student-iis/star_recruitment/detailsupdated.htm"

 

 RS.Close

 MyConn.Close

 Set RS = Nothing

 Set MyConn = Nothing



%>

I suggest you write two SQL queries - one to update the parent table, and

another to update the child table.



Since you are issuing an UPDATE query, no records are returned, so you don't

need a recordset object, instead just do:



<%

objConn.Execute strSQL,,adCmdText+adExecuteNoRecords

%>



Also, Response.Redirect() stops processing of the current page, so you

should clean up your ADO objects *before* you do the Response.Redirect()



Cheers

Ken



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

From: <laura.clancy@e...>

Subject: [access_asp] UPDATE query





: Hi,

: I would really appreciate some help with this problem.

: I'm having a lot of trouble updating my 'Jobseekers' tables from a form

: as 'Email' is the primary key in this table and a foreign key in CVtable.

: I've tried using a left join but i'm still getting an error

:

: This is the error i'm getting:

: "Microsoft OLE DB Provider for ODBC Drivers (0x80004005)

: [Microsoft][ODBC Microsoft Access Driver] The record cannot be deleted or

: changed because table 'CVtable' includes related records."

:

:

: This is my code: The variable names for the information being entered in

: the form are the same as the field names in the database

:

: <%

: 'grab the form contents

: Email = Request.Form("Email")

: Street_Name = Request.Form("Street_Name")

: Password = Request.Form("Password")

: TownCity = Request.Form("TownCity")

: County = Request.Form("County")

: Phone = Request.Form("Phone")

: Mobile_Number = Request.Form("Mobile_Number")

:

:

:

: Set MyConn=Server.CreateObject("ADODB.Connection")

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

:

: MyConn.Open "testdatabase"

:

: SQL = "UPDATE Jobseekers LEFT JOIN CVtable ON Jobseekers.Email 

: CVtable.Email SET  Jobseekers.Email = '" & request.form("Email")&"' ,

: CVtable.Email = '" & request.form("Email")&"', Jobseekers.Street_Name='" &

: request.form("Street_Name")& "', Jobseekers.TownCity ='" & request.form

: ("TownCity") & "',Jobseekers.County = '" & request.form("County")

: & "',Jobseekers.Phone ='" & request.form("Phone")

: & "',Jobseekers.Mobile_Number = '" & request.form("Mobile_Number") & "',

: Jobseekers.Password ='" & request.form("Password") & "'"

:

:

: Set RS = MyConn.Execute(SQL)

:

: Response.Redirect "http://Student-iis/star_recruitment/detailsupdated.htm"

:

:  RS.Close

:  MyConn.Close

:  Set RS = Nothing

:  Set MyConn = Nothing

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