Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Showing all records in ASP GridView by default

Posted on 2013-06-30
1
Medium Priority
?
397 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

877 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