ASP.NET GridView control demo (VBASPNETGridView)


ASP.NET
Data Binding, GridView
Web
en-US
6/11/2012
View this sample online

ASP.NET GridView control demo (VBASPNETGridView)

Introduction

This VBASPNETGridView project describes how to populate ASP.NET GridView control and how to implement Insert, Edit, Update, Delete, Paging and Sorting functions in ASP.NET GridView control. We have received many posts in forums about this popular web control, so this sample provides a complete sample for showing how to implement these basic functions of this control. The sample demonstrates data source from both database and memory.

Building the Sample

For this sample to work, you must install the SqlServer 2008 R2 Express. This sample contains a SqlServer database file, if you do not install SqlServer, The DataInMemory.aspx page can also works fine. More information about SqlServer 2008 R2 Express and download links can be found here:

•         SqlServer 2008 R2 details

•         Download SqlServer 2008 R2 Express

Running the Sample

Please follow these demonstration steps below.

Step 1: Open the VBASPNETGridView.sln. Expand the VBASPNETGridView web application and press Ctrl + F5 to show the DataFromDatabase.aspx.

Step 2: We will see a GirdView control on the page, you can add, edit, delete the columns of the GridView control, the data is come from App_Data/GridView.mdf file, and the GridView's status is stored in ViewState for persisting data across postbacks.

 

Step 3: The GridView the page size is 15, you need insert 16 Persons in this GridView to see the next page. Please click the title of the GridView to sort the result by PersonID, LastName or FirstName properties.

Step 4: Please press Ctrl+F5 to show DataInMemory.aspx page, the test steps just like DataFromDataBase.aspx.

Step 5: Validation finished.

Using the Code

Code Logical:

Step 1. Create a VB "ASP.NET Empty Web Application" in Visual Studio 2010 or Visual Web Developer 2010. Name it as "VBASPNETGridView ". The project includes two web form pages for demonstrating two ways to bind data source with the GridView, name them as "DataFromDataBase.aspx", "DataInMemory.aspx".

Step 2. Before we start to write code, we need install SqlServer 2008 R2 Express and create a database file as the data source of GridView control. Add an Asp.net folder "App_Data" and create a Sql Server Database,"GridView.mdf". Add "Person" table with three fields "PersonID","FirstName","LastName", PersonID is the primary key of the table, and you can insert some default values in Person table.

Step 3. Drag and drop a GridView control, two LinkButton controls, two TextBox controls and a Panel control into DataFromDataBase.aspx page. The GridView is used to display, edit and delete the data of database file, the TextBox and LinkButton are used to insert new items to the data table. In the first step, check your controls and rename them and set some basic properties of the GridView, such as GridView's templates and events.

The following Html code is showing the GridView's necessary events (onpageindexchanging, onrowcancelingedit, onrowdatabound, etc), GridView's TemplateField and other controls:            

HTML
Edit|Remove 
<asp:GridView ID="gvPerson" runat="server" AutoGenerateColumns="False" BackColor="White" 
BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4" 
    onpageindexchanging="gvPerson_PageIndexChanging" 
    onrowcancelingedit="gvPerson_RowCancelingEdit" 
    onrowdatabound="gvPerson_RowDataBound" onrowdeleting="gvPerson_RowDeleting" 
    onrowediting="gvPerson_RowEditing" onrowupdating="gvPerson_RowUpdating" 
    onsorting="gvPerson_Sorting">
<RowStyle BackColor="White" ForeColor="#003399" />
    <Columns>
        <asp:CommandField ShowEditButton="True" />
        <asp:CommandField ShowDeleteButton="True" />
        <asp:BoundField DataField="PersonID" HeaderText="PersonID" ReadOnly="True" 
            SortExpression="PersonID" />
        <asp:TemplateField HeaderText="LastName" SortExpression="LastName">
            <EditItemTemplate>
                <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("LastName") %>'></asp:TextBox>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="Label1" runat="server" Text='<%# Bind("LastName") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="FirstName" SortExpression="FirstName">
            <EditItemTemplate>
                <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("FirstName") %>'></asp:TextBox>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="Label2" runat="server" Text='<%# Bind("FirstName") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
    <FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
    <PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
    <SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
    <HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
</asp:GridView>
    


<asp:LinkButton ID="lbtnAdd" runat="server" onclick="lbtnAdd_Click">AddNew</asp:LinkButton>




<asp:Panel ID="pnlAdd" runat="server" Visible="False">
    Last name:
    <asp:TextBox ID="tbLastName" runat="server"></asp:TextBox>
    

    

    First name:
    <asp:TextBox ID="tbFirstName" runat="server"></asp:TextBox>
    

    

    <asp:LinkButton ID="lbtnSubmit" runat="server" onclick="lbtnSubmit_Click">Submit</asp:LinkButton>
       
    <asp:LinkButton ID="lbtnCancel" runat="server" onclick="lbtnCancel_Click">Cancel</asp:LinkButton>
    
</asp:Panel>
 

Step 4. Copy the Page_Load and BindGridView methods of the sample and paste them to your DataFromDataBase.aspx.vb file, and navigator to the Property panel and switch to Event. Double click on the following event and generate the Event Handlers, after that, fill the generated methods with the sample code.

•         RowDataBound Event

•         PageIndexChanging Event

•         RowEditing Event

•         RowCancelingEdit Event

•         RowUpdating Event

•         RowDeleting Event

•         Sorting Event

The following code is used to implement the basic functions of the GridView control.

VB
Edit|Remove 
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    ' The Page is accessed for the first time.
    If Not IsPostBack Then
        ' Enable the GridView paging option and 
        ' specify the page size.
        gvPerson.AllowPaging = True
        gvPerson.PageSize = 15


        ' Enable the GridView sorting option.
        gvPerson.AllowSorting = True


        ' Initialize the sorting expression.
        ViewState("SortExpression") = "PersonID ASC"


        ' Populate the GridView.
        BindGridView()
    End If


End Sub
Private Sub BindGridView()
    ' Get the connection string from Web.config. 
    ' When we use Using statement, 
    ' we don't need to explicitly dispose the object in the code, 
    ' the using statement takes care of it.
    Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("SQLServer2005DBConnectionString").ToString())
        ' Create a DataSet object.
        Dim dsPerson As New DataSet()


        ' Create a SELECT query.
        Dim strSelectCmd As String = "SELECT PersonID,LastName,FirstName FROM Person"


        ' Create a SqlDataAdapter object
        ' SqlDataAdapter represents a set of data commands and a 
        ' database connection that are used to fill the DataSet and 
        ' update a SQL Server database. 
        Dim da As New SqlDataAdapter(strSelectCmd, conn)


        ' Open the connection
        conn.Open()


        ' Fill the DataTable named "Person" in DataSet with the rows
        ' returned by the query.new n
        da.Fill(dsPerson, "Person")


        ' Get the DataView from Person DataTable.
        Dim dvPerson As DataView = dsPerson.Tables("Person").DefaultView


        ' Set the sort column and sort order.
        dvPerson.Sort = ViewState("SortExpression").ToString()


        ' Bind the GridView control.
        gvPerson.DataSource = dvPerson
        gvPerson.DataBind()
    End Using
End Sub


' GridView.RowDataBound Event
Protected Sub gvPerson_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
    ' Make sure the current GridViewRow is a data row.
    If e.Row.RowType = DataControlRowType.DataRow Then
        ' Make sure the current GridViewRow is either 
        ' in the normal state or an alternate row.
        If e.Row.RowState = DataControlRowState.Normal OrElse e.Row.RowState = DataControlRowState.Alternate Then
            ' Add client-side confirmation when deleting.
            DirectCast(e.Row.Cells(1).Controls(0), LinkButton).Attributes("onclick") = "if(!confirm('Are you certain you want to delete this person ?')) return false;"
        End If
    End If
End Sub


' GridView.PageIndexChanging Event
Protected Sub gvPerson_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
    ' Set the index of the new display page. 
    gvPerson.PageIndex = e.NewPageIndex


    ' Rebind the GridView control to 
    ' show data in the new page.
    BindGridView()
End Sub


' GridView.RowEditing Event
Protected Sub gvPerson_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
    ' Make the GridView control into edit mode 
    ' for the selected row. 
    gvPerson.EditIndex = e.NewEditIndex


    ' Rebind the GridView control to show data in edit mode.
    BindGridView()


    ' Hide the Add button.
    lbtnAdd.Visible = False
End Sub


' GridView.RowCancelingEdit Event
Protected Sub gvPerson_RowCancelingEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)
    ' Exit edit mode.
    gvPerson.EditIndex = -1


    ' Rebind the GridView control to show data in view mode.
    BindGridView()


    ' Show the Add button.
    lbtnAdd.Visible = True
End Sub


' GridView.RowUpdating Event
Protected Sub gvPerson_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
    Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("SQLServer2005DBConnectionString").ToString())
        ' Create a command object.
        Dim cmd As New SqlCommand()


        ' Assign the connection to the command.
        cmd.Connection = conn


        ' Set the command text
        ' SQL statement or the name of the stored procedure 
        cmd.CommandText = "UPDATE Person SET LastName = @LastName, FirstName = @FirstName WHERE PersonID = @PersonID"


        ' Set the command type
        ' CommandType.Text for ordinary SQL statements; 
        ' CommandType.StoredProcedure for stored procedures.
        cmd.CommandType = CommandType.Text


        ' Get the PersonID of the selected row.
        Dim strPersonID As String = gvPerson.Rows(e.RowIndex).Cells(2).Text
        Dim strLastName As String = DirectCast(gvPerson.Rows(e.RowIndex).FindControl("TextBox1"), TextBox).Text
        Dim strFirstName As String = DirectCast(gvPerson.Rows(e.RowIndex).FindControl("TextBox2"), TextBox).Text


        ' Append the parameters.
        cmd.Parameters.Add("@PersonID", SqlDbType.Int).Value = strPersonID
        cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = strLastName
        cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = strFirstName


        ' Open the connection.
        conn.Open()


        ' Execute the command.
        cmd.ExecuteNonQuery()
    End Using


    ' Exit edit mode.
    gvPerson.EditIndex = -1


    ' Rebind the GridView control to show data after updating.
    BindGridView()


    ' Show the Add button.
    lbtnAdd.Visible = True
End Sub


' GridView.RowDeleting Event
Protected Sub gvPerson_RowDeleting(ByVal sender As Object, ByVal e As GridViewDeleteEventArgs)
    Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("SQLServer2005DBConnectionString").ToString())
        ' Create a command object.
        Dim cmd As New SqlCommand()


        ' Assign the connection to the command.
        cmd.Connection = conn


        ' Set the command text
        ' SQL statement or the name of the stored procedure 
        cmd.CommandText = "DELETE FROM Person WHERE PersonID = @PersonID"


        ' Set the command type
        ' CommandType.Text for ordinary SQL statements; 
        ' CommandType.StoredProcedure for stored procedures.
        cmd.CommandType = CommandType.Text


        ' Get the PersonID of the selected row.
        Dim strPersonID As String = gvPerson.Rows(e.RowIndex).Cells(2).Text


        ' Append the parameter.
        cmd.Parameters.Add("@PersonID", SqlDbType.Int).Value = strPersonID


        ' Open the connection.
        conn.Open()


        ' Execute the command.
        cmd.ExecuteNonQuery()
    End Using


    ' Rebind the GridView control to show data after deleting.
    BindGridView()
End Sub


' GridView.Sorting Event
Protected Sub gvPerson_Sorting(ByVal sender As Object, ByVal e As GridViewSortEventArgs)
    Dim strSortExpression As String() = ViewState("SortExpression").ToString().Split(" "c)


    ' If the sorting column is the same as the previous one, 
    ' then change the sort order.
    If strSortExpression(0) = e.SortExpression Then
        If strSortExpression(1) = "ASC" Then
            ViewState("SortExpression") = Convert.ToString(e.SortExpression) & " " & "DESC"
        Else
            ViewState("SortExpression") = Convert.ToString(e.SortExpression) & " " & "ASC"
        End If
    Else
        ' If sorting column is another column,  
        ' then specify the sort order to "Ascending".
        ViewState("SortExpression") = Convert.ToString(e.SortExpression) & " " & "ASC"
    End If


    ' Rebind the GridView control to show sorted data.
    BindGridView()
End Sub
 

Step 5. Double click on the Click event of LinkButton control to generate the event handler and fill the generated methods with the sample, these two button are used to add new items to the database file and cancel the insert operate.

The following code shows how to insert new items to the database file.

VB
Edit|Remove 
Protected Sub lbtnAdd_Click(ByVal sender As Object, ByVal e As EventArgs)
    ' Hide the Add button and showing Add panel.
    lbtnAdd.Visible = False
    pnlAdd.Visible = True
End Sub


Protected Sub lbtnSubmit_Click(ByVal sender As Object, ByVal e As EventArgs)
    Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("SQLServer2005DBConnectionString").ToString())
        ' Create a command object.
        Dim cmd As New SqlCommand()


        ' Assign the connection to the command.
        cmd.Connection = conn


        ' Set the command text
        ' SQL statement or the name of the stored procedure 
        cmd.CommandText = "INSERT INTO Person ( LastName, FirstName ) VALUES ( @LastName, @FirstName )"


        ' Set the command type
        ' CommandType.Text for ordinary SQL statements; 
        ' CommandType.StoredProcedure for stored procedures.
        cmd.CommandType = CommandType.Text


        ' Append the parameters.
        cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = tbLastName.Text
        cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = tbFirstName.Text


        ' Open the connection.
        conn.Open()


        ' Execute the command.
        cmd.ExecuteNonQuery()
    End Using


    ' Rebind the GridView control to show inserted data.
    BindGridView()


    ' Empty the TextBox controls.
    tbLastName.Text = ""
    tbFirstName.Text = ""


    ' Show the Add button and hiding the Add panel.
    lbtnAdd.Visible = True
    pnlAdd.Visible = False
End Sub


Protected Sub lbtnCancel_Click(ByVal sender As Object, ByVal e As EventArgs)
    ' Empty the TextBox controls.
    tbLastName.Text = ""
    tbFirstName.Text = ""


    ' Show the Add button and hiding the Add panel.
    lbtnAdd.Visible = True
    pnlAdd.Visible = False
End Sub
 

Step 6. The DataInMemory.aspx page is pretty much the same with DataFromDataBase.aspx page, this web page get data from memory, instead of database file. So we only need to add a new method "InitializeDataSource" for generating the DataTable variable, then we need to modify the BindGridView method to bind new the DataTable with GridView.

The following code is use initialize the DataTable and stores it in ViewState.

VB
Edit|Remove 
' Initialize the DataTable.
Private Sub InitializeDataSource()
    ' Create a DataTable object named dtPerson.
    Dim dtPerson As New DataTable()


    ' Add four columns to the DataTable.
    dtPerson.Columns.Add("PersonID")
    dtPerson.Columns.Add("LastName")
    dtPerson.Columns.Add("FirstName")


    ' Specify PersonID column as an auto increment column
    ' and set the starting value and increment.
    dtPerson.Columns("PersonID").AutoIncrement = True
    dtPerson.Columns("PersonID").AutoIncrementSeed = 1
    dtPerson.Columns("PersonID").AutoIncrementStep = 1


    ' Set PersonID column as the primary key.
    Dim dcKeys As DataColumn() = New DataColumn(0) {}
    dcKeys(0) = dtPerson.Columns("PersonID")
    dtPerson.PrimaryKey = dcKeys


    ' Add new rows into the DataTable.
    dtPerson.Rows.Add(Nothing, "Davolio", "Nancy")
    dtPerson.Rows.Add(Nothing, "Fuller", "Andrew")
    dtPerson.Rows.Add(Nothing, "Leverling", "Janet")
    dtPerson.Rows.Add(Nothing, "Dodsworth", "Anne")
    dtPerson.Rows.Add(Nothing, "Buchanan", "Steven")
    dtPerson.Rows.Add(Nothing, "Suyama", "Michael")
    dtPerson.Rows.Add(Nothing, "Callahan", "Laura")


    ' Store the DataTable in ViewState. 
    ViewState("dtPerson") = dtPerson
End Sub


Private Sub BindGridView()
    If ViewState("dtPerson") IsNot Nothing Then
        ' Get the DataTable from ViewState.
        Dim dtPerson As DataTable = DirectCast(ViewState("dtPerson"), DataTable)


        ' Convert the DataTable to DataView.
        Dim dvPerson As New DataView(dtPerson)


        ' Set the sort column and sort order.
        dvPerson.Sort = ViewState("SortExpression").ToString()


        ' Bind the GridView control.
        gvPerson.DataSource = dvPerson
        gvPerson.DataBind()
    End If
End Sub
 

Step 7. Build the application and you can debug it.

More Information

•         Using Statement (Visual Basic)

•         Understanding ASP.NET View State

•         Editing, Inserting, and Deleting Data

•         Adding Client-Side Confirmation When Deleting

•         WebControl.Attributes Property