Solved

Showing all records in ASP GridView by default

Posted on 2013-06-30
1
389 Views
Last Modified: 2013-06-30
Hi experts,

I'm using the Employees table of the Northwind sql server database.

I currently have a page called Test1.aspx
The page has an asp gridview , a textbox and a button.

This is my code for Test1.aspx

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Test1.aspx.vb" Inherits="Filters.Test1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Filter GridView with Textbox</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table style="width:29%;">
          <tr>
            <td class="style1">Employee Last Name:</td>
            <td>
                <asp:TextBox ID="TextBox1" AutoPostBack="False" runat="server"></asp:TextBox>
            </td>                 
            <td class="style2">
                <asp:Button ID="Button1" runat="server" Text="Submit" onclick="btnSubmit_Click" />
            </td>                 
          </tr>
        </table>
                
        <asp:GridView ID="GridView1" runat="server"></asp:GridView>
        
    </div>
    </form>
</body>
</html>

Open in new window


This is my code for Test1.aspx.vb

Imports System.Data.SqlClient

Public Class Test1
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Private Function BuildDataSet(commandText As String, tableName As String) As DataSet

        'Create Dataset 
        Dim myDataSet As New DataSet()

        'Create SqlConnection
        Dim myConnection As New SqlConnection("Data Source=MYSERVER\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True")
        'Create SqlCommand object
        Dim myCommand As New SqlCommand(commandText, myConnection)
        'Create the SqlDataAdapter
        Dim myAdapter As New SqlDataAdapter(myCommand)

        Try
            ' open the database connection
            myConnection.Open()

            ' fill the DataSet
            myAdapter.Fill(myDataSet, tableName)
        Finally
            ' always close the connection
            myConnection.Close()
        End Try

        ' return the DataSet
        Return (myDataSet)
    End Function

    Protected Sub btnSubmit_Click(sender As Object, e As EventArgs)

        'set up SQL query for Employees table
        Dim CommandText As String = "SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Notes, ReportsTo, PhotoPath FROM Employees WHERE LastName = '" + TextBox1.Text.ToString & "'"

        'setup the GridView
        GridView1.DataSource = BuildDataSet(CommandText, "Employees")
        GridView1.DataMember = "Employees"
        GridView1.DataBind()

    End Sub

End Class

Open in new window


When I run the page I get this:

when i run my asp page
If I type in "Davolio" in the Text Box and then click the button.

I get this:

filtering the Employees table by LastName
How do I modify this so that All Employees show up on the GridView by default when the page launches?
Then if I type a last name in the textbox and click submit it filters the employees from the all the employees to the one I typed in?
0
Comment
Question by:maqskywalker
1 Comment
 
LVL 18

Accepted Solution

by:
Jerry Miller earned 500 total points
ID: 39288779
Put this in your page_load event:

If NOT  IsPostBack Then
  'set up SQL query for Employees table
        Dim CommandText As String = "SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Notes, ReportsTo, PhotoPath FROM Employees"

        'setup the GridView
        GridView1.DataSource = BuildDataSet(CommandText, "Employees")
        GridView1.DataMember = "Employees"
        GridView1.DataBind()
End if
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question