locked
Only Show Certain Records from Database on Webpage RRS feed

  • Question

  • User1225369314 posted

    Hi I've created a webpage which shows a table from an access database

    FruitType YearTotal SoldTotal
    Pear 2011 400
    Apple 2011 500
    Pear 2010 300
    Apple 2010 600
    Pear 2009 200
    Apple 2009 200

    Is there a way I could create a drop down box on this page which allows the user to select a year, then it only shows the records where the year is equal to the dropdown box?

    Below are my aspx and aspx.vb pages

    Many Thanks for any help!

     

    <%@ Page Title="Home Page" 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:Repeater ID="Fruits" runat="server"> 
            <HeaderTemplate> 
                <table border="1" width="100%"> 
                    <tr> 
                        <th> 
                            Fruit Name 
                        </th> 
                        <th> 
                            Year 
                        </th> 
                        <th> 
                            Sold
                        </th> 
                    </tr> 
            </HeaderTemplate> 
            <ItemTemplate> 
                <tr> 
                    <td width="50%"> 
                        <%#Container.DataItem("FruitType")%></td> 
                    <td> 
                        <%#Container.DataItem("YearTotal")%></td> 
                    <td> 
                        <%#Container.DataItem("SoldTotal")%></td> 
                </tr> 
            </ItemTemplate> 
            <FooterTemplate> 
                </table> 
            </FooterTemplate> 
        </asp:Repeater>
    
    </asp:Content>
    Imports System.Data.OleDb
    
    Partial Class _Default
        Inherits System.Web.UI.Page
    
        Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
            If Not Page.IsPostBack Then
    
                Dim dbconn, sql, dbcomm, dbread
                dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;  data source=" & Server.MapPath("\Products\App_Data\Products.mdb"))
                dbconn.Open()
                sql = "SELECT * FROM Fruits"
                dbcomm = New OleDbCommand(sql, dbconn)
                dbread = dbcomm.ExecuteReader()
                Fruits.DataSource = dbread
                Fruits.DataBind()
    
                dbread.Close()
                dbconn.Close()
    
            End If
        End Sub
    End Class
    Thursday, December 22, 2011 4:15 AM

Answers

  • User551462331 posted

    Thank you so much KK!!!!!! That worked brilliant.

    One last question and it will work perfect.

    How can I make it so that the default view when loading is the latest year?

    THank You :-)

    I would update page_load event like this

        Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load 
            If Not Page.IsPostBack Then 
               year_name.Items.Add(New ListItem("2009","2009")); 
               year_name.Items.Add(New ListItem("2010","2010")); 
               year_name.Items.Add(New ListItem("2011","2011")); 
               year_name.Items.Add(New ListItem("2012","2012")); 
               year_name.Items.Insert(0,"--select year--"); 
              
                string strCurrentYear = DateTime.Now.Year.ToString();
    
                Dim dbconn, sql, dbcomm, dbread 
                dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;  data source=" & Server.MapPath("\Products\App_Data\Products.mdb")) 
                dbconn.Open() 
                sql = "SELECT * FROM Fruits where YearTotal='" + strCurrentYear + "'";  
                dbcomm = New OleDbCommand(sql, dbconn) 
                dbread = dbcomm.ExecuteReader() 
                Fruits.DataSource = dbread 
                Fruits.DataBind() 
     
                dbread.Close() 
                dbconn.Close() 
    
            End If 
        End Sub 
    

     

    here, i have used DateTime.Now.Year to get current year.... and pass that to query

    hope this helps...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 22, 2011 5:08 AM

All replies

  • User-1360095595 posted

    Yes indeed.  You'll need code similar to what you have in page_load, but with an added where clause: ... WHERE YearTotal = @YearTotal.  You'd do this in the selectedindexchanged event of the DDL, and you'd set the parameter in question to the selectedvalue properrty of the DDL (potenitally converted to int depending on your DB column type).

    Thursday, December 22, 2011 4:23 AM
  • User1225369314 posted

    Thanks for this, I'm only just starting and really unsure about how to code this and where to put it.

    Mant Thanks

    Thursday, December 22, 2011 4:26 AM
  • User-1360095595 posted

    Put a DDL on the page and populate it with the years (you can bind it to a datasource).  Handle the SelectedIndecChanged event of the DDL.  Also set the DDL's AutoPostback to true.  Now in the handler of the DDL, write SQL code that's identical to what you have in page_load with the additions I described in my previous post. This should get you going.  You'll need to account for other issues, but one step at a time.

    Thursday, December 22, 2011 4:32 AM
  • User551462331 posted

    change aspx code as

    <%@ Page Title="Home Page" 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:DropDownList ID="year_name" runat="server" Width="78px" OnSelectedIndexChanged="year_name_SelectedIndexChanged" AutoPostBack="True" > </asp:DropDownList> 
        <asp:Repeater ID="Fruits" runat="server"> 
            <HeaderTemplate> 
                <table border="1" width="100%"> 
                    <tr> 
                        <th> 
                            Fruit Name 
                        </th> 
                        <th> 
                            Year 
                        </th> 
                        <th> 
                            Sold
                        </th> 
                    </tr> 
            </HeaderTemplate> 
            <ItemTemplate> 
                <tr> 
                    <td width="50%"> 
                        <%#Container.DataItem("FruitType")%></td> 
                    <td> 
                        <%#Container.DataItem("YearTotal")%></td> 
                    <td> 
                        <%#Container.DataItem("SoldTotal")%></td> 
                </tr> 
            </ItemTemplate> 
            <FooterTemplate> 
                </table> 
            </FooterTemplate> 
        </asp:Repeater>
    
    </asp:Content>

    add change codebehind code as

    Imports System.Data.OleDb
    
    Partial Class _Default
        Inherits System.Web.UI.Page
    
        Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
            If Not Page.IsPostBack Then
               year_name.Items.Add(New ListItem("2009","2009"));
               year_name.Items.Add(New ListItem("2010","2010"));
               year_name.Items.Add(New ListItem("2011","2011"));
               year_name.Items.Add(New ListItem("2012","2012"));
      year_name.Items.Insert(0,"--select year--"); End If End Sub Public Sub year_name_SelectedIndexChanged(sender As Object, e As EventArgs) Dim dbconn, sql, dbcomm, dbread dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("\Products\App_Data\Products.mdb")) dbconn.Open() sql = "SELECT * FROM Fruits where YearTotal='" + year_name.SelectedValue + "'" dbcomm = New OleDbCommand(sql, dbconn) dbread = dbcomm.ExecuteReader() Fruits.DataSource = dbread Fruits.DataBind() dbread.Close() dbconn.Close() End Sub End Class

    i have remove code from page_load. hence, initially there will not be any record displayed... but dropdown would display...

    when changing value from dropdown, it will populate filtered data.... here, i have hardcoded year values in code.... u can even get that from database...

    hope this helps...

    Thursday, December 22, 2011 4:34 AM
  • User-448512826 posted

    Hi,

    you just simple do that in below query in dropdownselected_indexchanged event....

    like..

    select * from <tablename> where year=@year

    in parameter you pass that @year,ddl.selectedvalue

    Thanks...

    Thursday, December 22, 2011 4:35 AM
  • User1225369314 posted

    Thank you so much KK!!!!!! That worked brilliant.

    One last question and it will work perfect.

    How can I make it so that the default view when loading is the latest year?

    THank You :-)

    Thursday, December 22, 2011 5:00 AM
  • User551462331 posted

    Thank you so much KK!!!!!! That worked brilliant.

    One last question and it will work perfect.

    How can I make it so that the default view when loading is the latest year?

    THank You :-)

    I would update page_load event like this

        Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load 
            If Not Page.IsPostBack Then 
               year_name.Items.Add(New ListItem("2009","2009")); 
               year_name.Items.Add(New ListItem("2010","2010")); 
               year_name.Items.Add(New ListItem("2011","2011")); 
               year_name.Items.Add(New ListItem("2012","2012")); 
               year_name.Items.Insert(0,"--select year--"); 
              
                string strCurrentYear = DateTime.Now.Year.ToString();
    
                Dim dbconn, sql, dbcomm, dbread 
                dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;  data source=" & Server.MapPath("\Products\App_Data\Products.mdb")) 
                dbconn.Open() 
                sql = "SELECT * FROM Fruits where YearTotal='" + strCurrentYear + "'";  
                dbcomm = New OleDbCommand(sql, dbconn) 
                dbread = dbcomm.ExecuteReader() 
                Fruits.DataSource = dbread 
                Fruits.DataBind() 
     
                dbread.Close() 
                dbconn.Close() 
    
            End If 
        End Sub 
    

     

    here, i have used DateTime.Now.Year to get current year.... and pass that to query

    hope this helps...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 22, 2011 5:08 AM
  • User-1199946673 posted

    You can do this without any code behind

    <asp:DropDownList ID="year_name" runat="server"
        AutoPostBack="True"
        DataSourceID="AccessDataSource1"
        DataTextField="YearTotal"
        DataValueField="YearTotal" />  
    <asp:Repeater ID="Fruits" runat="server"
        DataSourceID="AccessDataSource2">  
        <HeaderTemplate>  
            <table border="1" width="100%">  
                <tr>  
                    <th>  
                        Fruit Name  
                    </th>  
                    <th>  
                        Year  
                    </th>  
                    <th>  
                        Sold 
                    </th>  
                </tr>  
        </HeaderTemplate>  
        <ItemTemplate>  
            <tr>  
                <td width="50%">  
                    <%#Container.DataItem("FruitType")%></td>  
                <td>  
                    <%#Container.DataItem("YearTotal")%></td>  
                <td>  
                    <%#Container.DataItem("SoldTotal")%></td>  
            </tr>  
        </ItemTemplate>  
        <FooterTemplate>  
            </table>  
        </FooterTemplate>  
    </asp:Repeater> 
    
    <asp:AccessDataSource ID="AccessDataSource1" runat="server"
                          DataFile="~/App_Data/Products.mdb"
                          SelectCommand="SELECT YearTotal FROM Fruits GROUP BY YearTotal ORDER BY YearTotal DESC">
    </asp:AccessDataSource>
    <asp:AccessDataSource ID="AccessDataSource2" runat="server"
                          SelectCommand="SELECT * FROM Fruits WHERE YearTotal = @YearTotal">
        <SelectParameters>
            <asp:ControlParameter Name="YearTotal"
                                  ControlID="year_name"
                                  PropertyName="SelectedValue" />
        </SelectParameters>
    </asp:AccessDataSource>
    
    Thursday, December 22, 2011 5:22 AM
  • User1225369314 posted

    Thanks for that Hans I couldnt get it to work though. The error which comes back when I run it is...

    Description:
    An error occurred during the compilation of a resource required to service
    this request. Please review the following specific error details and modify your
    source code appropriately.

    Compiler Error Message: CS1955:
    Non-invocable member 'System.Web.UI.WebControls.RepeaterItem.DataItem' cannot be
    used like a method.

    Source Error:

    Line 41:         <tr>  
    Line 42:             <td width="50%">  
    Line 43: <%#Container.DataItem("FruitType")%></td> Line 44:             <td>  
    Line 45:                 <%#Container.DataItem("YearTotal")%></td>  

     

    Thanks KK, you've been really helpful, I've just struggled with that last block of code. I've changed the .asp.vb code to

    Imports System.Data.OleDb
    
    Partial Class _Default
        Inherits System.Web.UI.Page
    
        Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
            If Not Page.IsPostBack Then
                year_name.Items.Add(New ListItem("2009", "2009"))
                year_name.Items.Add(New ListItem("2010", "2010"))
                year_name.Items.Add(New ListItem("2011", "2011"))
                year_name.Items.Add(New ListItem("2012", "2012"))
                year_name.Items.Insert(0, "--select year--")
    
                string strCurrentYear = DateTime.Now.Year.ToString();
    
                Dim dbconn, sql, dbcomm, dbread
                dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;  data source=" & Server.MapPath("\Products\App_Data\Products.mdb"))
                dbconn.Open()
                sql = "SELECT * FROM Fruits where YearTotal='" + strCurrentYear + "'";  
                dbcomm = New OleDbCommand(sql, dbconn)
                dbread = dbcomm.ExecuteReader()
                Fruits.DataSource = dbread
                Fruits.DataBind()
    
                dbread.Close()
                dbconn.Close()
    
            End If
        End Sub
    
    End Class

    But the code editor is saying on this line

    string strCurrentYear = DateTime.Now.Year.ToString();

    'String' is a class type and cannot be used as an expression and that '.' expected when i hover over strCurrentYear

    Thanks again

    Thursday, December 22, 2011 5:56 AM
  • User551462331 posted

    'String' is a class type and cannot be used as an expression and that '.' expected when i hover over strCurrentYear

    aah... my bad... that was a C# bit

    please chage this line as

    Dim strCurrentYear as String = DateTime.Now.Year.ToString();

    hope this helps...

    Thursday, December 22, 2011 6:35 AM
  • User-1199946673 posted

    Try

        <ItemTemplate> 
            <tr> 
                <td width="50%"> 
                    <%# Eval("FruitType") %></td> 
                <td> 
                    <%# Eval("YearTotal") %></td> 
                <td> 
                    <%# Eval("SoldTotal") %></td> 
            </tr> 
        </ItemTemplate>

    Thursday, December 22, 2011 6:39 AM
  • User1225369314 posted

    Thanks KK, nearly there!  I've got one more error when I run it though sorry.

    Description:
    An error occurred during the compilation of a resource required to service
    this request. Please review the following specific error details and modify your
    source code appropriately.

    Compiler Error Message: BC30456:
    'year_name_SelectedIndexChanged' is not a member of
    'ASP.default_aspx'.

    Source Error:

    Line 7:  <asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">  
    Line 8:   
    Line 9: <asp:DropDownList ID="year_name" runat="server" Width="78px" OnSelectedIndexChanged="year_name_SelectedIndexChanged" AutoPostBack="True" > </asp:DropDownList> Line 10:     <asp:Repeater ID="Fruits" runat="server">  
    Line 11:         <HeaderTemplate>  

    Thanks Hans, i'll give that a try :-)

    Thursday, December 22, 2011 6:42 AM
  • User551462331 posted

    Thanks KK, nearly there!  I've got one more error when I run it though sorry.

    selectedindexchanged event should be

        Protected Sub year_name_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles year_name.SelectedIndexChanged
     
                Dim dbconn, sql, dbcomm, dbread 
                dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;  data source=" & Server.MapPath("\Products\App_Data\Products.mdb")) 
                dbconn.Open() 
                sql = "SELECT * FROM Fruits where YearTotal='" + year_name.SelectedValue + "'" 
                dbcomm = New OleDbCommand(sql, dbconn) 
                dbread = dbcomm.ExecuteReader() 
                Fruits.DataSource = dbread 
                Fruits.DataBind() 
     
                dbread.Close() 
                dbconn.Close() 
     
       End Sub 
    

    and remove selectedindexchanged event name from aspx page... like this

    <asp:DropDownList ID="year_name" runat="server" Width="78px" AutoPostBack="True" > </asp:DropDownList>

    hope this helps...

    Thursday, December 22, 2011 6:54 AM
  • User1225369314 posted

    Ah Fantastic!!!!  One last thing and I will mark it as answered!

    Is there anyway to make the drop down box automatically select the latest year when the page first loads aswell?

    Thanks for all your help its helped me so much!

    Thursday, December 22, 2011 7:45 AM
  • User551462331 posted

    put this towards end in page_load event...

     year_name.SelectedItem.Value = DateTime.Now.Year

    hope this helps...

    Thursday, December 22, 2011 8:21 AM
  • User-448512826 posted

    Hi,

    but there are fear of sql injection..

    so refer below link and change your code like that...

    http://www.mikesdotnetting.com/Article/113/Preventing-SQL-Injection-in-ASP.NET

    Thanks...

    Thursday, December 22, 2011 8:48 AM
  • User1225369314 posted

    Hi KK, hope your having a good christmas.

    I still cant do this last thing sorry, i've put...

     year_name.SelectedItem.Value = DateTime.Now.Year

    ...everywhere but cant get it to work!

    Many Thanks

    Monday, December 26, 2011 6:02 AM
  • User-448512826 posted

    Hi,

    if you need the  same result in pageLoad time then you have to call again that procedure in page load..

    so create a procedure lke..

    private Sub Getsearch(Byval year as string) as String
     Dim dbconn, sql, dbcomm, dbread 
                dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;  data source=" & Server.MapPath("\Products\App_Data\Products.mdb")) 
                dbconn.Open() 
                sql = "SELECT * FROM Fruits where YearTotal=@year
    
                dbcomm = New OleDbCommand(sql, dbconn) 
            dbcomm .Parameters.Add("@year", SqlDbType.string);
          dbcomm .Parameters["@year"].Value = Convert.ToString(year);
                dbread = dbcomm.ExecuteReader() 
                Fruits.DataSource = dbread 
                Fruits.DataBind() 
     
                dbread.Close() 
                dbconn.Close() 
    End Sub
    and in Page Load time call like below
    sub page_load 
    if Not ispostback then
    dim y as string=DateTime.Now.Year.Tostring()
    Getsearch(y)
    end if
    in selected Inchanged event
    Getsearch(ddlyear.selectedvalue)


    Thanks...
    Tuesday, December 27, 2011 12:10 AM
  • User-1757347794 posted

    Hello,

    Yes you can you just need to pass the selected year from the dropdown box to your select query's where clause

    Tuesday, December 27, 2011 12:18 AM
  • User551462331 posted

    Hi KK, hope your having a good christmas.

    I still cant do this last thing sorry, i've put...

     year_name.SelectedItem.Value = DateTime.Now.Year

    ...everywhere but cant get it to work!

    Many Thanks

    year_name.SelectedValue = DateTime.Now.Year.ToString()

    hope this helps...

    Wednesday, December 28, 2011 5:04 AM
  • User1225369314 posted

    That was it thanks!!

    Wednesday, December 28, 2011 5:10 AM