How to display data by date in nested listview

Question

I have 2 nested listview... each with different data source ... i want to have something like this:

February 2012

• 10: article title
• 04: article title

January 2012

• 20: article title
• 24: article title

But now I have

February 2012

• 10: article title

February 2012

• 04: article title

... and so on ... my code is something like this:

<asp:ListView ID="lvMonthYear" runat="server" DataSourceID="SqlDataSource1"
    ItemPlaceholderID="PlaceHolder2" DataKeyNames="MnthYr" 
    onitemdatabound="lvMonthYear_ItemDataBound1">
    <ItemTemplate>
        <h1>
          <asp:Label ID="lblMonthYear" runat="server" Text='<%# Eval("MnthYr") %>'/> </h1>
         <asp:ListView ID="lvDayArticle" runat="server" DataKeyNames="artid" ItemPlaceholderID="PlaceHolder2" >
            <ItemTemplate>
                <li runat="server">
                    <asp:Label ID="lblDay" runat="server" Text='<%# Eval("artdate","{0:dd}") %>' />:
                    <asp:LinkButton ID="lblTitle" runat="server" Text='<%# Eval("title") %>' PostBackUrl='<%#Bind("artid","Articol.aspx?art={0}") %>'
                        CssClass="LinkButton1" />
                </li>
            </ItemTemplate>
            <LayoutTemplate>
                <ul>
                    <asp:PlaceHolder runat="server" ID="PlaceHolder2" />
                </ul>
            </LayoutTemplate>
             <EmptyDataTemplate>
                 Nu există niciun articol.<br />
             </EmptyDataTemplate>
        </asp:ListView>

    </ItemTemplate>
     <EmptyDataTemplate>
                 Nu există niciun articol.<br />
             </EmptyDataTemplate>
    <LayoutTemplate>
        <asp:PlaceHolder runat="server" ID="PlaceHolder2" />
    </LayoutTemplate>
</asp:ListView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ASConnectionString %>"
    SelectCommand="SELECT DISTINCT (DATENAME(MONTH, [artdate]) + ' ' + CONVERT (varchar, YEAR([artdate]))) AS [MnthYr] FROM as_Articles ORDER BY [MnthYr] DESC">
 </asp:SqlDataSource>

and code behind:

protected DataSet GetArticleds(string Month, string Year)
     {
         DataSet articleDataSet=new DataSet();
         ConnectionStringSettings cs;
         cs = ConfigurationManager.ConnectionStrings["ASConnectionString"];
         String connString = cs.ConnectionString;
         SqlConnection dbConnection = new SqlConnection(connString);
         string query = "SELECT [artid], [title], [artdate] FROM [as_Articles] WHERE DATENAME(MONTH,[artdate])=@strMonth AND CONVERT(VARCHAR,YEAR([artdate]))=@strYear ORDER BY [artdate] DESC";
         SqlCommand dbCommand = new SqlCommand(query, dbConnection);
         dbCommand.Parameters.Add(new SqlParameter("@strMonth", Month));
         dbCommand.Parameters.Add(new SqlParameter("@strYear", Year));
         SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(dbCommand);
         try
         {
             sqlDataAdapter.Fill(articleDataSet);
         }
         catch { }
             return articleDataSet;
     }


 protected void lvMonthYear_ItemDataBound1(object sender, ListViewItemEventArgs e)
 {
     if (e.Item.ItemType == ListViewItemType.DataItem)
     {
         ListViewDataItem currentItem = (ListViewDataItem)e.Item;
         DataKey currentDataKey = this.lvMonthYear.DataKeys[currentItem.DataItemIndex];
         ListView lvDayArticle = (ListView)currentItem.FindControl("lvDayArticle");

         string strMonthYear = Convert.ToString(currentDataKey["MnthYr"]);

         string strMonth = strMonthYear.Split(' ')[0];
         string strYear = strMonthYear.Split(' ')[1];
         lvDayArticle.DataSource = GetArticleds(strMonth, strYear);
         lvDayArticle.DataBind();

     }
 }

Answer 1

protected void lvMonthYear_ItemDataBound(object sender, ListViewItemEventArgs e)
{
    if (e.Item.ItemType == ListViewItemType.DataItem)
    {
        ListViewDataItem currentItem= (ListViewDataItem)e.Item;
        DataKey currentDataKey = this.lvMonthYear.DataKeys[currentItem.DataItemIndex];
        ListView lvDayArticle = (ListView)currentItem.FindControl("lvDayArticle");
        string strMonthYear = Convert.ToString(currentDataKey["MnthYr"]);
        string strMonth = strMonthYear.Split(' ')[0];
        string strYear = strMonthYear.Split(' ')[1];
        lvDayArticle.DataSource = GetArticleds(strMonth,strYear);
        lvDayArticle.DataBind();
    }
}

protected DataSet GetArticleds(string Month, string Year)
{
    string strCommand="SELECT [artid], [title] FROM [as_Articles] WHERE DATENAME(MONTH,[artdate])=@strMonth AND YEAR([artdate])=@strYear";
    List<SqlParameter> sqlparam = new List<SqlParameter>();
    sqlparam.Add(new SqlParameter("@strMonth", SqlDbType.VarChar, 3) { Value = Month });
    sqlparam.Add(new SqlParameter("@strYear", SqlDbType.SmallInt) { Value = Year });
    SqlConnection con = new SqlConnection("ConnectionString");
    SqlCommand cmd = new SqlCommand(strCommand, con);
    cmd.Parameters.AddRange(sqlparam.ToArray());
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    using (DataSet ds = new DataSet())
    {
        da.Fill(ds);
        return ds;
    }
}

Hope you will get it from now :)

Answer 2

Using this approach you can't achieve your requirement.

I would like to provide some steps to go on with your requirement.

1. Add a DataKey as [MnthYr] for the Outer ListView. Then change the outer SELECT Query such that you will get all the distinct Article Months something similar to below query

   SELECT DISTINCT DATENAME(MONTH,[artdate])+' '+CONVERT(VARCHAR,YEAR([artdate])) AS [MnthYr] FROM [as_Articles] ORDER BY [artdate] DESC

2. Then add OnItemDataBound Event for the Outer ListView. Get the instance of the inner List View and DataKey value of the current Outer ListView item. Using that you can write a Method of get the Article details by passing Month and Year. Then Bind the inner List View.

This would surely get the requirement done.