G
Guest
Hi,
I have a problem updating data in two tables which are related to each other.
The parent table is called "Owners" and each owner can have multiple
animals, standing in a table "Animals". "Owners" has a column "ID", which is
it's primary key. In "Animals" there is a column "owner" wich is the foreign
key.
Now I want to add some owners and animals to the database and update the
tables with those new values, but this doesn't seem to work, because I am
doing something wrong, but I cannot figure out my mistake. Here's a bit of
code:
MyDataSet.OwnersRow newOwner = MyDataSet.Owners.NewOwnersRow();
MyDataSet.AnimalsRow newAnimal = MyDataSet.Animals.NewAnimalsRow();
// filling the columns with data and add a new Row- works fine
newOwner.fname = customer.firstname;
newOwner.lname = customer.name;
newOwner.phone_home = customer.phone;
MyDataSet.Owners.Rows.Add(newOwner);
// now I want to add an animal to the customer
newAnimal.name = animal.name;
newAnimal.age = animal.age;
MyDataSetAnimals.Rows.Add(newAnimal);
// Getting the updated values and write them to DB
MyDataSet.OwnersDataTable rowToadd =
(MyDataSet.OwnersDataTable)MyDataSet.Owners.GetChanges(DataRowState.Added);
MyDataSet.AnimalsDataTable rowToadd1 =
(MyDataSet.AnimalsDataTable)MyDataSet.Animals.GetChanges(DataRowState.Added);
OwnersTableAdapter.Update(rowToadd);
AnimalsTableAdapter.Update(rowToadd1);
This doesn't work. I always get an exception, where it says the "owner"
column in the animals data table must not be DBNull.
If I include a DataRelation object in my code, connecting the columns
explicitly in the manner as described above, a different exception is thrown,
where it says a DataRelation like that was already there. That is true,
because the relation was defined in the SQL Server istself and is known to my
DataSet.
The next thing I tried was setting the value for the owner column in
"Animals" myself, like this:
// filling the columns with data and add a new Row- works fine
newOwner.fname = customer.firstname;
newOwner.lname = customer.name;
newOwner.phone_home = customer.phone;
MyDataSet.Owners.Rows.Add(newOwner);
// now I want to add an animal to the customer
newAnimal.name = animal.name;
newAnimal.age = animal.age;
newAnimal.owner = newOwner.ID;
MyDataSetAnimals.Rows.Add(newAnimal);
Now an exception was thrown when trying to write the data back into the SQL
Server, stating there was something wrong with the foreign key.
So where's my fault? How can I update my database with two related tables,
using the correct foreign keys etc.?
I have a problem updating data in two tables which are related to each other.
The parent table is called "Owners" and each owner can have multiple
animals, standing in a table "Animals". "Owners" has a column "ID", which is
it's primary key. In "Animals" there is a column "owner" wich is the foreign
key.
Now I want to add some owners and animals to the database and update the
tables with those new values, but this doesn't seem to work, because I am
doing something wrong, but I cannot figure out my mistake. Here's a bit of
code:
MyDataSet.OwnersRow newOwner = MyDataSet.Owners.NewOwnersRow();
MyDataSet.AnimalsRow newAnimal = MyDataSet.Animals.NewAnimalsRow();
// filling the columns with data and add a new Row- works fine
newOwner.fname = customer.firstname;
newOwner.lname = customer.name;
newOwner.phone_home = customer.phone;
MyDataSet.Owners.Rows.Add(newOwner);
// now I want to add an animal to the customer
newAnimal.name = animal.name;
newAnimal.age = animal.age;
MyDataSetAnimals.Rows.Add(newAnimal);
// Getting the updated values and write them to DB
MyDataSet.OwnersDataTable rowToadd =
(MyDataSet.OwnersDataTable)MyDataSet.Owners.GetChanges(DataRowState.Added);
MyDataSet.AnimalsDataTable rowToadd1 =
(MyDataSet.AnimalsDataTable)MyDataSet.Animals.GetChanges(DataRowState.Added);
OwnersTableAdapter.Update(rowToadd);
AnimalsTableAdapter.Update(rowToadd1);
This doesn't work. I always get an exception, where it says the "owner"
column in the animals data table must not be DBNull.
If I include a DataRelation object in my code, connecting the columns
explicitly in the manner as described above, a different exception is thrown,
where it says a DataRelation like that was already there. That is true,
because the relation was defined in the SQL Server istself and is known to my
DataSet.
The next thing I tried was setting the value for the owner column in
"Animals" myself, like this:
// filling the columns with data and add a new Row- works fine
newOwner.fname = customer.firstname;
newOwner.lname = customer.name;
newOwner.phone_home = customer.phone;
MyDataSet.Owners.Rows.Add(newOwner);
// now I want to add an animal to the customer
newAnimal.name = animal.name;
newAnimal.age = animal.age;
newAnimal.owner = newOwner.ID;
MyDataSetAnimals.Rows.Add(newAnimal);
Now an exception was thrown when trying to write the data back into the SQL
Server, stating there was something wrong with the foreign key.
So where's my fault? How can I update my database with two related tables,
using the correct foreign keys etc.?