.NET Discussion

.NET Issues, Problems, Code Samples, and Fixes

ASP.NET: How To Use BulkEditGridview To Save Hours In Database Editing

The GridView is a fantastic, versatile, and powerful tool. Unfortunately, it is also unrealistic in several aspects. Say you have a page in your website’s administrative back-end that is to update all of your prices for your products. With a typical GridView, if you wanted to update, say, 10 of these prices, you will require at minimum 20 postbacks, which depending on the speed of your server can be a lengthy process. Now imagine that you have over 1000 lines to edit. 2000 postbacks, minimum… sound like a daunting task that may take you hours in just waiting for your server to respond?

What if I told you that you could edit an unlimited number of records in a GridView with one click, one postback, and one call to your database, and that only the records you modified would be updated? What if I told you it was way, way easier than you think? You may think I am crazy at first if you have any experience with GridViews, but let me introduce you to the panacea of GridView woes, a little tool created by Matt Dotson called BulkEditGridView (more recently moved to this location on CodePlex). I have implemented this tool into nearly every editing application I have that requires a GridView and it has shaved not minutes or hours, but days of off time waiting for the server to post back.

I have been using this tool for quite some time now, but have waited until I had a full understanding of its power and nuances before I posted about it.  The links I provided you will give you more background on the why and how its production came about (and where to download it), but the documentation on its use is a bit on the scattered side. That’s where I come in!

To give you a bit of perspective, the BulkEditGridView is little more than a custom user control that inherits the GridView. However, the functionality it provides is considered more “real world applicable”, because on binding, it places every row in the GridView in Edit mode automatically. You may then either tab through columns or click through records you wish to edit, and then once you’re done with ALL your editing, you click your designated save button and everything is updated! It also knows which rows were edited and stores them in a Generics List Of(GridViewRow) called DirtyRows, which can be accessed programmatically.

Enough already, you say. How do I use it? Here’s a step-by-step of how I have implemented it. Please note that this is how I have implemented it. There may be better ways, but this is how it has worked for me. In this documentation, I am going to assume you have downloaded and installed the .dll for this control.

Step 1: Add BulkEditGridView to your .aspx page

Remember, it is essentially a GridView, so add it like you would to any other page. You may specify bound columns, template columns, or anything of the like the same way you would a regular GridView. The only extra property you must specify is the SaveButtonId property. This is just a button you have placed on your page that when pressed will make the BulkEditGridView work its magic.

Step 2: Add Save Button to page

As mentioned in the previous step, the BulkEditGridView requires that you specify which button is to be pressed for it to run. Any codebehind for this button is not required, but I typically add some catch-all code for when no edits have been made:

Protected Sub btnSaveChanges_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
	If BulkEditGridView1.DirtyRows.Count = 0 Then
		litMsg.Text = "No bulk updates made."
	End If
End Sub

Step 3: Define your RowUpdating Event Handler

This is where all the magic happens. When your Save Button is pressed, if you have any DirtyRows the RowUpdating event will fire for each row in that collection. You should create your RowUpdating event the same way you would for a regular GridView, but include the following modifications:

  1. Declare a Static counter variable (Static count As Integer), and immediately increment it at the beginning of your event handler. This will come in handy later.

  2. Declare a Static sql string variable (Static sql As String) or System.Text.StringBuilder if you are looking to optimize. This is where you will store all of your UPDATE strings for your database so that once you have iterated through all your rows, you may send them to your database to be run all at once. In other words, rather than update your database every time the RowUpdating event is called, return the sql string from your update function that you would typically run and add it on to a growing string of calls to make.

  3. At the end of the RowUpdating event, include some conditional code like:

    If count = BulkEditGridView1.DirtyRows.Count Then
    	Dim rowIndexes As New StringBuilder
    	For Each row As GridViewRow In BulkEditGridView1.DirtyRows	'find out which rows were updated
    		rowIndexes.Append(row.RowIndex + 1 & ", " ) 'real count, not 0 based
    	Next
    	rowIndexes.Remove(rowIndexes.Length - 2, 2)	'get rid of final comma
    	msg = "Rows " & rowIndexes.ToString & " have been updated."
    	AddOrUpdateData(sql.ToString) 'your function to call your database
    	BindGridView() 'your function to bind your GridView
    	litMsg.Text = msg
    End If
    

    The preceding code does four things: it checks to see if it has completed running the RowUpdating for each modified row (remember that static count variable?), it iterates back through the DirtyRows so that you may relay back to the user which rows were updated, it updates your database using one call (the AddOrUpdateData function, which is however you call your database to make updates), and it re-binds your GridView. NOTE: when building your SQL string, remember to end your separate UPDATE commands with a semicolon!

Some notes to consider:

  • If you are using template fields, do NOT include controls you wish to modify (textboxes, etc) inside of a Panel control. For some reason it will not detect them. What I did to get around this (if I wanted to display a panel on RowDataBound based on a variable, for instance) is I created two Literals: one where I would want to place the <div style="display:none;"> and one for the closing div tag.

  • At least for me, Intellisense doesn’t work for the BulkEditGridView in the .aspx sourcecode edit mode, so either know your stuff or drop a regular GridView into your page first, build it the way you want, and then copy and paste the guts into a BulkEditGridView once you’re done.

  • Once you update all your items and rebind your BulkEditGridView, it will show up again in edit mode. To toggle this “Bulk Edit” mode, I have sometimes created one BulkEditGridView and one normal GridView on the same page, and then a button to bind and show one and hide the other. For me it has created a seamless user experience and only costs you one postback.

And that’s it! I know this is a long post, but when you get used to using this amazing control, you will see that it boils down to only a few different things you need to change to get it working for you.

kick it on DotNetKicks.com

Advertisements

April 6, 2008 Posted by | ASP.NET, Generics, GridView, MySQL, Tips & Tricks | | 20 Comments

ASP.NET: Issues with Generics

The introduction of Generics into .NET 2.0 was a huge improvement over the previous methods of aggregating objects. There are many, many new things you can do with Generics that could not be done previously, in addition to making harder tasks easier and more efficient. For example, I can take a Generics list of my own objects and bind them to a GridView and be able to use the objects’ properties as bound datafields! Something not possible before. However, they do, of course, come with problems of their own. For instance, for the novice to intermediate user, the concept of predicates and actions is enough to make your (my) head spin. And it seems like this is the most efficient way to find things, even though to me, it makes no sense. I suppose until I decide to really hunker down and attempt to learn how to use these features, I’ll stick with my For Each [whatever] in MyGenericsList ... Next code.

Here is where I come across my problem, though. One of my business objects has a Generics list of another object (AllPrices as List(Of Price)) consisting of prices and options. This is bound to a GridView, which displays all the properties. On update, I have all the properties of the selected Price object update, some numbers are automatically determined by various variables and business calculations, and then the object is matched up back to its appropriate item in my AllPrices Generics collection, and the GridView is rebound. This works wonderfully for all but one situation, which has me vexed and confused. Here’s how I match the item and recalibrate my AllPrices Generics list (in my RowUpdating event):

' "newPrice" contains all my price changes. it is of type PriceInfo.Price
Dim updatedprice As New PriceInfo.Price
		For Each updatedprice In Prod.Prices.AllPrices
			If updatedprice.PriceID = newPrice.PriceID Then
				updatedprice = newPrice
				updatedprice.UpdatePrice() 'updates the database
				Prod.Prices.AllPrices(Prod.Prices.AllPrices.IndexOf(updatedprice)) = updatedprice 'update AllPrices ****
				Exit For
			End If
		Next
		GridView1.EditIndex = -1
		BindGridView() 'rebinds the AllPrices Generics list to the GridView

What this code does is finds the matching object in the list by PriceID, updates it with the changes (updatedprice = newPrice), updates the database, and then re-inserts the object into the AllPrices Generics list. However, in one situation, I get an “out of index” error at the line with four asterisks (****), even though the database updates correctly. Logically, this doesn’t make sense to me, since 1) it is finding the object in the collection (it can’t get to that line unless it does), 2) it is updating the database correctly (I’ve verified this in my database), it just isn’t updating the AllPrices list!

What the crap?

Any help is much appreciated. Also, if you’ve experienced similar issues with Generics, please post them! Or if you have some normal, realistic way to use actions and predicates! 🙂

Status: Solved! (12/11/07)
Solution: I was making things way too complex, apparently. I was running two loops (the For loop and the IndexOf() loop) and trying to match objects to one another. Rather than include all this ridiculous complexity, I created a simple counter variable (Dim i As Integer) outside the For loop and just incremented it after the conditional statement runs (i += 1), and replaced Prod.Prices.AllPrices(Prod.Prices.AllPrices.IndexOf(updatedprice)) = updatedprice with Prod.Prices.AllPrices(i) = updatedprice. Now everything is gravy. Thanks to Nick Berardi for the tips!

December 11, 2007 Posted by | ASP.NET, Errors, Generics, GridView | Leave a comment

ASP.NET Gridview: Merging Cells

I had been searching for a way to merge cells in a GridView, and recently the answer was given to me by Mike Flavin (thanks!). Place the following code, or some variation thereof depending on your requirements, in the RowBinding event of your GridView:

If e.Row.DataItemIndex % 2 = 0 Then
     e.Row.Cells(0).RowSpan = 2
     e.Row.Cells(1).RowSpan = 2
End If
'Remove the extra cells created due to row span for odd rows
If e.Row.DataItemIndex % 2 = 1 Then
     e.Row.Cells.RemoveAt(0)
     e.Row.Cells.RemoveAt(0)
     e.Row.Cells(1).HorizontalAlign = HorizontalAlign.Center
End If

This code should take every cell in the first and second columns and merge it with the cell directly below it. Of course, this should be used very carefully, because you could screw up your data if each record does not have another corresponding record with it. However, this shouldn’t be too difficult to program around by adding data checks.

November 15, 2007 Posted by | ASP.NET, GridView, Tips & Tricks | 5 Comments

ASP.NET and AJAX: UpdatePanelPopupExtender Is An Awesome Tool

Supposing that your site is set up for ASP.NET and AJAX, adding a popup panel to any control is a snap, thanks to Raj Kaimal’s UpdatePanelPopupExtender (UPPE).  Normally I am not one for taking other people’s code or user controls, but I was in a bind and needed something like this very quickly, and it worked amazingly well.  Essentially, the UPPE acts as a method to hide and show a details or “extra info needed” panel using AJAX.  You can even easily attach it to command buttons in GridViews!

It is very easy to install: simply drop the DLL in your /bin/ directory and reference it, and then add it to your toolbox (second section). Then drop it where you want it, usually at the bottom of the page because during runtime its page location will be determined dynamically.  Here’s code that I used to make it work on the .aspx page:


<cc1:updatepanelpopupextender id="uppeDetails" runat="server" targetcontrolid="upDetails" offsetx="0" offsety="0" CalloutColor="#7C6F57" CalloutBorderColor="Black" AutoPostBack="True">
 <div style="display:none;">
  <asp:updatepanel id="upDetails" runat="server">
  <contenttemplate>
<!-- ENTER WHATEVER YOU WANT HERE -->
  <div class="popup-details">
  <h3>Inventory Notes: </h3>
  <asp:textbox id="txtInventoryNotes" runat="server" textmode="multiLine" rows="5"></asp:textbox>
  <p><asp:button runat="server" id="btnSaveNotes" text="Save and Close" onclick=btnSaveNotes_Click> <asp:button runat="server" id="btnCancelNotes" text="Cancel"></p>
  </div>
<!-- END -->
  </contenttemplate>
  </asp:updatepanel>
 </div>

The necessary parts of the above code are the <div style="display: none;">, which ensures that the panel is hidden from the get-go, the autopostback="true" property in the control declaration, and the fact that it is referencing an update panel within your hidden div. Anything else is pretty much optional, and you can put anything in there, too.

Once you have that set up, you have a few steps in your code-behind to set up (all control names reference the controls from the above example). First, import the library (Imports Flan.Controls). Then in your Page_Load event, you need to reference the “close” button for your UpdatePanel, like so: UpdatePanelPopupExtender.RegisterCloseControl(btnCancelNotes, upDetails)

Then the last step is to have the control show up where you want it to, usually next to the button that says “Edit” or “See Details” or whatever, and to do that, just put the following code in that control’s Click event: uppeDetails.ShowAt(yourControl). And that’s it!

If you want to wire it to a control in a GridView, it is a little trickier, but not because of the UPPE. Rather, you need to dynamically locate the control to wire the UPPE to. Here’s how you do it using the “SelectedIndexChanging” event of a GridView:

Protected Sub gvExisting_SelectedIndexChanging(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.GridViewSelectEventArgs)
		Dim ctl As Control = CType(gvExisting.Rows(e.NewSelectedIndex).Cells(10).Controls(0), Control)	'Cells(10) is the location of my CommandField column
		gvExisting.SelectedIndex = e.NewSelectedIndex
		txtInventoryNotes.Text = gvExisting.DataKeys(e.NewSelectedIndex).Values(1)
		uppeDetails.ShowAt(ctl)
	End Sub

What I did in the above example is figure out exactly which cell was clicked, then pull the datakey from the GridView (which happens to contain the info I want) and then setting the textbox in my UPPE to that information, then showing the UPPE.

It’s all very easy to do, and extremely helpful for UI. You can have entire detail panels just pop up for editing, take care of business, then disappear again. No more need for separate detail pages, just use an UpdatePanelPopupExtender!

Here’s the download location for the UpdatePanelPopupExtender and sample code.

kick it on DotNetKicks.com

October 31, 2007 Posted by | AJAX, AJAX Toolkit, ASP.NET, GridView, Tips & Tricks | | Leave a comment

ASP.NET: Tutorials for Every Standard Server Control

While trying to determine the best way to display certain data, I came across a veritable goldmine of information: Microsoft’s ASP.NET QuickStart Tutorials.  This resource has a tutorial on every standard control in the ASP.NET library, including working samples and source code.  I learned a whole lot about controls that I thought I already knew everything about and controls that I’ve never really had a desire to learn about because they looked too complex or like something I wouldn’t need. Even if you are an “expert” programmer, perusing these tutorials will teach you a thing or two.

Especially check out the section on the DataControls. I didn’t even know there was such a thing as a DataList control, and now I don’t know if I’ll use a GridView again!  Really great resource.

October 10, 2007 Posted by | ASP.NET, GridView, Resources, Tips & Tricks | 1 Comment

ASP.NET: GridView Update/Edit/Cancel, HyperLinkFields, and DataKey Retrieval

Today was spent mostly tackling the GridView’s Update/Edit functionality, and I got incredibly frustrated, at first. This was my first foray into this part of the GridView’s functionality, since I haven’t really had to develop anything using it since the switch to 2.0, as everything in DataGrids still functioned fine. With a little bit of research, a lot bit of patience, and some help from the internet community, I was able to solve all my issues, and pretty elegantly at that.

Basically, I was trying to convert an existing data-display-only GridView (ie, no special functions) into one where I can update a field, namely the inventory for displayed products. After working with DataGrids so efficiently, I was scared that GridViews were going to be a million times more complex. I suppose my eyes got big when I saw all the new properties and methods, and wanted to try them all out.  Big mistake.

Most articles I found were for hardcoded datasources (especially on MSDN, ugh) and didn’t really help me much, so this post will be referring to GridViews with dynamically bound datasources. First, create a GridView with three bound columns and an Update/Edit/Cancel (CommandField) column:

<asp:gridview id="GridView1" runat="server" autogeneratecolumns="False" onrowediting=GridView1_RowEditing datakeynames="productid" onrowcancelingedit=GridView1_RowCancelingEdit onrowupdating=GridView1_RowUpdating>
       <columns>
       <asp:hyperlinkfield datanavigateurlfields="productid" datanavigateurlformatstring="/products/index.aspx?productid={0}" text="Product" target="_blank" />
        <asp:boundfield datafield="inventory" headertext="Inventory" />
        <asp:commandfield showeditbutton="True" />
        <asp:boundfield datafield="productid" visible="false" />
       </columns>
      </asp:gridview>

Nothing in there should be shocking to anyone: all your events are handled, you have a HyperLinkField that uses the DataNavigateURLFields property to insert a ProductID, and you have set your DataKeyNames property to your hidden field which holds your ProductID.

Because we are not using the GridView’s built-in data model and are binding dynamically, the GridView will not switch into and out of Edit Mode automatically. Thus, in order to switch it into Edit Mode, in each of your event handles (RowEditing, RowUpdating, and RowCancelingEdit) you must do two things:

  1. Change the GridView’s EditIndex appropriately
  2. Re-bind your GridView

These are both very easy to do, and your RowEditing and RowCancelingEdit handles should be simple, like this: 

Protected Sub GridView1_RowEditing(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs)

GridView1.EditIndex = e.NewEditIndex 'set to selected row
BindGridView() ' your own subroutine that you use to bind your datagrid

End Sub

Protected Sub GridView1_RowCancelingEdit(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs)

GridView1.EditIndex = -1 'set to no selection
BindGridView()

End Sub

One issue I ran into while attempting to figure this all out was if I didn’t rebind my GridView at the end of each event, I was required to click twice to get it to select the correct row, and if I clicked around, it would always be a row behind. So be sure to rebind your GridView at the end of your event handler. 

The RowUpdating event is where it gets tricky, since pulling values is not necessarily intuitive. You can’t do something like GridView1(i)(j).Text = str or anything even close. Instead, you must cast the cells as controls. Here’s an example of how I did it in my RowUpdating event handler:

Dim name As HyperLink
Dim inv As TextBox

name = CType(GridView1.Rows(e.RowIndex).Cells(0).Controls(0), HyperLink)
inv = CType(GridView1.Rows(e.RowIndex).Cells(1).Controls(0), TextBox) ' .Cells(1) refers to the 2nd column

Once you’ve casted them, you can now easily get their values by invoking their .Text property, ie, name.Text. Remember that my first column was a HyperLinkField, so in order to pull that value, I must cast that column as a HyperLink.

Finally, to pull your hidden value that you’ve dubbed a DataKey in the GridView’s DataKeyNames property, do the following:

Dim ProductID as Integer = GridView1.DataKeys(e.RowIndex).Value

EDIT 3/5/08: I forgot to mention in this article how to retrieve “Read-Only” data. You cannot convert a read-only cell into a textbox, so you must retrieve the value another way:

Dim str as String = GridView1.Rows(e.RowIndex).Cells([your cell]).Text

Then do whatever you need to do to update your database, set the EditIndex to -1, and call your BindGridView() subroutine. Everything should be sorted out when the “Update” link is pressed. To enhance this process, be sure to include your GridView in an AJAX UpdatePanel.

Hope this saves someone a full day of research (and possibly some hair)!

kick it on DotNetKicks.com

September 26, 2007 Posted by | AJAX, ASP.NET, GridView, Tips & Tricks | 148 Comments

ASP.NET GridView: How To Add A HyperLinkColumn Dynamically

I have been searching all over the web for a way to do this, and it doesn’t appear as if anyone else knows! How do you add a HyperLinkColumn to a GridView dynamically at runtime? If this isn’t possible, is there a way to modify its properties? I can’t seem to access the HyperLinkColumn to modify the Target property, which doesn’t seem right.  Shouldn’t you be able to access any property programmatically?

Status: Solved! (10/23/07)
Solution: See comment by Kevin Brock. Thanks, Kevin!

September 24, 2007 Posted by | ASP.NET, GridView | | 13 Comments

ASP.NET and MySQL: How To Build A Search Engine For Your Website In 3 Steps

About a year ago I built a fully-functioning search engine for my website, Columbus Supply. It took me all day to figure out the SQL syntax, but afterwards, I was very pleased with the results.  MySQL has very powerful search capabilities with the MATCH() and AGAINST() functions when used in conjunction with the FULLTEXT attribute. Using these, MySQL determines a “score”, which is a relative relevancy to the search query. 

The technique I used requires a few queries, but only one call to the database if all the queries are strung together and sent at once (separated by semicolons, of course 🙂 ). First, create a temporary table with all the necessary columns from all your different existing tables:

CREATE TEMPORARY TABLE temp1 (PlayerID INT, Name TEXT, Age INT, Biography TEXT, FULLTEXT (Name, Biography)) ENGINE=MyISAM;

Then fill your temp table with all the data from your tables. Be sure to do any replacing and concatenation here:

INSERT INTO temp1 (PlayerID, Name, Age, Biography)
SELECT PlayerID, Name, Age, REPLACE(Biography,'</p><p>')
FROM tblPlayers;

Now you are going to do a SELECT statement against that table for all the columns you want. The real magic happens in the WHERE clause:

SELECT *
FROM temp1
WHERE MATCH(Name, Biography) AGAINST ('YourSearchString')

Remember how we declared ‘Name’ and ‘Biography’ as FULLTEXT? Here’s where that comes into play. You are selecting everything, but you are comparing against the columns you deemed as FULLTEXT. With the MATCH() and AGAINST() functions in the WHERE clause, the results are automatically ordered by relevancy. If you want to see the actual relevancy, just include your MATCH() and AGAINST functions in the SELECT statement using AS to name it something, like “score”.

And that’s it! You can take those results and populate a GridView or any other control. It can get complex with all the unions and joins from all your tables, but this is the concept that I came up with. It has served me well, and my results are relatively accurate. You can extend this further with filtering in your WHERE clause, such as WHERE Age=24 AND MATCH(...

NOTE: This will not return a result for some queries for various reasons, including but not limited to: the use of common words that MySQL has deemed semantically negligible, searches of three letters or less, or searches where the search string appears in 50% or more of the records in the particular dataset you are pulling from, ie, your temp table.

In the instance where a search is three letters or less, you can do the first two steps as usual, and in the last step, instead of using MATCH() and AGAINST(), use LIKE like so:

SELECT *
FROM temp1
WHERE Name LIKE '%YourSearchString%' OR Biography LIKE '%YourSearchString%'

Granted, this will result in a much less accurate recordset, but it will still yield some relevant records, which may be enough for your user. They did, after all, enter a three-letter-or-less query!

More reading on this technique: Full-Text Search | Fine Tuning Full-Text Searches

Hope this helps someone out there!

September 20, 2007 Posted by | ASP.NET, GridView, MySQL, Tips & Tricks | 4 Comments

ASP.NET GridView: ‘The Gridview [x] Fired Event RowDeleting Which Wasn’t Handled’

Came across this error today which left me scratching my head for a bit. Basically, I have a Gridview with a Select column, two Button columns, and a few Bound columns.  I gave each button column a unique CommandName property, and one of them was “Delete”, because I was using it to delete the row (I was doing it manually).  I am handling the buttons’ click events in the RowCommand event and discerning which is which by e.CommandName. However, every time I clicked the delete button, I would get the error:

The Gridview [x] Fired Event RowDeleting Which Wasn’t Handled

This struck me as odd, because I wasn’t doing anything with a “RowDeleting” event. I tried putting some random code in there, nothing worked, although the code was getting executed every time. After trying any and everything I could think of, I decided to change the CommandName “Delete” to “Delort” (courtesy of Strongbad 🙂 ) and viola! It worked!

Apparently, the CommandName “Delete” is reserved for the Built-In Editing Functionality of the GridView. I’m sure there are others reserved, too, but I didn’t really look into it any further. Who knew?

August 31, 2007 Posted by | ASP.NET, Errors, GridView, Tips & Tricks | 5 Comments