Only Show Certain Records from Database on Webpage

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

Answer 1

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).

Answer 2

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

Answer 3

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.

Answer 4

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...

Answer 5

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...

Answer 6

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 :-)

Answer 7

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...

Answer 8

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>

Answer 9

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

Answer 10

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...

Answer 11

User-1199946673 posted

Try

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

Answer 12

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 :-)

Answer 13

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...

Answer 14

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!

Answer 15

User551462331 posted

put this towards end in page_load event...

 year_name.SelectedItem.Value = DateTime.Now.Year

hope this helps...

Answer 16

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...

Answer 17

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

Answer 18

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...

Answer 19

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

Answer 20

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...

Answer 21

User1225369314 posted

That was it thanks!!