ASP.NET Data Controls Part 1: DataGrids
Pages: 1, 2, 3

Let's now write the code to perform all of the various functions that we have indicated in the DataGrid control.

First, we have the Page_Load() event:

   
    Private Sub Page_Load(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) _
                Handles MyBase.Load
        '---sets the initial page size to be 4
        If Session("page_size") Is Nothing Then _
           Session("page_size") = 4

        '---if loading for the first time, 
        '---set the page size and also the page index
        If Not IsPostBack Then
            txtPageSize.Text = Session("page_size")
            DataGrid1.PageSize = txtPageSize.Text
            DataGrid1.CurrentPageIndex = Session("curr_page")
            Load_Data()
        End If

        '---displays the selected employee information---
        If Request.QueryString("emp_id") <> "" Then
            Dim ds As New DataSet

            SqlDataAdapter1.SelectCommand.CommandText = _
            "SELECT * FROM Employee WHERE emp_id='" & _
            Request.QueryString("emp_id") & "' " & Session("sortBy")

            SqlDataAdapter1.Fill(ds, "employee_record")
            DataGrid2.DataSource = ds
            DataGrid2.DataBind()
        End If
    End Sub
   

Here is the Load_Data() method, which loads the data from the database and binds it to the DataGrid control:

   
    Private Sub Load_Data()
        Dim ds As New DataSet
        SqlDataAdapter1.SelectCommand.CommandText = _
            "SELECT * FROM Employee " & Session("sortby")
        SqlDataAdapter1.Fill(ds, "employee")
        DataGrid1.DataSource = ds
        DataGrid1.DataBind()
    End Sub
   

The code for the Hide Names button:

   
    Private Sub hidedetails_Click(ByVal sender As System.Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles hidedetails.Click
        '---hides the second, third and fourth column---
        DataGrid1.Columns(1).Visible = False
        DataGrid1.Columns(2).Visible = False
        DataGrid1.Columns(3).Visible = False
    End Sub
   

The code for the Show Names button:

   
    Private Sub showdetails_Click(ByVal sender As System.Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles showdetails.Click
        '---displays the second, third and fourth column---
        DataGrid1.Columns(1).Visible = True
        DataGrid1.Columns(2).Visible = True
        DataGrid1.Columns(3).Visible = True
    End Sub
   

The code for the edit() method:

   
    Public Sub edit(ByVal s As Object, _
                    ByVal e As DataGridCommandEventArgs)
        '---sets the row to edit---
        DataGrid1.EditItemIndex = e.Item.ItemIndex
        Load_Data()
    End Sub
   

The code for the cancel() method:

   
    Public Sub cancel(ByVal s As Object, _
                      ByVal e As DataGridCommandEventArgs)
        '---deselects the row to edit---
        DataGrid1.EditItemIndex = -1
        Load_Data()
    End Sub
   

The code for the update() method:

   
    Public Sub update(ByVal s As Object, _
                      ByVal e As DataGridCommandEventArgs)
        Dim tbox As TextBox
        Dim fname, lname, emp_id As String

        '---retrieves the data from the row to be updated---
        tbox = e.Item.Cells(2).Controls(0)
        fname = tbox.Text
        tbox = e.Item.Cells(3).Controls(0)
        lname = tbox.Text

        '---retrieves the key for the row---
        emp_id = DataGrid1.DataKeys(e.Item.ItemIndex)

        '---updates the database---
        Dim sql As String = "UPDATE Employee SET fname='" & _
                            fname & "' , lname='" & lname & _
                            "' WHERE emp_id='" & emp_id & "'"
        Dim conn As New SqlConnection("server=localhost; " & _
                        "user id =sa; password=;database=pubs")
        Dim comm As New SqlCommand(sql, conn)

        conn.Open()
        comm.ExecuteNonQuery()
        conn.Close()

        DataGrid1.EditItemIndex = -1
        Load_Data()
    End Sub
   

The code for the sort() method:

   
    Public Sub sort(ByVal s As Object, _
                    ByVal e As DataGridSortCommandEventArgs)
        ' session object is used to support paging
        Session("sortby") = e.SortExpression          
        Load_Data()
    End Sub
   

The code for the delete_record() method:

   
    Public Sub delete_record(ByVal s As Object, _
                             ByVal e As DataGridCommandEventArgs)
        '---retrieves the key for the row---
        Response.Write(DataGrid1.DataKeys(e.Item.ItemIndex))
        '---codes to delete row here----
        '
        '-------------------------------
    End Sub
   

The code for the changepage() method:

   
    Public Sub changepage(ByVal s As Object, _
                          ByVal e As DataGridPageChangedEventArgs)
        '---displays the next page---
        DataGrid1.CurrentPageIndex = e.NewPageIndex
        Session("curr_page") = e.NewPageIndex
        Load_Data()
    End Sub
   

The code for the Change button:

   
    Private Sub changepagesize_Click(ByVal sender As System.Object, _
                                     ByVal e As System.EventArgs) _
                                     Handles changepagesize.Click
        '---changes the size of the page---
        DataGrid1.PageSize = CLng(txtPageSize.Text())
        Session("page_size") = DataGrid1.PageSize
        Load_Data()
    End Sub
   

Run this application and you should now be able to display the records in multiple pages. You can also edit and delete any rows you desire:

Figure 10. Running the example application

Summary

The DataGrid is a very powerful and useful control, if you know how to use it properly. Don't be fooled by its appearance; you still need to write substantial code to make it work the way you want it to work. By if you take some time to trace the code and understand the documentation, it is really worth the effort!

Wei-Meng Lee (Microsoft MVP) http://weimenglee.blogspot.com is a technologist and founder of Developer Learning Solutions http://www.developerlearningsolutions.com, a technology company specializing in hands-on training on the latest Microsoft technologies.

---

Return to ONDotnet.com