May 2006 - Posts

Deleting the Last Record on the Last Page from a GridView with Custom Paging
30 May 06 05:36 PM | Scott Mitchell

In working on my upcoming Data Tutorials for MSDN, alert coder Amrinder S. wrote in voicing the following concern:

I have been waiting for tutorials showing how to do custom paging with the GridView. So up until now, I have been using the old DataGrid.

One thing that I noticed when I was working with the code is an issue with deleting and paging. Say for example, the GridView has 12 rows and Page Size is set to 10. If the user goes to Page 2 and deletes the two rows, the Grid will disappear from the page. After searching around I found the solution. An Deleted event handler has to be added to the ObjectDataSource control and the following line has to be added: e.AffectedRows = (int)e.ReturnValue;

The problem Amrinder unearthed has to do when implementing custom paging in a GridView using an ObjectDataSource that also supports deleting. The GridView provides two flavors of paging - custom paging and default paging. Default paging is the default paging implementation model and can be implemented by simply setting the GridView's AllowPaging property to True. While default paging is a breeze to implement, it's very inefficient since for each page of data being viewed, all of the database records are returned (even though only a potentially small subset of them are displayed to the end user). Custom paging solves this inefficiency, but requires that the page developer be able to grab the precise subset of records to display. See Custom Paging in ASP.NET 2.0 with SQL Server 2005 for more information and details on the differences between default and custom paging and for specifics on implementing custom paging...

As Amrinder points out, when deleting the last record from the last page of a GridView using custom paging, the GridView disappears, even if there are additional records in preceding pages. (This is not a problem with default paging however, as deleting the last record from the last page returns leaves you at the new last page.) This custom paging / deleting the last record in the last page thing can be fixed by simply creating a Deleted event handler for the ObjectDataSource and manually setting e.AffectedRows to some value greater than 0 (assuming the delete operation succeeded).

What in the world is the issue here?

I wondered the same thing myself and decided to find out exactly why this oddity occurs. Firing up Reflector I started digging through System.Web.... and here's what I found!

When the end user clicks the Delete button for a row, a postback occurs and the following sequence of steps transpire:

  1. The GridView tells its ObjectDataSource to go ahead and delete the record whose Delete button was clicked
    1. The ObjectDataSource invokes the Delete method
    2. The ObjectDataSource returns information about the operation just performed via an ObjectDataSourceEventArgs instance. This class contains an AffectedRows property that indicates how many records were affected by the operation. However, the ObjectDataSource does not set the AffectedRows property, though, meaning that a value of -1 is returned for this property
  2. The GridView runs a method after the delete completes. In this post-action method, it does two important things: first, if the AffectedRows value from the delete operation is greater than 0 it checks to see if the record deleted was the last record on the page. If so, it decrements the GridView's PageIndex property; regardless of the AffectedRows value, this post-action method sets the RequiresDataBinding property to True, which means that the data will be rebound to the GridView in the PreRender stage of the page lifecycle.
  3. In the PreRender stage, when it's determined that the GridView needs to be rebound, the data is re-retrieved from the data source
  4. The GridView is then rebound to the data. In its CreateChildControls method, the GridView checks to see if its PageIndex property is too large (i.e., if it's greater than the total number of pages provided by the data source). If so, the PageIndex property is decremented and then the data retrieved in Step 3 is bound to the grid.

The reason the GridView disappears after deleting the last record of the last page when using custom paging is because the ObjectDataSource fails to indicate that any rows were affected. Therefore, the GridView's PageIndex property is not updated until Step 4. That means that in Step 3, when the GridView re-retrieves the data, it's asking for the page of data whose last record was just deleted in Step 1! Namely, no data is coming back. Therefore, even though the PageIndex is decremented in Step 4, it's too late, as the data being bound to the GridView has already been retrieved and its empty.

To fix this problem we need to make sure that the PageIndex is updated BEFORE Step 3. This can be accomplished using either one of the following two approaches:

  • Manually set the e.AffectedRows property in the ObjectDataSource's Deleted event handler. Of course, only do so if the delete operation succeeded.
  • In the GridView's RowDeleted event handler, update the GridView's PageIndex if the delete occurred for a page of data that has only one record (namely, we just deleted the last record of the page). Again, you want to only do this if the delete actually succeeded. This can be accomplished using the following code:

protected void GridView1_RowDeleted(object sender, GridViewDeletedEventArgs e)
// If we just deleted the last row in the GridView, decrement the PageIndex
if (e.Exception == null && GridView1.Rows.Count == 1)
// we just deleted the last row
GridView1.PageIndex = Math.Max(0, GridView1.PageIndex - 1);

This latter technique is similar to the technique that must be used in ASP.NET 1.x when deleting from a pageable DataGrid...

It's unfortunate that this bug made its way out into production, but so it goes. At least there's a not-too-painful workaround. And the lesson to take away from this blog entry - Reflector is your friend. :-)

Filed under:
SQL Prompt - IntelliSense for SQL
22 May 06 08:41 AM | Scott Mitchell

Visual Studio has long spoiled us with IntelliSense, but one place where it's been lacking is when writing T-SQL. When I type in SELECT * FROM ..., why can't I have an IntelliSense drop-down list of the available tables and views in my database? Well, I can now with Red Gate Software's SQL Prompt tool. This tool runs as a little guy down in your system tray that detects when writing a query in SQL Query Analyzer, Management Studio, Visual Studio, or even other programs (like UltraEdit).

What's particularly cool is that for this version of SQL Prompt, Red Gate is releasing it for free!

I've been using SQL Prompt for a couple of days now and it's pretty slick. The IntelliSense UI and keyboard shortcuts are similar to Visual Studio's. My only gripe is that when working with a remote database the IntelliSense can sometimes take a bit longer than I'd like to pop up. I guess I'm spoiled with Visual Studio's immediate IntelliSense drop-down list, and having to wait a second and a half for the hundreds of tables and views to be populated in the IntelliSense drop-down list in Query Analyzer can seem a bit offputting. There is some level of caching, so subsequent queries IntelliSense drop-down lists come up much quicker, but at times it still feels a bit herky-jerky. I imagine that these are non-issues when working with a local database, or a database on the intranet.

Other than that micro-gripe, I've really been enjoying SQL Prompt. And with it's price tag ($0.00), there's no reason not to give it a whirl!

-- SQL Prompt Product Information
-- SQL Prompt Download Page

Filed under:
Esoteric Connection String Parsing Problem When Ending the Password with an Apostrophe
08 May 06 01:11 PM | Scott Mitchell

If you look at, the guideance for creating a connection to Microsoft SQL Server through a .NET application using SQL Server Authentication (standard security) is to use a connection string of the form:

Data Source=server;Initial Catalog=database;User Id=userID;Password=password;

I found a little gotcha today. If the password ends in an apostrophe attempting to assign the connection string to the SqlConnection object's ConnectionString property throws an ArgumentException. The password may include an apostrophe within the password and things will run smoothly, but if it ENDs with a password, all hell breaks loose.

Run the following code to repro:

Dim myConnectionString As String = “Data Source=server;Initial Catalog=database;User Id=userID;Password=somePassword'

'Create the connection object
Dim myConnection As New SqlConnection

myConnection.ConnectionString = myConnectionString

The last line will throw the exception; it doesn't even try to connect to the database, so you can make up values for the connection string properties, just be sure to have the password end with an apostrophe. After some tinkering and testing, the following appears to be a workaround:

  • Surround the password value in the connection string with apostrophes
  • Escape any apostrophes in the password with two successive apostrophes

Changing the connection string to the following will operate as expected:

Data Source=server;Initial Catalog=database;User Id=userID;Password='somePassword'''

The problem, if you're interested stems from code in the System.Data.Common.DBConnectionString class's ParseInternal() method. When attempting to break down the connection string into its various tokens, the parser doesn't know if the ' at the end of the password is part of the password itself or delimits some quoted text. The inner conflict caused by this ambiguity is resolved by the method by throwing an exception. (I've had jobs before where I wish I could throw an ArgumentException at someone!)

Filed under: , - Runner Up for asp.netPRO's 2006 Top Community Resource
06 May 06 02:39 PM | Scott Mitchell

Every year asp.netPRO magazine runs a “Readers' Choice Awards,” inviting readers to cast their vote for the best resource in a variety of categories. In the 2006 survey, ended up as the runner up for best Community Resource (coming in second to The Code Project). For a full list of winners and the categories polled, see the asp.netPRO Readers' Choice Awards 2006 results page.

Back in the late 90s, when the “Web” was super duper hot, there were a ton of sites that meted out awards to technical sites, and 4Guys won their fair share of these. In fact, back when these things seemed to be important, I actually maintained an “Awards” page on 4Guys. As you can see, we were given a 5-star award by itmWeb and noted twice by, which were both something to get excited about back in the dot com days. (Heh, those were some fun times back then, everything was so new and exciting. As a society, we had reached a “new economy,” where the old rules no longer applied! It certainly was fun until reality reared it's ugly head, not much unlike the housing bubble here in San Diego in, say, 2002/2003.)

Filed under:
June's Toolbox Column in MSDN Magazine
05 May 06 03:05 PM | Scott Mitchell

My sixth Toolbox column in the June 2006 issue of MSDN Magazine is now available online. The June issue examines three products:

  • OfficeWriter, a tool for efficiently generating native Microsoft Word and Excel documents without the need for having Word or Excel installed (useful for generating these types of reports on a website)
  • Event Sentry, which can be used to monitor a computer's event log or resource consumption and send notification to an administrator upon certain thresholds being met
  • Vault, a source control tool

I also reviewed the book Micro-ISV: From Vision to Reality, by Bob Walsh, which looks at the fundamentals of quitting your day job and starting up shop as a one man (or one woman) software developer. The term “micro-ISV” was coined by Eric Sink in an MSDN article (although I can't find it right now, but I believe its available on Eric's site), and refers to an independent software vendor manned by a sole individual. In a micro ISV the sole employee is responsible for everything, including all technical and design decisions (what platform should I target? what software tools should I use?) along with the business-side decisions (how much should I charge for my product? how am I going to market my product? what legal entity should I establish for my company?). For a more eloquent discussion of these challenges, see Joel Spolsky's essay, The Development Abstraction Layer.

Bob's book does a good job of distilling these core concerns down to informative chapters with a number of interviews and commentary from successful micro ISVers and others who can help with advice (a lawyer, a payment processing vendor, and so on). This book and Eric Sink's The Business of Software essays are must reads for those considering entering into the micro ISV space.

You can keep abreast of the latest Toolbox articles through the column's RSS feed or the Toolbox column category here on my blog.

Filed under:
What's with the MSDN2 Page Sizes?
03 May 06 05:08 PM | Scott Mitchell

Is it just me, or are the online web pages at MSDN2 extremely “bulky?” I really like the MSDN2 design, from the friendly URLs to the slicker tree view, but garsh darnit if it isn't slower than molasses. There's typically a several-second pause from clicking on a link in a help doc or from the tree view, and having the page loaded, and my little network traffic monitor in the Google Desktop Sidebar shows network traffic peaking at 160 KB/s for several seconds. I decided to break out Fiddler to see just what the heck was slowing everything down. I picked an arbitrary MSDN2 page - BoundField Class - and here are the results:

  • 35 Files were downloaded from Microsoft's site during the request, from the core ASP.NET page to CSS files, JavaScript files, images, and files from WebResource.axd that were, I assume, JavaScript files (seeing as they were of MIME type application/x-javascript). The total bytes downloaded for all 35 files was 1,171,064!!
  • The main culprit, by far, was the ASP.NET page, which was a whopping 1,081,612 bytes in size! Eep. Inspecting the rendered HTML, nearly 144 KB of that - about 13% - was view state. Aside from that, the majority of the bulk seemed due to JavaScript calls that tied together the main page content and the tree view on the left.
  • Sadly there was no compression on any dynamic resources. While this would add overhead on the server-side, it might be benefitial considering the rendered page sizes. Furthermore, the scripts emitted by WebResource.axd and the main page don't use caching. Which means I'm sucking down all 1+ million bytes the next time I hit this page. (The images, JavaScript files, and CSS files are cached.)

Do others experience this sort of lag on MSDN2? Alternatively, hitting up MSDN1 for the BoundColumn class help page (comparable to BoundField), 42 total files are downloaded, but the main ASP.NET page is about 1/90th the weight of the BoundField page, coming in at 13,569 bytes and the total size is 129,354 bytes (which is less than the view state size of the MSDN2 content!). Most of that total size is due to the tree view's XML content. However, since it's in a separate frame, the main page can load without having to wait for the other... much snappier than the MSDN2 counterpart.

Granted, I use the offline documentation 99% of the time (for this very reason, mostly), but when I link to Microsoft content through 4Guys articles and whatnot, it's frustrating that the help docs are so bloated. While it may not be practical, I think one way to fix this would be to require the page developers to access their own site using a modem.

(And, yes, I know I'm being a bit hypocritial here since I've created my fair share of bloated websites... in fact, I've created a page with a view state of over 800,000 bytes...)

Filed under:
More Posts


My Books

  • Teach Yourself ASP.NET 4 in 24 Hours
  • Teach Yourself ASP.NET 3.5 in 24 Hours
  • Teach Yourself ASP.NET 2.0 in 24 Hours
  • ASP.NET Data Web Controls Kick Start
  • ASP.NET: Tips, Tutorials, and Code
  • Designing Active Server Pages
  • Teach Yourself Active Server Pages 3.0 in 21 Days

I am a Microsoft MVP for ASP.NET.

I am an ASPInsider.