Solved

count total records from another table related in datalist

Posted on 2010-11-09
7
714 Views
Last Modified: 2012-05-10
i have 2 tables, one is a_category and one is article,  a_category.id = article.a_categoryid

article table contains article based on a_category table.

if article eg. c# contains 1 post, in the datalist for the  <%# DataBinder.Eval(Container.DataItem, "countacid")%>
it will show 1 post in it.
<asp:Datalist ID="dlarticle" runat="server" Height="33px" Width="600px" 
                                                    onitemdatabound="dlarticle_ItemDataBound">
                                                            <ItemTemplate>
                                                            <fieldset>
                                                            <table>
                                                                <tr>
                                                                    <td width="20px" background="images/round_rectangletopleft.png"></td>
                                                                    <td></td>
                                                                    <td width="20px" background="images/round_rectangletopright.png"></td>
                                                                </tr>
                                                                <tr>
                                                                    <td width="20px" background="images/round_rectangleleft.png"></td>
                                                                    <td>
                                                                    <div style="float:left; padding-left:10px">
                                                                    
                                                                    <%# DataBinder.Eval(Container.DataItem, "a_categoryname")  %>
                                                                    
                                                                    <asp:Label ID="lblid" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "id") %>' Visible="false" />  

                                                                    
                                                                    </div>

                                                                    <div style="padding-left:100px">
                                                                    
                                                                    <%# DataBinder.Eval(Container.DataItem, "a_desc") %>
                                                                    
                                                                    

                                                                    </div>

                                                                     <div style="padding-left:150px">
                                                                    
                                                                    <%# DataBinder.Eval(Container.DataItem, "countacid")%>
                                                                    
                                                                    

                                                                    </div>


                                                                    
                                                                    <div style="float:left;padding-left:650px">
                                                                    <HeaderTemplate>
                                                                        View
                                                                    </HeaderTemplate>

                                                                    <ItemTemplate>
                        
                                                                        <asp:LinkButton ID="lbCreatePost" runat="server" 
                                                                                CommandName="CREATEPOST" CssClass="note" CommandArgument='<%#
                                                                                DataBinder.Eval(Container.DataItem, "id") %>'></asp:LinkButton>
                                                                                
                                                                                
                                                                               
                                                                    </ItemTemplate>
                                                                    </div>
                                                                    <div style="float:left;padding-left:720px;">
                                                                    <HeaderTemplate>
                                                                        Create
                                                                    </HeaderTemplate>

                                                                    <ItemTemplate>
                                                                    <asp:LinkButton ID="lbEditPost" runat="server" 
                                                                                CommandName="EDITPOST" CssClass="editnote" CommandArgument='<%#
                                                                                DataBinder.Eval(Container.DataItem, "id") %>'></asp:LinkButton>
                                                                    </ItemTemplate>
                                                                    </div>

                                                                    </td>
                                                                    <td width="20px" background="images/round_rectangleright.png"></td>
                                                                </tr>
                                                                <tr>
                                                                    <td width="20px" background="images/round_rectanglebottomleft.png"></td>
                                                                    <td></td>
                                                                    <td width="20px" background="images/round_rectanglebottomright.png"></td>
                                                                </tr>
                                                            </table>

                                                             </fieldset>
                                                           </ItemTemplate>
                                                        </asp:Datalist>

Open in new window

0
Comment
Question by:doramail05
  • 4
  • 3
7 Comments
 
LVL 29

Expert Comment

by:David H.H.Lee
ID: 34099584
Hi doramail05,
So, what is your exact problem? If just need to output the total records of datalist, you can try DatalistName.Rows.Count.
0
 
LVL 1

Author Comment

by:doramail05
ID: 34100084
was thinking if works this method
code-behind
-----------
 protected void dlarticle_ItemDataBound(object sender, DataListItemEventArgs e)
    {
        if (e.Item.ItemType == ListItemType.Item ||
         e.Item.ItemType == ListItemType.AlternatingItem)
        {
            Label _lblid = (Label)e.Item.FindControl("lblid");

            if (_lblid.Text != String.Empty)
            {
                using (SqlConnection sqlconn = new SqlConnection(strconnstring))
                {
                    DataTable dt = new DataTable();
                    string strSQL = "Select COUNT(*) as countacid from article WHERE a_categoryid = " + Convert.ToInt32(_lblid.Text);
                    SqlDataAdapter adpt = new SqlDataAdapter(strSQL, sqlconn);
                    adpt.Fill(dt);

                    if (dt.Rows.Count > 0)
                    {
                        // Display countacid in <%# DataBinder.Eval(Container.DataItem, "countacid")%>                                                              

                    }

                }
            }
        }


    }
aspx
----
<div style="padding-left:150px">
                                                                 <%# DataBinder.Eval(Container.DataItem, "countacid")%>                                                                </div>

Open in new window

0
 
LVL 29

Accepted Solution

by:
David H.H.Lee earned 500 total points
ID: 34108607
If you just want to output the total records from the selected article by category id, try this simplified code:
....//follow previous code until this line ...


 using (SqlConnection sqlconn = new SqlConnection(strconnstring))
                {
                    DataTable dt = new DataTable();
                    string strSQL = "Select COUNT(*) as countacid from article WHERE a_categoryid = " + Convert.ToInt32(_lblid.Text);
                    SqlDataAdapter adpt = new SqlDataAdapter(strSQL, sqlconn);
                    adpt.Fill(dt);

                    Label _lblTotalRec = (Label)e.Item.FindControl("lblTotal");//assign total record to a label called - lblTotal
                    _lblTotalRec.Text=dt.Rows.Count; //display total record by category id
                }

Open in new window

0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 1

Author Comment

by:doramail05
ID: 34108980
it work that way,
but unfortunately, every row's total show (eg. 1) even if that current row is 0 or NULL.  
0
 
LVL 29

Expert Comment

by:David H.H.Lee
ID: 34109073
Try check each returned categoryid(_lblid.Text), it should give the total row respectively.
0
 
LVL 1

Author Comment

by:doramail05
ID: 34109367
not quite understand :/

the id is getting from different id from each row right?

 <asp:Label ID="lblid" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "id") %>' Visible="false" />  
0
 
LVL 1

Author Comment

by:doramail05
ID: 34109386
now got it,


using (SqlConnection sqlconn = new SqlConnection(strconnstring))
            {
                DataTable dt = new DataTable();
                string strSQL = "Select COUNT(*) as countacid from article WHERE a_categoryid = " + Convert.ToInt32(_lblid.Text);
                SqlDataAdapter adpt = new SqlDataAdapter(strSQL, sqlconn);
                adpt.Fill(dt);

                if (dt.Rows.Count > 0)
                {

                    Label _lblTotalRec = (Label)e.Item.FindControl("lblTotal");//assign total record to a label called - lblTotal
                    _lblTotalRec.Text = dt.Rows[0]["countacid"].ToString();//display total record by category id
                }
               
                
            }

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

840 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