Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > .NET > Other .NET > ADO.NET
Password Reminder
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
ADO.NET For discussion about ADO.NET.  Topics such as question regarding the System.Data namespace are appropriate.  Questions specific to a particular application should be posted in a forum specific to the application .
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ADO.NET section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 7th, 2005, 02:00 PM
Friend of Wrox
Join Date: Apr 2004
Location: San Francisco, CA, USA.
Posts: 204
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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:


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!


Reply With Quote
  #2 (permalink)  
Old September 10th, 2005, 05:09 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts

Cross post.

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting a untyped dataset to a typed dataset daphnean Visual Studio 2005 0 July 13th, 2006 01:16 AM
login dillema vijayaptetheone General .NET 1 March 29th, 2006 07:52 AM
login dillema post 2 vijayaptetheone General .NET 0 March 22nd, 2006 05:20 AM
Dataset Dillema Aaron Edwards ASP.NET 1.0 and 1.1 Professional 4 October 6th, 2005 02:07 AM
Re: SQL Server dataset to ACCESS dataset dazzer ADO.NET 0 March 22nd, 2004 05:28 AM

All times are GMT -4. The time now is 01:55 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.