Introduction
For those of you that read through my last article, the introduction to the ASP.Net DataGrid control, you have probably been spending most of your time since then pumping out tables on web pages with the greatest of ease. If most of your time is too much of me to ask, then I will narrow my request to at least one attempt and try each of the methods I described. It can only help you, as it will be much easier to understand what’s going on in this article.
I’m not going to spend any time really on a review, as I’ll assume you’re now somewhat comfortable using the basic functionality of a DataGrid. If not, feel free to go back and read the first article, (Behold the Power of the DataGrid!). As for this article, we will go through two revamped concepts, paging and editing. Roll up your sleeves…
{mospagebreak title=Paging}
“Umm, what is paging?” you ask. Simply put, it is the means by which we can apportion our data into chunks and deliver them a piece at a time. A good example of this is a Google search. By default you receive 10 search results at a time, or ‘per page’. Then you have the ability to see the next ten results, the next ‘page’ of results.
We can and should apply the same methodology in our applications. Why? Well, suppose you’re building an application to manage inventory. What are the chances of having only four pieces of inventory in total? I would guess that it’s roughly about the same probability of me finding a million bucks in the mail tomorrow. Not null, just very improbable. For example, an inventory app I worked on for a company of only 200 employees has thousands of entries being tracked. Picture surfacing all entries at once, and you quickly understand the usefulness of paging. The same holds true for forums, e-commerce website, etc.
With ASP, creating paging involved a mix of server and client-side scripting, and was just a pain to implement, maintain, and reuse. ASP.Net solves all that. In fact, you won’t believe just how easy this is. I’m hoping that you still have the basic code in mind or in an IDE to work with because I’m not going to retype it; I’m just going to start adding in the new pieces. The first thing we do is modify our DataGrid itself:
[code]
<asp:DataGrid id="dgSocks" runat="server"
AllowPaging="true" OnPageIndexChanged="pager" PageSize="3" >
[/code]
This is fairly straightforward. We are just saying that we want to allow paging, what exactly we want to do when the page index changes (a subroutine called “pager”), and defining the default number of results to return per page.
You can combine this with any other options, such as sorting. But just make sure that you have set the AutoGenerateColumns property to to true, so that .NET knows to handle it.
It’s good to know exactly what is going to be produced here. Basically, you can expect an additional row added to th bottom of your table (grid). At the left side of the row will be two arrows (< & >) indicating that you can advance or retreat to another page. Now most likely you are as unsatisfied with default settings as I am, so thankfully we have a good degree of customizability (I think I may have just invented a word) available to us. We could easily center the arrows by adding the following:
[code]
PagerStyle-HorizontalAlign="center"
[/code]
We may also come to the conclusion that the arrows just won’t cut it, we want to know exactly where we are, we want numbered pages. Well hold on to your hat, because you’re about to be blown away by the immense amount of extra code thats required:
[code]PagerStyle-Mode="NumericPages"[/code]
Wow, was that ever difficult?! It took all of let’s see, about 30 characters? I can almost hear you singing your paean to Microsoft already! Oh, but it gets better still; let me show you the editing capabilities!
{mospagebreak title=Editing}
I honestly can’t tell you the number of times I’ve had to do supply editing capabilities within a table of data. This involves having hidden fields to track the row (item) you wish to edit, the actual text fields to hold the values, and the server-side intelligence to pick up the modification and commit it to the database. Not the most difficult thing in the world to do, but tedious nonetheless.
Well, wouldn’t you know it, Microsoft has been so gracious as to make our lives easier in this arena as well! Unfortunately it’s not as simple as typing AllowEdit=”true”. There is slightly more required than just one or two lines, but it’s still pretty sweet. Basically all we need to work with is three properties: OnEditCommand, OnCancelCommand, and OnUpdateCommand. There is also an OnDeleteCommand if you need it. And to make it all work, we just need to tell the DataGrid which field is the key for each row, which we do by use of the DataKeyField property.
So, as we add these to our DataGrid, we see it look something like this:
[code]
<asp:DataGrid id="dgSocks" runat="server" AutoGenerateColumns="true"
DataKeyField="id" onEditCommand="editMode" onUpdateCommand="doUpdate"
OnCancelCommand="cancelEdit">
[/code]
Now, we see a bunch of subroutines being referenced here: editMode(), doUpdate(), and cancelEdit(). Let’s first worry about editMode() and cancelEdit().
[code]
sub editMode( s as Object, e As DataGridCommandEventArgs )
dgSocks.EditItemIndex = e.Item.ItemIndex
doBinding
End sub
sub cancelEdit( s as Object, e As DataGridCommandEventArgs )
dgSocks.EditItemIndex = -1
doBinding
End sub
[/code]
Let me explain what’s going on here. As soon as you turn on editing as we have, you get an extra column in your table. In each row is an edit ‘button’. When we click edit for a given row, the ‘item index’ is passed to the editMode() subroutine, which in turn understands and signals that you want to edit that specific row, so put it in edit mode! What this means is that all modifiable fields are replaced by text boxes containing the modifiable values. And the edit ‘button’ is replaced by Cancel and Update.
So go ahead and try this. Make sure you have created an empty doUpdate() subroutine to circumvent any compile-time errors.
What happens? Well, hopefully exactly what I described! Now if you have paging still turned on, try paging while in edit mode. You’ll see that the edit mode stays on, and now the same row on the next page is being edited. This is because it’s the same index, make sense?
So how do we get out of edit mode? Try clicking update. Does that do it? It shouldn’t, because you’ve got nothing within the doUpdate() subroutine to tell it to. But if you click cancel, we can see the Edit Item Index is changed to -1, thus exiting edit mode.
Alright, now that we have that under our belt, we just need to create the logic behind the actual updating of the item. Here’s what doUpdate should look like:
[code]
sub doUpdate( s as Object, e As DataGridCommandEventArgs )
Dim strConn, strSQL as String
dim updCMD as OleDbCommand
Dim intID, intQuantity as Integer
Dim dblPrice as Double
intID = dgSocks.DataKeys.Item(e.Item.ItemIndex)
dblPrice = cDbl((CType( e.Item.Cells(2).Controls(0), Textbox)).Text)
intQuantity = cInt((CType( e.Item.Cells(3).Controls(0), Textbox)).Text)
strSQL = "UPDATE socks SET price = " & dblPrice.ToString & _
", quantity = " & intQuantity.ToString & _
" WHERE id = " & intID.ToString
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data" &
_ "Source=C:Inetpubdbinventory.mdb"
Dim dbConnection As OleDbConnection = New OleDbConnection(strConn)
dbConnection.Open()
'dbConnection.Execute( strSQL )
updCMD = new OleDbCommand( strSQL, dbConnection )
updCMD.ExecuteNonQuery
dbConnection.Close()
dgSocks.EditItemIndex = -1
doBinding
end sub
[/code]
So by now, you should be able to read through that like the morning paper. Essentially we’re just picking up the modified values, throwing them into a SQL UPDATE statement, and executing it. Then we set the edit item index back to -1 (exit edit mode), and re-bind the data to pick up all the fresh values.
What may have stumped you (though it may not have) is the part where we get the textbox values. Really, all I did there was cast the Control(0) of the cell to a textbox. This is just a quick way of obtaining the text value, which could then be cast to whatever data type necessary.
And that’s all it takes! Are you sold yet?
{mospagebreak title=Conclusion}
I’m really starting to understand the approach Microsoft is taking with the whole ASP.NET platform. If you sit back (please not so far back that you fall off your chair) and stare at your application like on of those 3D pictures, you’ll soon see what takes shape. It resembles a Visual Basic Windows application, does it not?
I mean, we haven’t touched a bit of HTML code, which was always necessary in ASP 3.0. We’re just treating this like any other application, and letting .Net worry about the end result for us. I’m really beginning to enjoy this rapid development idea, I hope you are too! Oh, and just in case you want it, here’s all the code for a fully formatted DataGrid with all the bells and whistles:
[code]
<%@ Page Language="VB" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
sub page_load( source as Object, e as EventArgs )
if not isPostBack then
doBinding
end if
end sub
Sub doBinding( optional sortBy As String="id" )
'=== omitted code use to fill a dataset called dsInventory
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4;
Data Source=C:Inetpubnewsitetestauto1.mdb"
Dim dbConnection As OleDbConnection = New OleDbConnection(strConn)
Dim strSQL As String = "SELECT * FROM socks "
strSQL &= " ORDER BY " & sortBy
Dim dataAdapter As IDbDataAdapter = New OleDbDataAdapter( strSQL, strConn)
Dim dataSet As DataSet = New DataSet
dataAdapter.Fill(dataSet)
'=== databind to DataGrid called dgSocks
dgSocks.DataSource = dataSet
dgSocks.DataBind()
End Sub
sub pager(s as Object, e as DataGridPageChangedEventArgs )
dgSocks.CurrentPageIndex = e.NewPageIndex
doBinding
end sub
Sub reSort( s as Object, e As DataGridSortCommandEventArgs )
doBinding( e.sortExpression )
End Sub
sub editMode( s as Object, e As DataGridCommandEventArgs )
dgSocks.EditItemIndex = e.Item.ItemIndex
doBinding
End sub
sub cancelEdit( s as Object, e As DataGridCommandEventArgs )
dgSocks.EditItemIndex = -1
doBinding
End sub
sub doUpdate( s as Object, e As DataGridCommandEventArgs )
Dim strConn, strSQL as String
dim updCMD as OleDbCommand
Dim intID, intQuantity as Integer
Dim dblPrice as Double
intID = dgSocks.DataKeys.Item(e.Item.ItemIndex)
dblPrice = cDbl((CType( e.Item.Cells(2).Controls(0), Textbox)).Text)
intQuantity = cInt((CType( e.Item.Cells(3).Controls(0), Textbox)).Text)
strSQL = "UPDATE socks SET price = " & dblPrice.ToString & _
", quantity = " & intQuantity.ToString & _
" WHERE id = " & intID.ToString
'response.write( strSQL)
'response.end()
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:Inetpubnewsitetestauto1.mdb"
Dim dbConnection As OleDbConnection = New OleDbConnection(strConn)
dbConnection.Open()
'dbConnection.Execute( strSQL )
updCMD = new OleDbCommand( strSQL, dbConnection )
updCMD.ExecuteNonQuery
dbConnection.Close()
dgSocks.EditItemIndex = -1
doBinding
end sub
</script>
<html>
<head>
</head>
<body>
<form runat="server">
<asp:DataGrid id="dgSocks" runat="server" AutoGenerateColumns="false"
width="400" cellPadding="2" Font-Size="10px"
AllowSorting="true" OnSortCommand="reSort"
AllowPaging="true" PageSize="3" OnPageIndexChanged="pager"
PagerStyle-HorizontalAlign="center" PagerStyle-Mode="NumericPages"
DataKeyField="id" onEditCommand="editMode" onUpdateCommand="doUpdate"
OnCancelCommand="cancelEdit">
<HeaderStyle backcolor="Salmon" font-bold="true" />
<Columns>
<asp:EditCommandColumn EditText="mod" UpdateText="upd" CancelText="cancel"
/>
<asp:BoundColumn HeaderText="Sock Color" DataField="color" readonly="true"
/>
<asp:BoundColumn HeaderText="Price" DataField="price" DataFormatString="{0:c}">
<ItemStyle horizontalalign="right" />
</asp:BoundColumn>
<asp:BoundColumn HeaderText="Quantity" DataField="quantity" />
</Columns>
<AlternatingItemStyle backcolor="#CCCCCC" />
</asp:DataGrid>
</form>
</body>
</html>
[/code]