Solved

Showing all records in ASP GridView by default

Posted on 2013-06-30
1
384 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now