Solved

count total records from another table related in datalist

Posted on 2010-11-09
7
700 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now