?
Solved

count total records from another table related in datalist

Posted on 2010-11-09
7
Medium Priority
?
749 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

718 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