ForeignKeyConstraint problem with insert

Hello!

I'm using a dataset to manage SQL db. I created two tables into DataSet
(Headers and Items), I fill them with data, and I make relation between them
like (Headers-table column ID (AutoNumber) (int) to Items-table column
HeaderID (int)) ...

ds.Relations.Add(New DataRelation("Items",
ds.Tables("Headers").Columns("ID"), ds.Tables("Items").Columns("HeaderID")))

... then I have bound Headers DataTable into TextBoxes and Items DataTable
into Grid.

So far application I working well, but when I add a new record (this only
happends when trying to add new records) after clicking 'New'-button ...

.EndCurrentEdit()
.AddNew()

... I fill TextBoxes bound to parent DataTable, and then try to insert a new
row to the grid, I'll get the following error...

"ForeignKeyConstraint Items requires the child key values (2) to exist in
the parent table."

I know I don't have a datarow in parent DataTable with a primary key value
of 2 yet, but I think it must be possible to fill both TextBoxes and Grid
before saving (insert) these into database.

I hope to have explained well the problem.

--
Any help? Thanks in advance!

Mika

Frans
Sat May 15 04:15:13 CDT 2004

Mika M wrote:

> Hello!
>
> I'm using a dataset to manage SQL db. I created two tables into DataSet
> (Headers and Items), I fill them with data, and I make relation between them
> like (Headers-table column ID (AutoNumber) (int) to Items-table column
> HeaderID (int)) ...
>
> ds.Relations.Add(New DataRelation("Items",
> ds.Tables("Headers").Columns("ID"), ds.Tables("Items").Columns("HeaderID")))
>
> ... then I have bound Headers DataTable into TextBoxes and Items DataTable
> into Grid.
>
> So far application I working well, but when I add a new record (this only
> happends when trying to add new records) after clicking 'New'-button ...
>
> .EndCurrentEdit()
> .AddNew()
>
> ... I fill TextBoxes bound to parent DataTable, and then try to insert a new
> row to the grid, I'll get the following error...
>
> "ForeignKeyConstraint Items requires the child key values (2) to exist in
> the parent table."
>
> I know I don't have a datarow in parent DataTable with a primary key value
> of 2 yet, but I think it must be possible to fill both TextBoxes and Grid
> before saving (insert) these into database.
>
> I hope to have explained well the problem.
>
The relation you defined is in fact an FK constraint. So the dataset's logic
will verify if the values specified in child rows are present in the parent
rows, thus will verify the relation. If you don't want that, remove the
relation or supply the parent data first.

FB

--
Get LLBLGen Pro, the new O/R mapper for .NET: http://www.llblgen.com
My .NET Blog: http://weblogs.asp.net/fbouma
Microsoft C# MVP

Andy
Sat May 15 11:13:25 CDT 2004

You might also be interested in the EnforceConstraints property.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatasetclassenforceconstraintstopic.asp

"Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> wrote in message
news:xn0diatfy3kxnt003@msnews.microsoft.com...
> Mika M wrote:
>
> > Hello!
> >
> > I'm using a dataset to manage SQL db. I created two tables into DataSet
> > (Headers and Items), I fill them with data, and I make relation between
them
> > like (Headers-table column ID (AutoNumber) (int) to Items-table column
> > HeaderID (int)) ...
> >
> > ds.Relations.Add(New DataRelation("Items",
> > ds.Tables("Headers").Columns("ID"),
ds.Tables("Items").Columns("HeaderID")))
> >
> > ... then I have bound Headers DataTable into TextBoxes and Items
DataTable
> > into Grid.
> >
> > So far application I working well, but when I add a new record (this
only
> > happends when trying to add new records) after clicking 'New'-button ...
> >
> > .EndCurrentEdit()
> > .AddNew()
> >
> > ... I fill TextBoxes bound to parent DataTable, and then try to insert a
new
> > row to the grid, I'll get the following error...
> >
> > "ForeignKeyConstraint Items requires the child key values (2) to exist
in
> > the parent table."
> >
> > I know I don't have a datarow in parent DataTable with a primary key
value
> > of 2 yet, but I think it must be possible to fill both TextBoxes and
Grid
> > before saving (insert) these into database.
> >
> > I hope to have explained well the problem.
> >
> The relation you defined is in fact an FK constraint. So the dataset's
logic
> will verify if the values specified in child rows are present in the
parent
> rows, thus will verify the relation. If you don't want that, remove the
> relation or supply the parent data first.
>
> FB
>
> --
> Get LLBLGen Pro, the new O/R mapper for .NET: http://www.llblgen.com
> My .NET Blog: http://weblogs.asp.net/fbouma
> Microsoft C# MVP