Generated Strongly Typed Dataset and AutoNumber

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
Back
Top