Solved

Showing all records in ASP GridView by default

Posted on 2013-06-30
1
392 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

738 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