Using Gridview in ASP.NET 2.0

Posted on November 25, 2007 - Filed Under ASP.NET |

By Aurelie A. Peralta

Gridview Demo in ASP.NET 2.0

I have prepared a simple application in ASP.NET that uses a Gridview Object to perform Searching, Adding, Editing, and Deleting of records. The editing and deleting operations will be performed in the Gridview object itself. Column Sorting and Numeric Paging of records were also incorporated.

The first step is to create a database MELODB using MS SQL SERVER 2005 Express. Then use this database and create a table named [Course Table] using this sqlscript:

——————————————————— 

USE [melodb]
GO
/****** Object:  Table [dbo].[Course Table]    Script Date: 11/25/2007 18:19:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Course Table](
 [Course Code] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [Course Description] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [Major] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Course Table] PRIMARY KEY CLUSTERED
(
 [Course Code] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

———————————————————

After creating the database, create the CONNECTION.ASPX file using the following codes:

———————————————————

 <%@ Import Namespace=”System.Data.SQLClient” %>
<%@ Import Namespace=”System.Data” %>
<%@ Import Namespace=”System.XML” %>

<script runat=”server” language=”VB”>
    Dim dbconn As New SqlClient.SqlConnection(”Data Source=MISBRAIN\SQLEXPRESS;Initial Catalog=melodb;Integrated Security=True”)
    Dim connstring As String = “Data Source=MISBRAIN\SQLEXPRESS;Initial Catalog=melodb;Integrated Security=True”
      �
</script>

———————————————————

And finally create the GRIDVIEWDEMO.ASPX file with the following codes:

———————————————————

 <%@ Page Language=”VB” %>
<%@ Import Namespace=”System.Web.UI.WebControls” %>
<%@ Import Namespace=”System.Data.SQLClient” %>
<%@ Import Namespace=”System.Data” %>
<%@ Import Namespace=”System.XML” %>
<!– #INCLUDE FILE=”connection.aspx” –>

<script runat=”server”>

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        me.sqldatasource1.connectionstring = connstring
      �
        If Not IsPostBack Then�
            Me.SqlDataSource1.SelectParameters.Item(0).DefaultValue = ” ”
            loadingthem()
                                �
        End If
          �
                    �
    End Sub
  �
    Sub totalling()
        Dim x As Integer
        Dim sqlstr As String
        sqlstr = Me.SqlDataSource1.SelectCommand
      �
        Dim dbadapter As New SqlClient.SqlDataAdapter(sqlstr, dbconn)
        Dim dbdataset As New DataSet
      �
        dbdataset.Clear()
        dbadapter.Fill(dbdataset, “Course Table”)
        x = dbdataset.Tables(”Course Table”).DefaultView.Count
        Me.GridView1.Caption = “Total Records Found :” & x
      �
    End Sub
  �
    Sub loadingthem()
        Me.SqlDataSource1.SelectCommand = “SELECT [Course Code] AS Course_Code, [Course Description] AS Course_Description, [Major] FROM [Course Table] Where [Course Description] Like ‘” & Me.TextBox1.Text.Trim & “%’”
        totalling()
        Me.GridView1.DataBind()
        Me.TextBox2.Text = vbNullString
        Me.TextBox3.Text = vbNullString
        Me.TextBox4.Text = vbNullString
      �
    End Sub
 �
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)     �
        loadingthem()
        Me.Label6.Text = vbNullString
        Me.Label7.Text = vbNullString
      �
    End Sub
  �
    Sub pagingnow(ByVal sender As Object, ByVal e As System.EventArgs)
        loadingthem()
      �
    End Sub
  �
    Sub updatenow(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs)
        If e.NewValues.Item(1) = vbNullString Or e.NewValues.Item(2) = vbNullString Then
            e.Cancel = True
            Exit Sub
          �
        End If
        Me.SqlDataSource1.UpdateCommand = “Update [Course Table] Set [Course Description]=@Course_Description, [Major]=@Major Where ([Course Code] = @Course_Code)”
        loadingthem()
        Me.Label6.Text = “Course successfully updated.”
        Me.Label7.Text = vbNullString
    End Sub
  �
    Sub editnow(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs)
        loadingthem()
      �
    End Sub
  �
    Sub deletednow(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeletedEventArgs)
        loadingthem()
        If Me.Label7.Text = vbNullString Then
            Me.Label6.Text = “Course successfully deleted.”
        Else
            Me.Label6.Text = vbNullString
        End If
      �
    End Sub
  �
    Sub cancelnow(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs)
        loadingthem()
      �
    End Sub
    �
    Sub pagesorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs)
        loadingthem()
      �
    End Sub
  �
    Sub deletenow(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs)     �
        Dim dbconn As New SqlClient.SqlConnection(”Data Source=MISBRAIN\SQLExpress;Initial Catalog=registrardb;Integrated Security=True”)
        Dim dbadapter As New SqlClient.SqlDataAdapter(”Select * from [Student Directory Table] Where [Course Code]=’” & e.Keys.Item(0) & “‘”, dbconn)
        Dim dbdataset As New DataSet
      �
        dbdataset.Clear()
        dbadapter.Fill(dbdataset, “Student Directory Table”)
        If dbdataset.Tables(”Student Directory Table”).DefaultView.Count = 0 Then
            Me.SqlDataSource1.DeleteCommand = “DELETE FROM [Course Table] WHERE ([Course Code] = @Course_Code)”
            loadingthem()
            Me.Label7.Text = vbNullString
        Else
            Me.SqlDataSource1.DeleteCommand = “DELETE FROM [Course Table] WHERE ([Course Code] = ‘@Course_Code’)”
            loadingthem()
            Me.Label7.Text = “Cannot Delete this record because it has related records!”
            Exit Sub
        End If
      �
    End Sub
  �
    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)
              �
        If Me.TextBox2.Text = vbNullString Then
            Me.Label6.Text = “Course Code cannot be empty!”
            Exit Sub
          �
        End If
        If Me.TextBox3.Text = vbNullString Then
            Me.Label6.Text = “Course Description cannot be empty!”
            Exit Sub
          �
        End If
        If Me.TextBox4.Text = vbNullString Then
            Me.Label6.Text = “Major cannot be empty!”
            Exit Sub
          �
        End If
      �
        Me.Label6.Text = vbNullString
        Dim dbadapter As New SqlClient.SqlDataAdapter(”Select * from [Course Table] Where [Course Code]=’” & Me.TextBox2.Text.Trim & “‘”, dbconn)
        Dim dbdataset As New DataSet
      �
        dbdataset.Clear()
        dbadapter.Fill(dbdataset, “Course Table”)
        If dbdataset.Tables(”Course Table”).DefaultView.Count > 0 Then
            Me.Label6.Text = “Course Code already exist!”
            Exit Sub
          �
        End If
        Dim insertcommand As New SqlClient.SqlCommand
        insertcommand.CommandText = “Insert Into [Course Table] ([Course Code], [Course Description], [Major]) Values (’” & Me.TextBox2.Text.Trim & “‘, ‘” & Me.TextBox3.Text.Trim & “‘, ‘” & Me.TextBox4.Text.Trim & “‘)”
        insertcommand.Connection = dbconn
        dbconn.Open()
        insertcommand.ExecuteReader()
        dbconn.Close()
        loadingthem()
        Me.Label6.Text = “New Course successfully added.”
        Me.Label7.Text = vbNullString
        loadingthem()
        Me.TextBox2.Text = vbNullString
        Me.TextBox3.Text = vbNullString
        Me.TextBox4.Text = vbNullString
     �
    End Sub
     �
</script>

<html xmlns=”http://www.w3.org/1999/xhtml” >
<head>
    <title>Gridview Demo Page by Aurelie Peralta</title>
</head>
<body>
<form id=”form1″ runat=”server”>
    <div>

    <h3 class=”title02″>Courses Page</h3>
    <hr size=0 />
           �
            <asp:Label ID=”Label2″ runat=”server” Text=”Search Course Description that begins with ” CssClass=”menu02″></asp:Label>
            <asp:TextBox ID=”TextBox1″ runat=”server” CssClass=”menu02″></asp:TextBox>
            <asp:Button ID=”Button1″ runat=”server” Text=”Go” CssClass=”menu02″ OnClick=”Button1_Click” />      <a href=”Gridviewdemo.aspx#newcourse” mce_href=”Gridviewdemo.aspx#newcourse” class=”menu02″>Add New Course</a>
        <hr size=0 />
      �
            <asp:GridView
                ID=”GridView1″
                runat=”server”
                AllowPaging=”True”
                AllowSorting=”True”
                AutoGenerateColumns=”False”
                CellPadding=”4″
                DataSourceID=”SqlDataSource1″
                Font-Names=”Verdana”
                Font-Size=”8pt”
                ForeColor=”#333333″
                GridLines=”None”
                Width=”100%”
                OnPageIndexChanged=”pagingnow”
                OnSorting=”pagesorting”
                OnRowDeleting=”deletenow”
                DataKeyNames=”Course_Code”
                OnRowUpdating=”updatenow”
                OnRowEditing=”editnow”
                OnRowCancelingEdit=”cancelnow”
                OnRowDeleted=”deletednow”
                CssClass=”menu09″>
                <FooterStyle BackColor=”#5D7B9D” Font-Bold=”True” ForeColor=”White” />
                <Columns>
                    <asp:CommandField ShowSelectButton=”True” />
                    <asp:CommandField ButtonType=”Button” ShowEditButton=”True” />
                    <asp:BoundField DataField=”Course_Code” HeaderText=”Course_Code” SortExpression=”Course_Code” />
                    <asp:BoundField DataField=”Course_Description” HeaderText=”Course_Description” SortExpression=”Course_Description” />
                    <asp:BoundField DataField=”Major” HeaderText=”Major” SortExpression=”Major” />
                    <asp:TemplateField ShowHeader=”False”>
                        <ItemTemplate>
                            <asp:Button ID=”Button1″ runat=”server” CausesValidation=”False” CommandName=”Delete”
                                Text=”Delete” OnClientClick=”return confirm(’Are you sure you want to delete this Course?’);” />
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
                <RowStyle BackColor=”#F7F6F3″ ForeColor=”#333333″ />
                <EditRowStyle BackColor=”#999999″ />
                <SelectedRowStyle BackColor=”#E2DED6″ Font-Bold=”True” ForeColor=”#333333″ />
                <PagerStyle BackColor=”#284775″ ForeColor=”White” HorizontalAlign=”Center” />
                <HeaderStyle BackColor=”#5D7B9D” Font-Bold=”True” ForeColor=”White” />
                <AlternatingRowStyle BackColor=”White” ForeColor=”#284775″ />
            </asp:GridView>
          �
            <asp:SqlDataSource ID=”SqlDataSource1″ runat=”server”
                ProviderName=”System.Data.SqlClient” >
                <SelectParameters>
                    <asp:ControlParameter ControlID=”TextBox1″ Name=”textboxpar” PropertyName=”Text” />
                </SelectParameters>
            </asp:SqlDataSource>
            <hr size=0 />
            <h3 class=”title02″><a name=”newcourse”>Add New Course</a></h3>
          �
            <table class=”menu02″>
            <tr>
            <td>
                <asp:Label ID=”Label3″ runat=”server” Text=”Course Code” Width=”93px”></asp:Label></td>
            <td style=”width: 353px”>
                <asp:TextBox ID=”TextBox2″ runat=”server” MaxLength=”20″ Width=”292px”></asp:TextBox></td>
            </tr>
            <tr>
            <td>
                <asp:Label ID=”Label4″ runat=”server” Text=”Course Description” Width=”120px”></asp:Label></td>
            <td style=”width: 353px”>
                <asp:TextBox ID=”TextBox3″ runat=”server” MaxLength=”100″ Width=”292px”></asp:TextBox></td>
            </tr>
            <tr>
            <td>
                <asp:Label ID=”Label5″ runat=”server” Text=”Major” Width=”74px”></asp:Label></td>
            <td style=”width: 353px”>
                <asp:TextBox ID=”TextBox4″ runat=”server” MaxLength=”100″ Width=”292px”></asp:TextBox></td>
            </tr>
            </table>
            <asp:Label ID=”Label6″ runat=”server” CssClass=”menu02″ ForeColor=”Red”></asp:Label>
            <asp:Label ID=”Label7″ runat=”server” CssClass=”menu02″ ForeColor=”Red”></asp:Label><br />
            <asp:Button ID=”Button2″ runat=”server” Text=”Add this new course to the database” OnClick=”Button2_Click” OnClientClick=”return confirm(’Are you sure?’);” />
        �
 </div>
    </form>
</body>
</html>

———————————————————

This simple web application demonstrates the use of the include command to simplify connection configuration, the power of ASP.NET Gridview object, javascript for making confirmations, and SQL commands used for file maintenance.

Comments

Leave a Reply