AddThis Social Bookmark Button

Print

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:

Running the example application
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


  • Error when run the code
    2005-09-14 02:47:24  Saheb [View]

    Hi KAMAL,
    Could you please explain me how you removed the error?I was also trying to use the code but it showed error as you had explained-Also please give solutions to the further problems which you encountered while using this code-hoping to hear from you soon.
    Thanks
  • Error when run the code
    2005-06-27 00:32:02  KAMAL [View]

    OK I got the solution
    • no Error when run the code but cant update grid
      2005-09-01 11:05:39  khushi_m28 [View]

      i m using the code as given , but the problem is
      no error is there ,still i cant update my table in database from grid.
      i dont understand wt to do?
  • asp.net
    2005-04-06 02:18:03  wishi [View]


    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'Put user code to initialize the page here
    If (Not IsPostBack) Then
    ddl_databind()

    End If
    End Sub

    Public Sub ddl_databind()
    Dim sqlcon As New SqlConnection("server=Shikhaws;user id=sa;password=sa;database=northwind")
    Dim sqlcmd As New SqlCommand("SELECT FirstName,EmployeeId FROM Employees", sqlcon)
    sqlcon.Open()
    DropDownList1.DataSource = sqlcmd.ExecuteReader()
    DropDownList1.DataTextField = "FirstName"
    DropDownList1.DataValueField = "EmployeeId"
    DropDownList1.DataBind()
    sqlcon.Close()
    End Sub
    Public Sub DropDownList1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
    Dim sb As New StringBuilder
    Dim ddl As New DropDownList
    sb.Append("DropDownList1.selecteditem value=")
    sb.Append(DropDownList1.SelectedItem.Value)
    sb.Append("
    ")
    sb.Append("dropdownlist.selecteditem=")
    sb.Append(ddl.SelectedItem)
    lblmessage.Text = sb.ToString()
    Dim sqlcon As New SqlConnection("server=localhost;uid=sa;pwd=;database=northwind")
    Dim ds As New DataSet
    Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT Title FROM Employees where EmployeeId= " & DropDownList1.SelectedItem.Value, sqlcon)
    da.Fill(ds, "EmployeeId")
    TextBox1.Text = ds.Tables(0).Rows(0)("Title")

    End Sub
    End Class
  • Works Gr8 Gud article - Infact Getting Small error
    2005-03-24 22:50:59  MulapalliPrasad [View]

    Every thing works fine , But when i click on update i am getting error saying DATA TYPE MISMATCH.... please help me in this regard.

    I've checked the data types at the back end .. they are absolutely fine as per my vision.

    thanks in advance.
    Prasad

    Here goes my code.

    Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='E:\lakshmi\jobs\jobs.mdb';"

    Dim MySQL As String = "UPDATE usermaster SET username='" & _
    username & "' , phone='" & CInt(phone) & _
    "' WHERE userid='" & userid & "'"

    Dim MyConn As New OleDbConnection(strConn)

    Dim Cmd As New OleDbCommand(MySQL, MyConn)
    Dim Usercount As Integer
    MyConn.Open()

    Cmd.ExecuteNonQuery()
    MyConn.Close()

    DataGrid1.EditItemIndex = -1
    Load_Data()
    End Sub
  • about print datagrid
    2005-03-23 01:44:31  sun9582 [View]

    Respected Sir,
    How to print the datagrid in the ASP.NET with backhand of Oracle.
    I want to get the print of the datagrid data

    so, give reply me as soon as possibel on this e-mail, nirav_bodiwala@hotmail.com

    thank you,
    • about print datagrid
      2007-01-31 04:59:06  shilpiJain [View]

      I want to get the print of the datagrid data
  • Remove row dialog
    2004-12-31 01:28:15  Flax [View]

    I was wondering if this example would be a correct place to handle a dialog screen.


    Most of the time you want to ask the user if he / she is sure about removing the row and i had hard time finding a good working example for this
  • Code behind
    2004-08-11 02:45:25  stuart_taylor [View]

    Hi, love the article, i'm new to this whole .NET thing, and am getting to grips with the C# flavour, i notice that the code behind is in VB, is it easy to convert?

    any help would be appreciated.
  • To all who say Rubbish
    2004-01-16 06:44:32  anonymous2 [View]

    What the hell do you want? The author to write all of your custom code for you. This is a very good article, that gives good examples for the code needed for custom datagrids!
  • Datagrids example
    2003-11-10 08:31:21  anonymous2 [View]


    Exactly what I needed to update and delete records from a Datagrid! Thank you very much!

    Paul McP.
  • worked great!
    2003-10-31 09:32:14  anonymous2 [View]

    Very straighforward. Thanks!
  • Very Nice and Tidy...
    2003-08-28 06:34:08  anonymous2 [View]

    this is great.
    it helps me lot.

    thanks.
  • dats gud
    2003-08-21 08:09:38  anonymous2 [View]

    hi! you have explained it very well and all the things are good and most of the things are working but i need u know that if i click on the grid on the rows then it should give me the field name of the particular cell how can i do that. if you can manage to explain this then i can be very helpful. Ta
  • ASP.NET Data Controls Part 1: DataGrids -
    2003-08-18 21:59:43  anonymous2 [View]

    very good topic. Thanks for your information provided on the web.
  • ASP.NET Data Controls Part 1: DataGrids
    2003-06-29 13:34:20  anonymous2 [View]

    In Wei-Meng Lee's article above, I can find no way to click on the page number to get to the next page. In my copy, it only shows page number 1 and no ability to click on it!

    Could you please tell me what's missing from the article?

    Thanks,

    Larry
    rosenzl@optonline.net
  • source code?
    2003-06-12 11:53:25  anonymous2 [View]

    can we download the full source code for this? i am curious as to how the ...aspx?emp_id={0} is called. does it go straight to a new page? i don't get it

    please reply to
    webmaster@kendalltech.com
  • Rubbish!
    2003-04-25 08:10:25  anonymous2 [View]

    The thing doesn't work, is poorly and is poorly explained.
    • Rubbish!
      2003-07-25 20:56:13  anonymous2 [View]

      U so good u come and give yur source code and explain it lah...imbecile
    • To the person who wrote Rubbish!
      2003-05-05 23:08:17  anonymous2 [View]

      I hate people like you saying rubbish to other people's hard work.

      If you aren't smart enough to understand, admit it.
      • To the person who wrote Rubbish!
        2003-12-18 08:34:29  anonymous2 [View]

        I think hate is a harsh word. I agree with the guy who wrote "Rubbish" because that explains this article perfectly. It doesn't work and it is poorly explained.






      • To the person who wrote Rubbish!
        2003-12-18 08:34:24  anonymous2 [View]

        I think hate is a harsh word. I agree with the guy who wrote "Rubbish" because that explains this article perfectly. It doesn't work and it is poorly explained.
      • To the person who wrote Rubbish!
        2003-12-18 08:34:21  anonymous2 [View]

        I think hate is a harsh word. I agree with the guy who wrote "Rubbish" because that explains this article perfectly. It doesn't work and it is poorly explained.
      • To the person who wrote Rubbish!
        2003-12-18 08:34:18  anonymous2 [View]

        I think hate is a harsh word. I agree with the guy who wrote "Rubbish" because that explains this article perfectly. It doesn't work and it is poorly explained.
  • help
    2003-03-21 01:26:05  enigma5 [View]


    can you guide me how can i specify more than one fields in 'DataNavigateUrlField'..

    for eg:
    DataNavigateUrlField="Order ID" DataNavigateUrlFormatString="WebForm2.aspx?Order ID={0}&ItemID={1}"

    how can i specify this itemId in DataNavigateUrlField with Order ID

    as i m sending as anonymous please reply me on nausheen.ramzan@eintelligencesoft.com

    thnx
    thnx
    • help
      2003-10-09 07:41:01  anonymous2 [View]

      You will need to use a script in the HTML coding like NavigateUrl='<%# "WebForm2.aspx?OrderID=" + DataBinder.Eval(Container, "DataItem.OrderID") + "&ItemID=" + DataBinder.Eval(Container, "DataItem.ItemID")%>