• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • Last Modified:

Showing all records in ASP GridView by default

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
maqskywalker
Asked:
maqskywalker
1 Solution
 
Jerry MillerCommented:
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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now