?
Solved

count total records from another table related in datalist

Posted on 2010-11-09
7
Medium Priority
?
774 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 2000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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