Build a Reusable Datagrid to Make Life Easier

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:\inetpub\sites\site.com\www\database\site.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!