Dataset Dillema
I have an ASP.NET driven CMS application I developed for a small online retailer of kids t-shirts.
The CMS attempts to use an ADO.NET dataset to duplicate the functionality of the underlying database. The database has 2 tables:
Categories
Products
Pretty standard, huh? The primary key in Categories is called pkCategoryID, which has a foreign-key relationship to a field in the Products table called fkCategoryID (I like to use the pk and fk prefixes, I'm not sure why).
Anyway the dillema is that the CMS allows my client to Add new categores and products, make edits and do deletions, all using a dataset stored in the Session object. Then, when they're all done they click the 'Save All' button, which then updates the changes/deletions/additions in the dataset back to the underlying database.
This worked great until my client added a new Category and then deleted it.
Now when she adds a new Category, the dataset thinks the pkCategoryID should be 9 (there were 8 in the database already). But the database itself will ultimately wind up using a pkCategoryID value of 10, because once you delete a row with an identity value, it doesn't repeat that value, but goes onto the next one.
Does that make sense: If you add a row, and the ID is X, and then you delete it, the next time you add a row, the ID does not come up as X, but X+1. But my dataset does not seem to know about this behavior.
Then, when my client then adds a Product to that Category, it winds up with a fkCategoryID value of 9, which is what the dataset expected, rather than the 10 that the database wound up actually using. The end result is that my client's customers click on a Link to Category 9, and then they get a blank screen, because the Products are actually coded to Category 10.
So far what I've been doing is manually changing the fkCategoryID values of the new products to the correct CategoryID, but that's a drag. I've also asked my client to (for now) add the Category, then click 'Save All', then add the Products, then click 'Save All' again. But this makes me look foolish.
Is there a dataset-enabled solution to this? A way for the dataset to know what the next identity value WILL be? Or is there a way to tell my database (SQL Server 2000) to start recylcling old identity values? Or is the answer to just not use a dataset for additions, but execute them directly using a SQL command?
Thanks for your help!
Aaron
|
