This tip was submitted to the Visual Studio .NET Info Center tip exchange by member Roger McCook. Let other users know how useful this tip is by rating it below. Got a tip or code of your own you'd like to share? Submit it here!
The idea is that we have a DataGrid that has a list of records in a database table. We want to be able to select a row and call up a detail form that will have the corresponding database detail.
Using Visual Studio .NET, I drag and drop a DataGrid onto my Web form and start tinkering with the properties. This is the way I like to do it but I still have to switch to HTML view and code a few items by hand. Here is some actual code from a recent project I worked on for a large company in Atlanta. The grid is generalized to handle several different reference tables in the database. The detail form that will be called is dependent on which table I'm working with.
<asp:DataGrid id="myDataGrid" style="Z-INDEX: 102; LEFT: 159px; POSITION: absolute; TOP: 116px" runat="server" Border="0" Cellpadding="4" AlternatingItemStyle-BackColor="#EFEFEF" HeaderStyle-BackColor="Black" HeaderStyle-ForeColor="White" HeaderStyle-Font-Bold="True" AllowPaging="True" PagerStyle-Mode="NextPrev" PagerStyle-NextPageText="Next ->" PagerStyle-PrevPageText="<- Previous" PagerStyle-Font-Bold="True" OnPageIndexChanged="myDataGrid_PageChanger" OnItemCommand="myDataGrid_ItemCommand" AutoGenerateColumns="False" Width="501px" BorderStyle="Solid" BorderWidth="1px" CellSpacing="1" DataKeyField="ID"< <AlternatingItemStyle BackColor="#EEF5FA"></AlternatingItemStyle> <HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#6FB1D9"></HeaderStyle> <Columns> <asp:BoundColumn Visible="False" DataField="ID" ReadOnly="True" HeaderText="RowID"> </asp:BoundColumn> <asp:BoundColumn DataField="Description" HeaderText="Description"> </asp:BoundColumn> <asp:ButtonColumn Text="Edit" CommandName="Edit"> </asp:ButtonColumn> <asp:ButtonColumn Text="Delete" CommandName="Delete"> </asp:ButtonColumn> </Columns> <PagerStyle NextPageText="Next ->" Font-Bold="True" PrevPageText="<- Prev"></PagerStyle> </asp:DataGrid<
Most of the code was generated automatically by Visual Studio (VS.NET Enterprise Edition), but I had to switch to HTML view and add a couple of lines myself. The following info was manually entered: This following line (from above) calls a procedure named "myDataGrid_PageChanger" when I want to move forward or backward a page.
These following lines (from above) put an EDIT button and a DELETE button in each row.
<asp:ButtonColumn Text="Edit" CommandName="Edit"></asp:ButtonColumn> <asp:ButtonColumn Text="Delete" CommandName="Delete"></asp:ButtonColumn>
This following line (from above) calls a procedure named "myDataGrid_ItemCommand" when one of the above buttons is clicked.
The following code is entered in the code-behind page in the page_load procedure. This is where I determine which table I'm working with and refresh the DataGrid accordingly.
If Page.IsPostBack = False Then Me.lblPageTitle.Text = Request.QueryString("Table") RefreshGrid() Exit Sub End If
The following procedure is entered in the code-behind file to refresh the DataGrid. Note how the SQL syntax changes depending on which table I'm working with.
Private Sub RefreshGrid() Dim strConnect As String = GetConnectString() Dim strSQL As String Select Case Trim$(lblPageTitle.Text) Case "Action Types" strSQL = "Select ActionType_ID as ID, ActionType_Desc as Description" strSQL = strSQL & " FROM dbv_ActionType ORDER BY ActionType_Desc" Case "Date Types" strSQL = "Select DateType_ID AS ID, DateType_Desc as Description" strSQL = strSQL & " FROM dbv_DateType ORDER BY DateType_Desc" End Select Dim Conn As New SqlConnection(strConnect) Dim AdapterDockets As SqlDataAdapter Dim DataSetDockets As DataSet Conn.Open() AdapterDockets = New SqlDataAdapter(strSQL, Conn) DataSetDockets = New DataSet() AdapterDockets.Fill(DataSetDockets) myDataGrid.DataSource = DataSetDockets myDataGrid.DataBind() End Sub
The following procedure is called when you move forward or backward a page.
Sub myDataGrid_PageChanger(ByVal Source As Object, ByVal E As DataGridPageChangedEventArgs) myDataGrid.CurrentPageIndex = E.NewPageIndex RefreshGrid() End Sub
The following procedure is called when you click one of the command buttons in the DataGrid. Notice how I determine which button was clicked and how I pick out the Row_ID that I want to edit or delete.
Sub myDataGrid_ItemCommand(ByVal s As Object, ByVal e As DataGridCommandEventArgs) If e.CommandName = "Edit" Then Dim itemCell As TableCell = e.Item.Cells(0) Dim ID As Integer = Val(itemCell.Text) EditRecord(ID) End If If e.CommandName = "Delete" Then Dim itemCell As TableCell = e.Item.Cells(0) Dim ID As Integer = Val(itemCell.Text) DeleteRecord(ID) End If End Sub
Finally, there is one more procedure I'll show which is the EditRecord procedure. I'm not showing the DeleteRecord procedure because it is not relevant to this article. This is the code that redirects you to the appropriate detail form. By passing the database row in the call, you can write code in the detail form to fill the form from the database.
Private Sub EditRecord(ByVal ID As Integer) Select Case Trim$(lblPageTitle.Text) Case "Action Types" Response.Redirect("AddActionType.aspx?ActionType_ID=" & ID) Case "Date Types" Response.Redirect("AddDateType.aspx?DateType_ID=" & ID) End Select End Sub
I've demonstrated how we can have a form with a DataGrid control that can be used generically for several database tables. The control will contain an EDIT button in each row. When you click the EDIT button, the program selects the appropriate detail form and opens it (passing in the row_id so that the detail form can be populated from the database.)
If you have any comments, corrections or other ideas, you can e-mail me at RogerMcCook@hotmail.com.
Roger D. McCook
McCook Software, Inc.