insert and update data in two different tables

Hi there!
I am trying to insert and update data in two different tables that have a common field that is primary key in one table and foreign key in the other table. I understand that I should use a data relation. Am I right or is there another way of doing this?

Thanks

What is the backend database? MS SQL? Access?

Can you give a little clue about the two table names and what the relationship is and the columns you are updating?

Quote:

---

Originally Posted by szlamany

What is the backend database? MS SQL? Access?

Can you give a little clue about the two table names and what the relationship is and the columns you are updating?

---

Yes, thanks for your answer.
The two tables are related using an autonumeric field in one of the tables. The other table stores this numer also.This is the field that relates the two tables. The information that I store in this tables are things like: Student ID(autonumeric),Name, address, school level, telephone. In the other table I store another autonumeric for that specific table, plus an image(picture), and name of the parents.
I hope this is what you need in order to help me. If you need more I will try to send you the code I am working on.

Thanks

What is the database - ACCESS or MS SQL or MySQL - I really need to know that also - thanks.

Quote:

---

Originally Posted by szlamany

What is the database - ACCESS or MS SQL or MySQL - I really need to know that also - thanks.

---

It is a Microsoft SQL Server 2000 database.

Well I've always wanted to try this - seeing if a VIEW can be updated.

So I did this in QUERY ANALYZER - but it doesn't work.

Code:

---

--Drop Table Test1 Drop Table Test2
Create Table Test1 (keycol int, datacol1 varchar(1))
Insert into Test1 values (1,'A')
Create Table Test2 (keycol int, datacol2 varchar(1))
Insert into Test2 values (1,'Z')
Select * from Test1
Select * from Test2
Go
Create View View1 as Select T1.KeyCol,T1.DataCol1,T2.DataCol2 From Test1 T1 Left Join Test2 T2 on T2.KeyCol=T1.KeyCol
Go
Select * from View1
Update View1 Set DataCol1='B',DataCol2='B' Where KeyCol=1
Select * from View1

---

Gets this error:

View or function 'View1' is not updatable because the modification affects multiple base tables.

That's a shame.

Since you are using MS SQL Server - have you considered using a STORED PROCEDURE to do the update - so you can encapsulate both table UPDATES in a single TRANSACTION?

Quote:

---

Originally Posted by szlamany

Well I've always wanted to try this - seeing if a VIEW can be updated.

So I did this in QUERY ANALYZER - but it doesn't work.

Code:

---

--Drop Table Test1 Drop Table Test2
Create Table Test1 (keycol int, datacol1 varchar(1))
Insert into Test1 values (1,'A')
Create Table Test2 (keycol int, datacol2 varchar(1))
Insert into Test2 values (1,'Z')
Select * from Test1
Select * from Test2
Go
Create View View1 as Select T1.KeyCol,T1.DataCol1,T2.DataCol2 From Test1 T1 Left Join Test2 T2 on T2.KeyCol=T1.KeyCol
Go
Select * from View1
Update View1 Set DataCol1='B',DataCol2='B' Where KeyCol=1
Select * from View1

---

Gets this error:

View or function 'View1' is not updatable because the modification affects multiple base tables.

That's a shame.

Since you are using MS SQL Server - have you considered using a STORED PROCEDURE to do the update - so you can encapsulate both table UPDATES in a single TRANSACTION?

---

Yes I used a stored procedure but it produces the same error.

Post the code for the SPROC - I should be able to correct it for you...