How to Bloody Your Forehead

I know that last posting was a little obtuse, and not only that, but that the form post I made in reference to the problem was also a smidgen aloof. I tried explaining this problem so some wonderful colleagues, none of whom had too much of an idea on the resolution. All of saw the boo-koos of forum posts on the topic, and all in all found ourselves with bloody foreheads, wondering how this slipped through Microsoft's QA department. Somehow, the ObjectDataSource, the DataSet/TableAdapter designer, or a combination of the above has failed us. I've seen a lot of posts all over the web about this problem and so far not a single verifiable solution that makes any sense from an enterprise pespective. 

(cough) By the way. I'm pretty angry right now, at the sheer inadequacy of this new toolset I've been given. I love Atlas, I love .Net, and I came to the conclusion the other day that my team should give the native tools a shot instead of working many hours of overtime writing our own data layers. "Let's use these awesome tools they've given us," I said. Today, I've come to the conclusion - and I'm here to proove it in pictures - that the VS 2005 designers have a serious problem that needs resolution. So if I call out your team, if I pee in your cornflakes, or if I piss you off because of the numerous hours you spent working on this tool to make it "better" or more "efficient..." Well, I'm not apologizing. I'm asking you to apologize to me and all the others out there who've found this problem over and over again. You know how you can make this much-needed apology? Fix the problem with a service pack and stop rushing your releases out the door. Wait until the stuff works. And for those of you who insisted on the rush, be more patient. Stuff is better when it works.

With that, let's begin. I'm going to demonstrate these bugs through an end-to-end example, most of which will be told using screenshots.

First and foremost,  we'll need to create a demo database. The picture below shows the simple database's structure.


We'll also add a few records to the table. Sorry guys, you helped me, so you get some credit.


Not too complicated, right? Just wait. Next, create a DataSet in the project.


Then drag the People table into the designer to create a datatable.


We're going to proove that the designer just blows itself apart when creating new - and when using existing - stored procedures (because in most enterprise environments I've worked, that's the standard via the nice DBA specifications you're responsible for upholding). So below will demonstrate a few quick steps on how we'll create new procs to be used with this DataTableAdapter implementation.


 Next, I click previous, to set this thing up to use new procedures. The next few screens describe this process without the need for much verbiage. 







Now we've got a database and some procedures. Just for good measure, below is the SQL we just generated in our SQL 2000 database (there are, surprisingly to Microsoft, a lot of customers who still use this product). Below you'll find the SQL we just generated. 

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'GetPeople' AND user_name(uid) = 'dbo')

      DROP PROCEDURE dbo.GetPeople






SELECT PersonId, Firstname, Lastname FROM dbo.People



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertPerson' AND user_name(uid) = 'dbo')

      DROP PROCEDURE dbo.InsertPerson





      @Firstname nvarchar(50),

      @Lastname nvarchar(50)




INSERT INTO [dbo].[People] ([Firstname], [Lastname]) VALUES (@Firstname, @Lastname);


SELECT PersonId, Firstname, Lastname FROM People WHERE (PersonId = SCOPE_IDENTITY())



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'UpdatePerson' AND user_name(uid) = 'dbo')

      DROP PROCEDURE dbo.UpdatePerson





      @Firstname nvarchar(50),

      @Lastname nvarchar(50),

      @Original_PersonId int,

      @PersonId int




UPDATE [dbo].[People] SET [Firstname] = @Firstname, [Lastname] = @Lastname WHERE (([PersonId] = @Original_PersonId));


SELECT PersonId, Firstname, Lastname FROM People WHERE (PersonId = @PersonId)



IF EXISTS (SELECT * FROM sysobjects WHERE name = 'DeletePerson' AND user_name(uid) = 'dbo')

      DROP PROCEDURE dbo.DeletePerson





      @Original_PersonId int




DELETE FROM [dbo].[People] WHERE (([PersonId] = @Original_PersonId))


Moving on, I'd like to take just a moment to show everyone that I'm really using the n-tier model. I'm developing this dataset and the "stuff" within it via a separate project, that's referenced by my webforms project. Here's a snapshot of the Solution Explorer to illustrate this fact.

 Next, we'll create an ASPX page, add an ObjectDataSource to it that uses the new datatable adapter, and a GridView that binds to that ODS. The next few steps are most likely pretty self-explanatory.









 Now that we've got adatabound gridview on the page, let's browse it to make sure everything's working properly.


Looks good. So I click the edit button...


 I'll leave it as-is, and just click the update button to save it again...


POOF. So, here's the deal. As one of the links above (an ASP.Net forum post that goes on longer than even this article) explains, you have to make the primary key field editable or this process will not function. Yes, the primary key field must be editable. ONE MORE TIME. The primary key field must be made editable. Do I need to go into the long list of problems associated with this? Yeah, didn't think so. Anyway, the next few pictures outline this process - the process of allowing people to (what was that?) edit the primary key field. Yes, you heard me. 



This time, everything works, as the following pictures will dictate. Keep reading, it gets interesting in a minute.



So there. Its editable alright. Hopefully the users will listen when we tell them not to edit the primary key field. Hopefully. And hopefully, if they ask why not and we tell them "because it'll totally screw up the database if you do so" they won't get too frightened, will still sign off on the application, and the DBA's will chain themselves to their desks and begin the neverending process of caretaking the enterprise database.

But wait! What if we just hide the field containing the primary key from view!?!?!? How about that? Let's try it. We'll leave the field editable, but will simply hide it from the user. The screenshot below shows how this field has been selected, and how clicking the "remove column" link will hide it.


 Then, we browse the page...


Edit an item and then click the update button again, and get the following happy screen.


So basically, we can't hide the field from the users, we have to let them edit it, and that's just the way it's going to have to be.

Problem #1: Is this a realistic expectation? Should developers really allow for the editability of a primary key column in order to use these designers? I'd go with no here, but I guess they know better than me.

Moving on. This previous example kind of infers that you allowed the designer the SQL-writing for you. Most time our DBA counterparts aren't too keen on that as an idea, and sometimes will be nice enough to even write the SQL for us. In my case, that's the deal with my team; the developers write the code and the DBA's write the procs the developers interface. So we need to back up a little and change our code so that it looks more like a realistic stored procedure we'd find in an enterprise environment.

Below is a screenshot of the procs created by the wizard. We're about to get rid of that nasty-looking @original_personId parameter. Pay attention to detail on these next two pictures, you'll see how I've changed the procedure so that it looks more realistic, like something you'd find in a real office enterprise database somewhere. This first picture shows the original (no pun intended) procs.  


And finally, the new, more realistic procs.


I then need to go back into the VS.Net dataset designer and update the table adapter. Theoretically, I could (and did, to be honest) create a whole new data table adapter. This just shortens the post and demonstrates the variation in the update and delete procedures.



I go back into the ASPX designer and refresh the ODS and the GridView so that they reflect the new changes.


 Now, as another post in that ASP.Net forum trail I referenced earlier points out, the ODS designer makes the assumption you're going to have to allow for the editing of original values, so there's a nice little attribute on the ODS object called OldValuesParameterFormatString. I hate to say this about my Redmond colleagues, but it feels like someone stuck this in without thinking up a better solution for the "we have to allow editing of the primary key column" issue. My personal opinion? Laziness or working far too many hours and therein coming up with quick fixes. Who knows, point is, the designer makes some assumptions for us when spitting out the HTML, and if you don't adhere to the original-prefixed nomenclature (haha!) you have to edit this yourself before things will work. First screenshot below shows the original values (once again, no pun intended).


And next, is the change suggested in the ASP.Net forum posts.


If we were to browse this page after having made this change we'd see that updating works. It even works if you hide the primary key column (go figure!!!). But here's the deal, people -

Problem #2: Should developers be forced to edit the designer-generated code? Should they be forced to possibly bork the (assumed) carefully-generated code requisite for a properly-functioning data mapping layer?  

So there you have it, in its entirety, with screenshots. In a sentence, the problem is simple. To use the datatable designers and default implementation to do data-mapping/binding one must allow for the editability of the primary key column or be required to edit the code generated by the designer. 

Based on the supposition that solution A basically breaks down the very idea of a properly-functioning data-mapping architecture, and that solution B requires that developers not only change the code that's generated, but that they know that the code needs to be changed in the first place, and more importantly just how it should be changed. So what's the point in using it at all?

Especially when you consider the fact that it doesn't work properly! 


  • In your example, when you "hide" the column, you are actually removing it. This is the same as not having it there in the first place. Also, you can set the OldValuesParameterFormatString in the property grid. It has to default to something, and in this case they made it default to an example of the most complex scenario.

    I agree the designer experience for creating a data layer and ui should be more straight-forward, but I'll be honest- this post is a little over the top.

  • I thought of that as a potential comment and problem. I have re-done this example again and again and every time it has the exact same output. In addition, I also set the Visible property of the column to False without Removing it, and the same results occurred.

    Today the problem has grown somewhat. Let's say you use a stored procedure that returns Firstname, Lastname, and PersonId, and from that resultset you create your datatable. And you have an Update or Insert procedure that sends in parameter names like ipFirstname, ipLastname, and ipPersonId (in our world we have nomenclature standards such as these). It fails there, too, and despite the fact that the mapping is correct within the designer and that everything looks okay, when the page is executed it fails. However, the failure indicates that there is no non-generic method containing parameters firstname, lastname, personId, ipFirstname, ipLastname, ipPersonId. So basically, it makes the assumption that you're passing in everything. For some reason, it doubles up on the parameters and therein explodes.

    So you can't use the designer to "talk to" existing procedures, to create procs, or to generate SQL and all work.

    And this post is not over the top, I'll refute that comment. IDE's are to make life quicker, faster, and easier. This IDE does none of the above (in reference to this particular issue, that it). I LOVE the VS.Net 2005 IDE for everything else, but in this capacity it not only fails, but it sucks. It needs to be fixed, MS needs to remove the requirement of allowing editability on a primary key, and the designer needs to work out of the box, without any edits being required.

    If it doesn't, the tool shouldn't be marketed as it has been marketed.

  • There is a DataKeyNames property on the GridView that you can use to include columns that you want to round-trip, but not provide UI for. These values are encrypted to avoid tampering with, and so are secure.

    You could use this to associate a primary key value for the row, without requiring any UI for it (editing or not).

    This series of tutorials also goes into more detail on using TableAdapters and the DataSet designer with the ObjectDataSource and might help:

    Hope this helps,


  • Thanks a lot, Scott, but that solution doesn't help. Any chance you have any ideas how to solve that most recent problem (I mention it in my own comment at the end of this thread).

    We're talking to Microsoft Support about this issue this morning. I'm hoping that we're offerred something better than "a workaround," and that there's a hotfix coming out to resolve this issue soon. I love this product, but this has _GOT_ to be fixed, it would make life so much easier for so many of us who have existing procs that need to be "interfaced with.'

  • Has this problem been addressed? I have an architect here who seems to have the hots for 2-tiered web apps. Don't ask me why! He's 30 and thinks he's smarter than everyone at Microsoft, Google, and Apple combined! From what I have read above, it seems that if you follow the paradigm of the Data Tutorials, everything should work just fine. Is this correct? Was this problem addressed in the last service pack or is there a hot fix?

    Ken Jinks

Comments have been disabled for this content.