The GridView's GREAT From Up Here

I recently found myself frolicking in the land of .NET 2.0 SqlDataSources and editable GridViews. After reading numerous excerpts touting the flexibility and ease with which web developers can now achieve virtually Excel-like interfaces between users and their data, I was stoked to try it all out.

In retrospect, I will certainly award snaps to those MS peepz for their notable effort towards predicting and facilitating the common-place task of giving web application users discriminating access to database stored information. I can only imagine the daunting task of attempting to allow for all derivations of developers' preferences (Lord knows we're opinionated if nothing else) while at the same time maintaining a minimal level of Integrated Development Environment complexity in order to achieve some element of intuition.

That said, I had a bit of trouble getting my (basic) editable GridView to work utilizing VS's all-encompassing, plug 'n play, SqlDataSource functionality. The predominant factor in my troubles were the subtle differences with which VS handles defining one's SqlDataSource select/update functions via referencing stored procedures versus defining them with embedding SQL statements directly within Visual Studio's SqlDataSource configuration wizard.

I'm happy to report that after a good night's sleep and some professional help, however, I worked my way through the cryptic behavior and resolved all the issues. Here are the 'GOTCHAS' I encountered, in the hopes of alleviating [some of] a fellow developer's trauma while exploring the wide world of editable GridView's.

Behavior #1: No errors being generated and no exceptions being thrown, but the GridView's update function just doesn't do anything. I encountered this problem when the condition within my Update stored procedure was not being met [WHERE MAIN_ID = @MAIN_ID]. Unfortunately the reason(s) the condition was failing weren't obvious.

• Reason #1: As stupid as I feel admitting this, I'm going to do it for the sake of others who could be making a similar error. I wasn't including the MAIN_ID field within my GridView's select query. VS did not generate any type of error in spite of the fact that I was declaring a parameter named @MAIN_ID within my Update function that was not included as part of my GridView's select query, but maybe that's so they can allow for extraneous GridView column additions. At any rate, my 'where' condition failed in this case (obviously!) since it likely had a value of 'NULL' for the @MAIN_ID input parameter value.
• Reason #2: If you're referencing a stored procedure for your GridView's select query, you're likely missing the necessary DataKeyNames attribute value within your GridView element. At a minimum, this attribute needs to hold the name of the field(s) you're using within the where clause of your update statement (in my case, I need DataKeyNames="MAIN_ID"). From what I can tell, VS will auto-populate this attribute if you're embedding the SQL directly within its SqlDataSource wizard with the names of any primary key columns it encounters, but no such auto-population will occur, nor will any errors/exceptions be generated when you're referencing your own stored procedure.

Behavior #2: A 'Procedure or function [your procedure's name here] has too many arguments specified.' exception gets thrown when you attempt to update a record within the Gridview.

• Another dissimilar behavior characteristic between using embedding SQL versus a referenced stored procedure is VS's ability to automatically determine the fields that should be included as parameters within the GridView's update statement. When using embedded SQL, the update parameters will be automatically limited to those elements included within your SqlDataSource's element regardless of the number of BoundFields you have listed within your GridView. However, when referencing a stored procedure every BoundField that does not contain a ReadOnly attribute value of 'True' (and BTW it's default value is 'False') will be included as a parameter and sent to your stored procedure REGARDLESS of what you have listed as . This seems highly un-intuitive to me, too, but it's an easy fix once you know that's what's going on. You can either change each of the BoundFields that you don't want included as update parameters to have a ReadOnly attribute value of 'true' or you can go ahead an include their column names as input parameters within your stored procedure (@CURRENT_STATUS_DESCRIPTION) and just do nothing with them.

As a sidenote, here are a couple of incorrect/outdated solutions I encountered on other forums and wasted time pursuing:

• Solution #1: The = comparison issue. One forum claimed that if you were including nullable columns within your GridView and attempted to update a column with a current value of NULL, the GridView's update procedure would attempt a = comparison, which would return false and halt the execution. I found no such behavior in my .NET 2.0 Framework and SQL Server 2000 configuration.
• Solution #2: Anything to do with the value contained within SqlDataSource's ConflictDetection attribute. After wasting time on this one, I wound up using the default value (easily achievable by just omitting the attribute altogether) and everything's working just fine.
• Solution #3: Anything to do with the value contained within SqlDataSource's OldValuesParameterFormatString attribute. [See solution #2].

Other than that, Mrs. Lincoln...how was the play?