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

Gridview display all records on load?

Hello, can someone help me to understand how to make a gridview display all records from a SQLDataSource onload, and also dynamically display filtered records using dropdowns?

TIA,
SS
<%@ Page Title="Plant ssDNA Virus Database" Language="VB" MasterPageFile="~/Site.Master" AutoEventWireup="false"
    CodeFile="Default.aspx.vb" Inherits="_Default" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:PlantssDNAVirusesConnectionString %>" 
    SelectCommand="SELECT * FROM [PlantssDNA] WHERE ([Family] = @Family) ORDER BY [Family], [Species], [Abbreviation]" 
        OldValuesParameterFormatString="original_{0}" 
        ConflictDetection="CompareAllValues" 
        DeleteCommand="DELETE FROM [PlantssDNA] WHERE [VID] = @original_VID AND (([Family] = @original_Family) OR ([Family] IS NULL AND @original_Family IS NULL)) AND (([SubFamily] = @original_SubFamily) OR ([SubFamily] IS NULL AND @original_SubFamily IS NULL)) AND (([Genus] = @original_Genus) OR ([Genus] IS NULL AND @original_Genus IS NULL)) AND (([SubGenus] = @original_SubGenus) OR ([SubGenus] IS NULL AND @original_SubGenus IS NULL)) AND (([Species] = @original_Species) OR ([Species] IS NULL AND @original_Species IS NULL)) AND (([Isolate] = @original_Isolate) OR ([Isolate] IS NULL AND @original_Isolate IS NULL)) AND (([Abbreviation] = @original_Abbreviation) OR ([Abbreviation] IS NULL AND @original_Abbreviation IS NULL)) AND (([AccessionA] = @original_AccessionA) OR ([AccessionA] IS NULL AND @original_AccessionA IS NULL)) AND (([GIA] = @original_GIA) OR ([GIA] IS NULL AND @original_GIA IS NULL)) AND (([AccessionB] = @original_AccessionB) OR ([AccessionB] IS NULL AND @original_AccessionB IS NULL)) AND (([GIB] = @original_GIB) OR ([GIB] IS NULL AND @original_GIB IS NULL)) AND (([NC#] = @original_column1) OR ([NC#] IS NULL AND @original_column1 IS NULL)) AND (([Geography] = @original_Geography) OR ([Geography] IS NULL AND @original_Geography IS NULL)) AND (([Year2007A] = @original_Year2007A) OR ([Year2007A] IS NULL AND @original_Year2007A IS NULL)) AND (([Year2007B] = @original_Year2007B) OR ([Year2007B] IS NULL AND @original_Year2007B IS NULL)) AND (([Year2008A] = @original_Year2008A) OR ([Year2008A] IS NULL AND @original_Year2008A IS NULL)) AND (([Year2008B] = @original_Year2008B) OR ([Year2008B] IS NULL AND @original_Year2008B IS NULL)) AND (([Year2009A] = @original_Year2009A) OR ([Year2009A] IS NULL AND @original_Year2009A IS NULL)) AND (([Year2009B] = @original_Year2009B) OR ([Year2009B] IS NULL AND @original_Year2009B IS NULL)) AND (([Year2010A] = @original_Year2010A) OR ([Year2010A] IS NULL AND @original_Year2010A IS NULL)) AND (([Year2010B] = @original_Year2010B) OR ([Year2010B] IS NULL AND @original_Year2010B IS NULL)) AND (([Year2011A] = @original_Year2011A) OR ([Year2011A] IS NULL AND @original_Year2011A IS NULL)) AND (([Year2011B] = @original_Year2011B) OR ([Year2011B] IS NULL AND @original_Year2011B IS NULL))" 
        InsertCommand="INSERT INTO [PlantssDNA] ([Family], [SubFamily], [Genus], [SubGenus], [Species], [Isolate], [Abbreviation], [AccessionA], [GIA], [AccessionB], [GIB], [NC#], [Geography], [Year2007A], [Year2007B], [Year2008A], [Year2008B], [Year2009A], [Year2009B], [Year2010A], [Year2010B], [Year2011A], [Year2011B]) VALUES (@Family, @SubFamily, @Genus, @SubGenus, @Species, @Isolate, @Abbreviation, @AccessionA, @GIA, @AccessionB, @GIB, @column1, @Geography, @Year2007A, @Year2007B, @Year2008A, @Year2008B, @Year2009A, @Year2009B, @Year2010A, @Year2010B, @Year2011A, @Year2011B)" 
        UpdateCommand="UPDATE [PlantssDNA] SET [Family] = @Family, [SubFamily] = @SubFamily, [Genus] = @Genus, [SubGenus] = @SubGenus, [Species] = @Species, [Isolate] = @Isolate, [Abbreviation] = @Abbreviation, [AccessionA] = @AccessionA, [GIA] = @GIA, [AccessionB] = @AccessionB, [GIB] = @GIB, [NC#] = @column1, [Geography] = @Geography, [Year2007A] = @Year2007A, [Year2007B] = @Year2007B, [Year2008A] = @Year2008A, [Year2008B] = @Year2008B, [Year2009A] = @Year2009A, [Year2009B] = @Year2009B, [Year2010A] = @Year2010A, [Year2010B] = @Year2010B, [Year2011A] = @Year2011A, [Year2011B] = @Year2011B WHERE [VID] = @original_VID AND (([Family] = @original_Family) OR ([Family] IS NULL AND @original_Family IS NULL)) AND (([SubFamily] = @original_SubFamily) OR ([SubFamily] IS NULL AND @original_SubFamily IS NULL)) AND (([Genus] = @original_Genus) OR ([Genus] IS NULL AND @original_Genus IS NULL)) AND (([SubGenus] = @original_SubGenus) OR ([SubGenus] IS NULL AND @original_SubGenus IS NULL)) AND (([Species] = @original_Species) OR ([Species] IS NULL AND @original_Species IS NULL)) AND (([Isolate] = @original_Isolate) OR ([Isolate] IS NULL AND @original_Isolate IS NULL)) AND (([Abbreviation] = @original_Abbreviation) OR ([Abbreviation] IS NULL AND @original_Abbreviation IS NULL)) AND (([AccessionA] = @original_AccessionA) OR ([AccessionA] IS NULL AND @original_AccessionA IS NULL)) AND (([GIA] = @original_GIA) OR ([GIA] IS NULL AND @original_GIA IS NULL)) AND (([AccessionB] = @original_AccessionB) OR ([AccessionB] IS NULL AND @original_AccessionB IS NULL)) AND (([GIB] = @original_GIB) OR ([GIB] IS NULL AND @original_GIB IS NULL)) AND (([NC#] = @original_column1) OR ([NC#] IS NULL AND @original_column1 IS NULL)) AND (([Geography] = @original_Geography) OR ([Geography] IS NULL AND @original_Geography IS NULL)) AND (([Year2007A] = @original_Year2007A) OR ([Year2007A] IS NULL AND @original_Year2007A IS NULL)) AND (([Year2007B] = @original_Year2007B) OR ([Year2007B] IS NULL AND @original_Year2007B IS NULL)) AND (([Year2008A] = @original_Year2008A) OR ([Year2008A] IS NULL AND @original_Year2008A IS NULL)) AND (([Year2008B] = @original_Year2008B) OR ([Year2008B] IS NULL AND @original_Year2008B IS NULL)) AND (([Year2009A] = @original_Year2009A) OR ([Year2009A] IS NULL AND @original_Year2009A IS NULL)) AND (([Year2009B] = @original_Year2009B) OR ([Year2009B] IS NULL AND @original_Year2009B IS NULL)) AND (([Year2010A] = @original_Year2010A) OR ([Year2010A] IS NULL AND @original_Year2010A IS NULL)) AND (([Year2010B] = @original_Year2010B) OR ([Year2010B] IS NULL AND @original_Year2010B IS NULL)) AND (([Year2011A] = @original_Year2011A) OR ([Year2011A] IS NULL AND @original_Year2011A IS NULL)) AND (([Year2011B] = @original_Year2011B) OR ([Year2011B] IS NULL AND @original_Year2011B IS NULL))">
        <DeleteParameters>
            <asp:Parameter Name="original_VID" Type="Int32" />
            <asp:Parameter Name="original_Family" Type="String" />
            <asp:Parameter Name="original_SubFamily" Type="String" />
            <asp:Parameter Name="original_Genus" Type="String" />
            <asp:Parameter Name="original_SubGenus" Type="String" />
            <asp:Parameter Name="original_Species" Type="String" />
            <asp:Parameter Name="original_Isolate" Type="String" />
            <asp:Parameter Name="original_Abbreviation" Type="String" />
            <asp:Parameter Name="original_AccessionA" Type="String" />
            <asp:Parameter Name="original_GIA" Type="String" />
            <asp:Parameter Name="original_AccessionB" Type="String" />
            <asp:Parameter Name="original_GIB" Type="String" />
            <asp:Parameter Name="original_column1" Type="String" />
            <asp:Parameter Name="original_Geography" Type="String" />
            <asp:Parameter Name="original_Year2007A" Type="String" />
            <asp:Parameter Name="original_Year2007B" Type="String" />
            <asp:Parameter Name="original_Year2008A" Type="String" />
            <asp:Parameter Name="original_Year2008B" Type="String" />
            <asp:Parameter Name="original_Year2009A" Type="String" />
            <asp:Parameter Name="original_Year2009B" Type="String" />
            <asp:Parameter Name="original_Year2010A" Type="String" />
            <asp:Parameter Name="original_Year2010B" Type="String" />
            <asp:Parameter Name="original_Year2011A" Type="String" />
            <asp:Parameter Name="original_Year2011B" Type="String" />
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter Name="Family" Type="String" />
            <asp:Parameter Name="SubFamily" Type="String" />
            <asp:Parameter Name="Genus" Type="String" />
            <asp:Parameter Name="SubGenus" Type="String" />
            <asp:Parameter Name="Species" Type="String" />
            <asp:Parameter Name="Isolate" Type="String" />
            <asp:Parameter Name="Abbreviation" Type="String" />
            <asp:Parameter Name="AccessionA" Type="String" />
            <asp:Parameter Name="GIA" Type="String" />
            <asp:Parameter Name="AccessionB" Type="String" />
            <asp:Parameter Name="GIB" Type="String" />
            <asp:Parameter Name="column1" Type="String" />
            <asp:Parameter Name="Geography" Type="String" />
            <asp:Parameter Name="Year2007A" Type="String" />
            <asp:Parameter Name="Year2007B" Type="String" />
            <asp:Parameter Name="Year2008A" Type="String" />
            <asp:Parameter Name="Year2008B" Type="String" />
            <asp:Parameter Name="Year2009A" Type="String" />
            <asp:Parameter Name="Year2009B" Type="String" />
            <asp:Parameter Name="Year2010A" Type="String" />
            <asp:Parameter Name="Year2010B" Type="String" />
            <asp:Parameter Name="Year2011A" Type="String" />
            <asp:Parameter Name="Year2011B" Type="String" />
        </InsertParameters>
        <SelectParameters>
            <asp:ControlParameter ControlID="ddlFamily" Name="Family" 
                PropertyName="SelectedValue" Type="String" />
        </SelectParameters>
        <UpdateParameters>
            <asp:Parameter Name="Family" Type="String" />
            <asp:Parameter Name="SubFamily" Type="String" />
            <asp:Parameter Name="Genus" Type="String" />
            <asp:Parameter Name="SubGenus" Type="String" />
            <asp:Parameter Name="Species" Type="String" />
            <asp:Parameter Name="Isolate" Type="String" />
            <asp:Parameter Name="Abbreviation" Type="String" />
            <asp:Parameter Name="AccessionA" Type="String" />
            <asp:Parameter Name="GIA" Type="String" />
            <asp:Parameter Name="AccessionB" Type="String" />
            <asp:Parameter Name="GIB" Type="String" />
            <asp:Parameter Name="column1" Type="String" />
            <asp:Parameter Name="Geography" Type="String" />
            <asp:Parameter Name="Year2007A" Type="String" />
            <asp:Parameter Name="Year2007B" Type="String" />
            <asp:Parameter Name="Year2008A" Type="String" />
            <asp:Parameter Name="Year2008B" Type="String" />
            <asp:Parameter Name="Year2009A" Type="String" />
            <asp:Parameter Name="Year2009B" Type="String" />
            <asp:Parameter Name="Year2010A" Type="String" />
            <asp:Parameter Name="Year2010B" Type="String" />
            <asp:Parameter Name="Year2011A" Type="String" />
            <asp:Parameter Name="Year2011B" Type="String" />
            <asp:Parameter Name="original_VID" Type="Int32" />
            <asp:Parameter Name="original_Family" Type="String" />
            <asp:Parameter Name="original_SubFamily" Type="String" />
            <asp:Parameter Name="original_Genus" Type="String" />
            <asp:Parameter Name="original_SubGenus" Type="String" />
            <asp:Parameter Name="original_Species" Type="String" />
            <asp:Parameter Name="original_Isolate" Type="String" />
            <asp:Parameter Name="original_Abbreviation" Type="String" />
            <asp:Parameter Name="original_AccessionA" Type="String" />
            <asp:Parameter Name="original_GIA" Type="String" />
            <asp:Parameter Name="original_AccessionB" Type="String" />
            <asp:Parameter Name="original_GIB" Type="String" />
            <asp:Parameter Name="original_column1" Type="String" />
            <asp:Parameter Name="original_Geography" Type="String" />
            <asp:Parameter Name="original_Year2007A" Type="String" />
            <asp:Parameter Name="original_Year2007B" Type="String" />
            <asp:Parameter Name="original_Year2008A" Type="String" />
            <asp:Parameter Name="original_Year2008B" Type="String" />
            <asp:Parameter Name="original_Year2009A" Type="String" />
            <asp:Parameter Name="original_Year2009B" Type="String" />
            <asp:Parameter Name="original_Year2010A" Type="String" />
            <asp:Parameter Name="original_Year2010B" Type="String" />
            <asp:Parameter Name="original_Year2011A" Type="String" />
            <asp:Parameter Name="original_Year2011B" Type="String" />
        </UpdateParameters>
    </asp:SqlDataSource>
     
    <asp:SqlDataSource ID="SqlDSFamily" runat="server" 
        ConnectionString="<%$ ConnectionStrings:PlantssDNAVirusesConnectionString %>" 
        SelectCommand="SELECT DISTINCT * FROM [Family] ORDER BY [Family]"></asp:SqlDataSource>
     
    <asp:SqlDataSource ID="SqlDSGenus" runat="server"
      ConnectionString="<%$ ConnectionStrings:PlantssDNAVirusesConnectionString %>" 
        SelectCommand="SELECT * FROM [Genus] ORDER BY [Genus]"></asp:SqlDataSource>

    Records to display:
    <asp:DropDownList ID="ddlRowsPerPage" runat="server" AutoPostBack="True">
        <asp:ListItem Value="25"></asp:ListItem>
        <asp:ListItem Value="50"></asp:ListItem>
        <asp:ListItem Value="100"></asp:ListItem>
        <asp:ListItem Value="5000">ALL</asp:ListItem>
    </asp:DropDownList>
    &nbsp;&nbsp;&nbsp;&nbsp; Family:&nbsp;
    <asp:DropDownList ID="ddlFamily" runat="server" AutoPostBack="True" 
        DataSourceID="SqlDSFamily" DataTextField="Family" DataValueField="Family">
    </asp:DropDownList>
    &nbsp;&nbsp;&nbsp;&nbsp; Genus: <asp:DropDownList ID="ddlGenus" 
        runat="server" AutoPostBack="True" DataSourceID="SqlDSGenus" 
        DataTextField="Genus" DataValueField="Genus">
    </asp:DropDownList>
    <br />
    <br />
     
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
    AllowSorting="True" CellPadding="4" DataSourceID="SqlDataSource1" 
    ForeColor="#333333" GridLines="None" HorizontalAlign="Left" PageSize="20" 
    AutoGenerateColumns="False" CellSpacing="1" 
        DataKeyNames="VID" 
        AutoGenerateEditButton="True">
    <AlternatingRowStyle BackColor="White" BorderStyle="Solid" />
    <Columns>
        <asp:TemplateField ShowHeader="false">
            <ItemTemplate>
                <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="false" CommandName="Delete"
                     OnClientClick='return confirm("Are you sure you want to delete this entry?");'
                     Text="Delete" ForeColor="#CC0000" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="VID" HeaderText="VID" InsertVisible="False" 
            ReadOnly="True" SortExpression="VID" Visible="False" />
        <asp:BoundField DataField="Family" HeaderText="Family" 
            SortExpression="Family" >
        <ItemStyle Wrap="False" />
        </asp:BoundField>
        <asp:BoundField DataField="SubFamily" HeaderText="SubFamily" 
            SortExpression="SubFamily" >
        <ItemStyle Wrap="False" />
        </asp:BoundField>
        <asp:BoundField DataField="Genus" HeaderText="Genus" SortExpression="Genus" >
        <ItemStyle Wrap="False" />
        </asp:BoundField>
        <asp:BoundField DataField="SubGenus" HeaderText="SubGenus" 
            SortExpression="SubGenus" >
        <ItemStyle Wrap="False" />
        </asp:BoundField>
        <asp:BoundField DataField="Species" HeaderText="Species" 
            SortExpression="Species" >
        <ControlStyle Width="500px" />
        <ItemStyle Wrap="False" />
        </asp:BoundField>
        <asp:BoundField DataField="Isolate" HeaderText="Isolate" 
            SortExpression="Isolate" >
        <ControlStyle Width="500px" />
        <ItemStyle Wrap="False" />
        </asp:BoundField>
        <asp:BoundField DataField="Abbreviation" HeaderText="Abbreviation" 
            SortExpression="Abbreviation" >
        <ControlStyle Width="500px" />
        <ItemStyle Wrap="False" />
        </asp:BoundField>
        <asp:BoundField DataField="AccessionA" HeaderText="AccessionA" 
            SortExpression="AccessionA" >
        <ItemStyle Wrap="False" />
        </asp:BoundField>
        <asp:BoundField DataField="GIA" HeaderText="GIA" SortExpression="GIA" >
        <ItemStyle Wrap="False" />
        </asp:BoundField>
        <asp:BoundField DataField="AccessionB" HeaderText="AccessionB" 
            SortExpression="AccessionB" >
        <ItemStyle Wrap="False" />
        </asp:BoundField>
        <asp:BoundField DataField="GIB" HeaderText="GIB" SortExpression="GIB" >
        <ItemStyle Wrap="False" />
        </asp:BoundField>
        <asp:BoundField DataField="NC#" HeaderText="NC#" SortExpression="NC#" />
        <asp:BoundField DataField="Geography" HeaderText="Geography" 
            SortExpression="Geography" >
        <ItemStyle Wrap="False" />
        </asp:BoundField>
        <asp:BoundField DataField="Year2007A" HeaderText="2007A" 
            SortExpression="Year2007A" >
        <ItemStyle Wrap="False" />
        </asp:BoundField>
        <asp:BoundField DataField="Year2007B" HeaderText="2007B" 
            SortExpression="Year2007B" >
        <ItemStyle Wrap="False" />
        </asp:BoundField>
        <asp:BoundField DataField="Year2008A" HeaderText="2008A" 
            SortExpression="Year2008A" >
        <ItemStyle Wrap="False" />
        </asp:BoundField>
        <asp:BoundField DataField="Year2008B" HeaderText="2008B" 
            SortExpression="Year2008B" >
        <ItemStyle Wrap="False" />
        </asp:BoundField>
        <asp:BoundField DataField="Year2009A" HeaderText="2009A" 
            SortExpression="Year2009A" >
        <ItemStyle Wrap="False" />
        </asp:BoundField>
        <asp:BoundField DataField="Year2009B" HeaderText="2009B" 
            SortExpression="Year2009B" >
        <ItemStyle Wrap="False" />
        </asp:BoundField>
        <asp:BoundField DataField="Year2010A" HeaderText="2010A" 
            SortExpression="Year2010A" >
        <ItemStyle Wrap="False" />
        </asp:BoundField>
        <asp:BoundField DataField="Year2010B" HeaderText="2010B" 
            SortExpression="Year2010B" >
        <ItemStyle Wrap="False" />
        </asp:BoundField>
        <asp:BoundField DataField="Year2011A" HeaderText="2011A" 
            SortExpression="Year2011A" >
        <ItemStyle Wrap="False" />
        </asp:BoundField>
        <asp:BoundField DataField="Year2011B" HeaderText="2011B" 
            SortExpression="Year2011B" >
        <ItemStyle Wrap="False" />
        </asp:BoundField>
    </Columns>
    <EditRowStyle BackColor="#FFFF66" />
    <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
    <PagerSettings Mode="NumericFirstLast" Position="TopAndBottom" />
    <PagerStyle BackColor="#507CD1" ForeColor="#000099" HorizontalAlign="Left" 
        VerticalAlign="Middle" Font-Bold="True" Font-Size="Medium" />
    <RowStyle BackColor="#EFF3FB" />
    <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="False" ForeColor="#333333" />
    <SortedAscendingCellStyle BackColor="#F5F7FB" />
    <SortedAscendingHeaderStyle BackColor="#6D95E1" />
    <SortedDescendingCellStyle BackColor="#E9EBEF" />
    <SortedDescendingHeaderStyle BackColor="#4870BE" />
</asp:GridView>
    <br />

   </asp:Content>

Open in new window

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub ddlRowsPerPage_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlRowsPerPage.SelectedIndexChanged
        GridView1.PageSize = Convert.ToInt32(ddlRowsPerPage.SelectedValue)
    End Sub
End Class

Open in new window

0
Ahelbling
Asked:
Ahelbling
1 Solution
 
plusone3055Commented:

use a load event in your .VB file

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

    End Sub
0
 
AhelblingAuthor Commented:
Ok, I understand page_load event, but what shall I do with it to display all records, then filtered records on selected indexhchanged events for the dropdowns?

Do I need multiple SQLDataSources for this? Can I tie more than 1 to a gridview?
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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