Number of comments per article

Hi,
Been working on a wee project for a while now and hitting my head against the desk doesn’t seem to be helping.

I’ve got the connection to the database, I’ve got the latest 5 news articles displaying on my homepage but when I try and show how many comments have been posted on each article I’m hitting a problem.

I’m using C# and SQL database.

On the aspx page I have the following code:

<h1>News headlines</h1>


    <asp:DataList ID="DataList1" runat="server" DataKeyField="newsID"  RepeatColumns="1" OnItemDataBound="item_bound">
            <ItemTemplate>
            <strong>
			     <asp:Image ID="Image1" runat="server" CssClass="newsIcon" ImageUrl= '<%# Eval("img_id") %>'/>
			</strong>

                <b><asp:HyperLink NavigateUrl='<%# "http://mywebsite/news/NewsArticle.aspx?id="+ Eval("newsID") %>' Text='<%# Eval("newsHeader") %>' runat="server" ID="newsHeading"></asp:HyperLink></b>

               <div class="clearit"></div>
			
                <asp:Label ID="newsSummaryLabel" runat="server" Text='<%# Eval("newsSummary") %>'>
                </asp:Label>

                 <a href="http://mywebsite/news/NewsArticle.aspx?id=<%# Eval("newsID") %>">	
                <asp:Label ID="readMoreLabel" runat="server" Text='(Read more..)'>
                </asp:Label></a>

                <br/>

                <asp:Label ID="lblcommentNumber" runat="server" Font-Bold="true" ForeColor="#FF0000" Visible="true">
                </asp:Label>


                <br />


                <em> Date published: <asp:Label ID="newsDateLabel" runat="server" Text='<%# Eval("newsDate","{0:dddd dd MMM yyyy}") %>'></asp:Label></em>
                </ItemTemplate>
        </asp:DataList>

        

This displays the results from :


      string newsIDString = string.Empty;

        if (!this.IsPostBack)
        {
            string selectSQL5 = "SELECT TOP 5 img_id, newsID, newsHeader, newsSummary, newsDate FROM [news_tbl] ORDER BY [newsID] Desc";
            SqlConnection con5 = new SqlConnection(connectionString);
            SqlCommand cmd5 = new SqlCommand(selectSQL5, con5);

            con5.Open();

            DataList1.DataSource = cmd5.ExecuteReader();

            DataList1.DataBind();
            con5.Close();


        }

I then make a new connection so that I can count the comments from the second table and join the comments table with the article table -


  public void item_bound(object sender, DataListItemEventArgs e)
    {

        if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
        {

            conn = new SqlConnection(connectionString);
            DataList1.FindControl("newsHeading");

            commComments = new SqlCommand("SELECT news_tbl.NewsID, Count(comments_tbl.id) FROM news_tbl LEFT JOIN comments_tbl ON news_tbl.newsID = comments_tbl.newsItemID GROUP BY news_tbl.NewsID ORDER BY Count(comments_tbl.newsItemID) DESC", conn);
            commComments.Parameters.Add("@id", SqlDbType.Int).Value = NewIDint;

            conn.Open();
            count = Convert.ToInt32(commComments.ExecuteScalar());
            string iCount = count.ToString();


            Label lb = (Label)e.Item.FindControl("lblCommentNumber");
           // lb.Text = i.ToString();
            lb.Text = iCount;
            i++;
        }


        conn.Close();
    }


But when this runs it just shows me a total number of articles that have comments, not the actual number of comments next to the article.

I need to get the newsID as this is unique to the article and and then query the comments table to see how many comments have the newsID matching the article.

If any of this isn’t clear then let me know and I will see if I can explain further,
Thanks,
Owain.

Found the answer on another forum -
I just needed to change the first select statement rather than have two connections - simply when you know how!

SELECT TOP 5 img_id, newsID, newsHeader, newsSummary, newsDate, (SELECT COUNT(ID) FROM comments_tbl WHERE newsItemID = news_tbl.newsID) as CommentNum FROM [news_tbl] ORDER BY [newsID] Desc