Generated Strongly Typed Dataset and AutoNumber

20 Feb 2006 7:19 PM

Remi@newsgroups.nospam

Ive been struggling for hours on a very simple problem. How can I use the
VS2005 Dataset Designer generated strongly type dataset to insert many rows
into 2 two table linked together by a simple Parent/Child relationship?

I am using SQL server 2000.

The "Prime" table as an ID primary key, identity.
The "Secondary" table as a FK on Prime PK.

Check this code, it should insert 5 rows into each table:

            for (int i = 0; i < 5; i++)
            {
                DataSet.PrimeRow row = dataSet1.Prime.NewPrimeRow();
                row.Data = "data 1";
                dataSet1.Prime.AddPrimeRow(row);

                DataSet.SecondaryRow secrow =
dataSet1.Secondary.NewSecondaryRow();
                secrow.Data2 = "data 2";
                secrow.PrimeRow = row;
                dataSet1.Secondary.AddSecondaryRow(secrow);
            }

            DataSetTableAdapters.PrimeTableAdapter adapter = new
TestDataSet.DataSetTableAdapters.PrimeTableAdapter();
            adapter.Update(dataSet1.Prime.GetChanges() as
DataSet.PrimeDataTable);
            DataSetTableAdapters.SecondaryTableAdapter sec = new
TestDataSet.DataSetTableAdapters.SecondaryTableAdapter();
            sec.Update(dataSet1.Secondary.GetChanges() as
DataSet.SecondaryDataTable);

When i run this, i receive a SqlException. {"INSERT statement conflicted
with COLUMN FOREIGN KEY constraint 'FK_Secondary_Prime'. The conflict
occurred in database 'JunkTest', table 'Prime', column 'Id'.\r\nThe statement
has been terminated."}

The exception is thrown because the parameter passed for the foreign key is
null. Since the commnad invocation code is generated by VS2005 how am i
supposed to pass that parameter back correctly.

I am lost. I have seen countless of manual workarounds which will take me
days to code.

Please help

6 Mar 2006 8:41 PM

David Sceppa [MSFT]

Open the strongly typed DataSet in the designer and do the following:

1.) Select the auto-increment column in the parent table, then use the
Properties window to set the AutoIncrementStep and AutoIncrementSeed
properties to -1.

2.) Double-click on the DataRelation between the tables. Specify that you
want a Foreign Key Constraint as well as a DataRelation. Set the Update
and Delete rules to Cascade. Leave the Accept/Reject Rule as None.

     Setting AutoIncrementStep and AutoIncrementSeed to -1 will cause the
DataTable to generate placeholder values for new rows (-1, -2, -3, ...).

     If you're working with SQL Server 2000, the TableAdapter that Visual
Studio generated should automatically retrieve the server-generated
identity values and apply those values to the parent rows after submitting
pending inserts. Associating your DataRelation with a ForeignKeyConstraint
with UpdateRule = Cascade will cause the DataSet to cascade changes to key
values down to the related rows.

     I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2006 Microsoft Corporation. All rights reserved.