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.
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.
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
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
<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
DoItemCancelare, as I am sure you can tell by looking at them, the easiest functions. After all they change where the
EditItemIndexis pointed. The
EditItemIndexis a number value that tells you which item is in edit mode. If you set the
EditItemIndexto -1 then none of the items are in edit mode. As you can see in the example,
ItemIndexand sets the
EditItemIndexequal to it, and all that the
DoItemCancelever does is set the
EditItemIndexequal 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. The
objArgsrefers to the selected row, then
Cellsrefers to the number of the cell counted from left to right starting at 0, and
Controlsrefers to the number of the control, be it a Textbox, Calendar, or any kind of control, inside that cell. Then, using the
Ctypefunction, the values of these are converted into strings and written into a SQL UPDATE statement. Then I wrote the
ExecuteSQLStatementfunction 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 same
Datakeycall from the update function, write an SQL DELETE statement, and send it to the
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!