Build a Reusable Datagrid to Make Life Easier

Are you still toiling with classic ASP for your customer’s Websites? Are you writing 20 or 30 page admin section for customers who just want to edit their News and Frequently Asked Questions pages? Well, put down your sticks because I’m about to hand you a golden Zippo!

If you freelance or run a small design company then you can’t financially justify continuing to use Classic ASP. It was just about 8 months ago that I can recall writing 8 or 10 pages just accomplish an ADD/EDIT/DELETE on a database table. Now I can do it in just one completely reusable page. I’d heard lots and lots about .NET, but I’d been rolling along great with ASP for so long and whenever I looked at it, the change seemed too daunting. I can’t remember what made me break down and get into .NET, but as soon as I saw what could be accomplished I knew that I’d made the right decision. After going through several books, I have built administration portions for 6 client sites, and each has been more functional and easier to use than the one before it.

What I am about to teach you is the ASP.NET datagrid. I’ll assume that you have a basic understanding of ASP.NET and VB.NET.

The DataGrid

With some initial work you can put together an ADD/EDIT/DELETE datagrid that will make your life a whole lot easier. Just imagine, every time your client wants to edit a simple database table like a calendar, “What’s New” section, or Frequently Asked Questions, all you have to do is make a copy of the last datagrid you made and spend 20 minutes making changes. It’s my favorite part of ASP.NET, just because it’s such a time saver. Now you can see what it will do for you.

I think that the best way to demonstrate this is going to be to let you see the evolution of the datagrid that I’m currently using. It started out with just EDIT/DELETE and now it takes care of all of my simple, yet time consuming tasks.

Right out of the box, the datagrid does almost everything you require. Here’s what you need to get started.

Sub Page_Load(sender as object, e as eventargs) 
   If Not Page.IsPostBack Then
       BindDataGrid()
   End If
End Sub
Sub BindDataGrid()
   Dim objConn as New OleDbConnection("Provider=Microsoft
   .Jet.OLEDB.4.0; Data Source=c:inetpubsitessite.com
   wwwdatabasesite.mdb")
   objConn.Open()

   Dim ds as Dataset = New DataSet()
   Dim objAdapter as New OleDbDataAdapter("SELECT * FROM  
   News ORDER BY NewsDate DESC;", objConn)
   
   objAdapter.Fill(ds,"News")

   EditNews.DataSource = ds.Tables("News").DefaultView
   EditNews.DataBind()

   objConn.Close()
End Sub

In this example I wrote a BindDataGrid() function that will pull the data from the database and write it into the datagrid. You may also notice that I’m using OleDb. You can substitute any type of connection you want, just make sure to import the proper namespaces. I also wrote the Page_Load function that simply runs the BindDataGrid() function the first time the page loads.

Defining the Datagrid

Next up I’ll show you the beginnings of the datagrid. This is the part where you define what information will be in which column, and decide on the look and feel of the interface.

<form runat="server"> 
       <asp:datagrid id="EditNews" runat="server" GridLines="Horizontal"  
Cellspacing="0" CellPadding="5" EditItemStyle-BackColor="#333366"
EditItemStyle-ForeColor="white" DataKeyField="NewsID"  
OnEditCommand="DoItemEdit" OnUpdateCommand="DoItemUpdate"  
OnCancelCommand="DoItemCancel" OnDeleteCommand="DoItemDelete"  
AutoGenerateColumns="false" AlternatingItemStyle-BackColor="#CCCCCC"  
BorderColor="black">
           <columns>
               <asp:boundcolumn HeaderText="Date"  
               DataField="NewsDate" ReadOnly="true"  
               HeaderStyle-BackColor="#333366"  
               HeaderStyle-ForeColor="#FFFFFF" />
               <asp:templatecolumn HeaderText="News Title"  
               ItemStyle-Width="200"    HeaderStyle-BackColor="#333366"  
               HeaderStyle-ForeColor="#FFFFFF">
                   <ItemTemplate>
                       <asp:label Text='<%# Container.DataItem
                       ("NewsTitle")%>' runat="server"></asp:label>
                   </ItemTemplate>
                   <EditItemTemplate>
                       <asp:textbox ID="NewsTitle"  
                       TextMode="MultiLine" Rows="3" size="60"  
                       runat="server" Text='<%#  
                       Container.DataItem("NewsTitle")%>' />
                   </EditItemTemplate>
               </asp:templatecolumn>
               <asp:templatecolumn HeaderText="NewsBody"  
               ItemStyle-Width="400"  
               HeaderStyle-BackColor="#333366"  
               HeaderStyle-ForeColor="#FFFFFF">
                   <ItemTemplate>
                       <asp:label Text='<%# Container.DataItem
                       ("NewsBody")%>'
                        runat="server"></asp:label>
                   </ItemTemplate>
                   <EditItemTemplate>
                       <asp:textbox ID="NewsBody"  
                       Columns="50" TextMode="MultiLine"  
                       Rows="8" runat="server" Text='<%#  
                       Container.DataItem("NewsBody")%>' />
                   </EditItemTemplate>
               </asp:templatecolumn>
               <asp:templatecolumn HeaderText="News Author"  
               ItemStyle-Width="100" HeaderStyle-BackColor="#333366"
               HeaderStyle-ForeColor="#FFFFFF">
                   <ItemTemplate>
                       <asp:label Text='<%# Container.DataItem
                       ("NewsWrittenBy")%>' runat="server"></asp:label>
                   </ItemTemplate>
                   <EditItemTemplate>
                       <asp:textbox ID="NewsAuthor" width="100"  
                       runat="server" Text='<%# Container.DataItem
                       ("NewsWrittenBy")%>' />
                   </EditItemTemplate>
               </asp:templatecolumn>
               <asp:templatecolumn HeaderStyle-BackColor="#333366"
                HeaderStyle-ForeColor="#FFFFFF"  
                ItemStyle-Width="75">
                   <ItemTemplate>
                       <asp:button CommandName="Edit"  
                       Text="Edit" runat="server" />
                   </ItemTemplate>
                   <EditItemTemplate>
                       <asp:button CommandName="Update"  
                       Text="Update" runat="server" Width="75" />
                       <asp:button CommandName="Delete"  
                       Text="Delete" runat="server" Width="75" />
                       <asp:button CommandName="Cancel"
                       Text="Cancel" runat="server" Width="75" />
                   </EditItemTemplate>
               </asp:templatecolumn>
           </columns>
       </asp:datagrid>
   </form>

The Attributes of the Code

Now this looks like a lot of HTML, but the thing to remember is that a good chunk of this will never change. The part that does change is in fact really easy to manipulate. Let’s start with the <asp:datagrid> tag, and look at some if its attributes. First off, you must enclose the tag inside a <form> tag with a runat="server", otherwise none of your updating and deleting features will work. Next you have the colors, padding, and borders. You can figure those out. The main thing that we want to hit on is the functionality attributes.

The DataKeyField is something that will have to be changed for every table you use. It carries on the unique identifier of each of your records, and will allow you to update and delete these records. The next big things are the “On Commands”. If you look at the example you can see an OnUpdateCommand, OnCancelCommand, OnEditCommand, and an OnDeleteCommand. These tell the datagrid which function to run when someone presses that button. These functions will be the next example. Before that, however, I did want to mention the column formatting and let you in on how it works.

There is a template for each column, even the one that contains the buttons. Each template contains the <ItemTemplate> and the <EditItemTemplate>. The <ItemTemplate> will contain the dynamic content from the database in a read only form. In the <EditItemTemplate> you must write everything to be displayed in <asp:textbox>s. This will be what people see whenever they press the edit button on a certain row. You’ll use the Container.DataItem("FieldName") to display your dynamic content on both sides: read only and edit. Now let’s see what these functions look like.

Sub DoItemEdit(objSource as Object, objArgs As DataGridCommandEventArgs)  
   EditNews.EditItemIndex = objArgs.Item.ItemIndex  
   BindDataGrid()  
End Sub  
Sub DoItemCancel(objSource as Object, objArgs As DataGridCommandEventArgs)  
   EditNews.EditItemIndex = -1  
   BindDataGrid()  
End Sub  
Sub DoItemUpdate(objSource as Object, objArgs As DataGridCommandEventArgs)  
   Dim strTitle, strBody,strWrittenBy as String  
   Dim intID as String  
   strTitle = Ctype(objArgs.Item.Cells(1).Controls(1), Textbox).text  
   strBody = Ctype(objArgs.Item.Cells(2).Controls(1), Textbox).text  
   strWrittenBy = Ctype(objArgs.Item.Cells(3).Controls(1), TextBox).text  
   'strDate =  
   intID = EditNews.DataKeys(objArgs.Item.ItemIndex)  
 
   Dim strSQL as String  
   strSQL = "UPDATE News SET NewsTitle='" & strTitle & _  
            "', NewsBody='" & strBody & _  
            "', NewsWrittenBy='" & strWrittenBy & _  
            "' WHERE NewsID=" & intID & ";"  
 
   ExecuteSQLStatement(strSQL)  
 
   EditNews.EditItemIndex = -1  
   BindDataGrid()  
End Sub  
Sub DoItemDelete(objSource as Object, objArgs as DataGridCommandEventArgs)  
   Dim intID as String  
   Dim strSQL as String  
   intID = EditNews.DataKeys(objArgs.Item.ItemIndex)  
 
   strSQL = "DELETE FROM News WHERE NewsID=" & intID & ";"  
 
   ExecuteSQLStatement(strSQL)  
 
   EditNews.EditItemIndex = -1  
   BindDataGrid()  
End Sub  
Sub ExecuteSQLStatement(strSQL as String)  
   Dim objConn as New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;  
   Data Source=c:inetpubsitessite.comwwwdatabasesite.mdb")  
   objConn.Open()  
 
   Dim objCommand as New OleDbCommand(strSQL, objConn)  
 
   objCommand.ExecuteNonQuery()  
 
   objConn.Close()  
End Sub
DoItemEdit and DoItemCancel are, as I am sure you can tell by looking at them, the easiest functions.  After all they change where the EditItemIndex is pointed.  The EditItemIndex is a number value that tells you which item is in edit mode.  If you set the EditItemIndex to -1 then none of the items are in edit mode.  As you can see in the example, DoItemEdit takes the ItemIndex and sets the EditItemIndex equal to it, and all that the DoItemCancel ever does is set the EditItemIndex equal to -1. 

Now, for the hardest thing in this whole process (which isn't really that hard). The DoItemUpdate function gave me headaches when I was first starting. I have come to understand that it is very hard to refer to an object that is inside the datagrid. This is accomplished by referring to its cell and control number. Here's how it works. The objArgs refers to the selected row, then Cells refers to the number of the cell counted from left to right starting at 0, and Controls refers to the number of the control, be it a Textbox, Calendar, or any kind of control, inside that cell. Then, using the Ctype function, the values of these are converted into strings and written into a SQL UPDATE statement. Then I wrote the ExecuteSQLStatement function to take the string and run it at the database.

Next is the DoItemDelete function, which is the last of our base functions. All that is required here is for you to use the same Datakey call from the update function, write an SQL DELETE statement, and send it to the ExecuteSQLStatement function.

The only thing that I have left for you to figure out, is how to accomplish Add, after all if I told you everything, you wouldn't learn anything! I will point you in the right direction, though. You must write a function that adds a blank row to the database, and then makes the EditItemIndex that row.

Now, I know that what we just did might seem like a lot of work. That's because it is a lot, but the greatest thing about it is that it is a wonderful base for everything that you will ever need to do as far as editing in table or a view. My experience is that the time it takes to write this initially is more than compensated for by the hours you save every time you have to write an administration section for a Website. If you think about it, all that needs to change are the connection strings, SQL statements, and template columns. I can whip out a datagrid in 20 minutes that will edit a simple table, and be finished hours under the quoted time. I think this is one tool that a successful Web programmer cannot do without!

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

No Reader comments

Comments on this post are closed.