Solved

count total records from another table related in datalist

Posted on 2010-11-09
7
689 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

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 …
In this article I will describe the Copy Database Wizard 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.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 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

21 Experts available now in Live!

Get 1:1 Help Now