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 SubIn 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 SubDoItemEditandDoItemCancelare, as I am sure you can tell by looking at them, the easiest functions. After all they change where theEditItemIndexis pointed. TheEditItemIndexis a number value that tells you which item is in edit mode. If you set theEditItemIndexto -1 then none of the items are in edit mode. As you can see in the example,DoItemEdittakes theItemIndexand sets theEditItemIndexequal to it, and all that theDoItemCancelever does is set theEditItemIndexequal to -1.Now, for the hardest thing in this whole process (which isn't really that hard). The
DoItemUpdatefunction 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. TheobjArgsrefers to the selected row, thenCellsrefers to the number of the cell counted from left to right starting at 0, andControlsrefers to the number of the control, be it a Textbox, Calendar, or any kind of control, inside that cell. Then, using theCtypefunction, the values of these are converted into strings and written into a SQL UPDATE statement. Then I wrote theExecuteSQLStatementfunction to take the string and run it at the database.Next is the
DoItemDeletefunction, which is the last of our base functions. All that is required here is for you to use the sameDatakeycall from the update function, write an SQL DELETE statement, and send it to theExecuteSQLStatementfunction.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
EditItemIndexthat 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!
