Improve company productivity with a Business Account.Sign Up

x
?
Solved

count total records from another table related in datalist

Posted on 2010-11-09
7
Medium Priority
?
794 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.

579 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