I am using following cod in my aspnetvbsql web to display Topics of the blogdefaultaspx page ltaspGridView ID34GridView134 runat34server34 AutoGenerateColumns34false34 class34gridview34 ShowHeader34false34 GridLines34None34gtltColumnsgtltaspTemplateField ShowHeader34false34gtltItemTemplategtlttrgtlttdgtltdiv class34BlogHead34gtlth2gtlta href39lt Eval34Id34 34detailsaspxId03

Hi baijuepPlease refer below SQL Query and apply according to your requirementCREATE TABLE BlogPost ID INT IDENTITY Title VARCHARMAXContent VARCHARMAXAuthor VARCHAR100BlogDate DATETIMEINSERT INTO BlogPost VALUES39Biju3939s First blog3939Test3939Biju EP39392016111139INSERT INTO BlogPost VALUES39Simulator3939Test lines3939Maj RK Narban39392016111139INSERT INTO BlogPost VALUES39Lethal Drones3939This page is rese3939Maj DS Misra39392016111139 CREATE TABLE CommentTableID INT IDENTITYComment VARCHARMAXName VARCHAR100Date DATETIMEBlogId INTINSERT INTO CommentTable VALUES39Good line3939Sanath393920161111391INSERT INTO CommentTable VALUES39This is a good blog3939Sanu Kumar393920161112393INSERT INTO CommentTable VALUES39This is a good blog3939Sanu Kumar393920161112394INSERT INTO Comment

Display Comments count for particular blog in ASP.Net

baijuep

SOLVED CLOSED ACTIVE ANSWERED
User: baijuep
Posted: on Nov 18, 2016 07:41 AM
Forum: ASP.Net Web Forms
Answer: 1
Views: 5924

I am using following cod in my asp.net+vb+sql web to display Topics of the blog.

default.aspx page

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" class="gridview" ShowHeader="false" GridLines="None">
<Columns>
<asp:TemplateField ShowHeader="false">
<ItemTemplate>
<tr>
<td>
<div class="BlogHead">
<h2><a href='<%# Eval("Id", "details.aspx?Id={0}") %>' class="BlogHead">
<asp:Label ID="Label1" runat="server" Text='<%#Eval("Title") %>'></asp:Label></a></h2>
    
</div>
<div class="post_meta">
    <span class="post_author blackLink nocursor"><asp:Label ID="Label2" runat="server" Text='<%#Eval("Author") %>'></asp:Label>,</span>
    <span class="date blackLink nocursor"><asp:Label ID="Label11" runat="server" Text='<%#Eval("BlogDate") %>'></asp:Label></span>
</div>
<br />
<div id="blbodythumb" style="text-align:justify;">
<p><asp:Label ID="Label100" runat="server" Text='<%#Eval("Content") %>' ></asp:Label></p></div><hr class="style-one" />
</td>
</tr>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<EmptyDataTemplate>
No data
</EmptyDataTemplate>
</asp:GridView>

default.aspx.vb page

Private Sub BindDetailsView()
        Dim constr As String = ConfigurationManager.ConnectionStrings("ourdb").ConnectionString
        Using con As New SqlConnection(constr)
            Using cmd As New SqlCommand("select * from BlogPost order by BlogDate desc")
                Using sda As New SqlDataAdapter()
                    cmd.Connection = con
                    sda.SelectCommand = cmd
                    Using dt As New DataTable()
                        sda.Fill(dt)
                        GridView1.DataSource = dt
                        GridView1.DataBind()
                    End Using
                End Using
            End Using
        End Using
    End Sub

I want to display number of replies recd (count in numbers) for the topic on the side of the topic header.

Main table is BlogPost

id Title [Content] author blogdate

Comments are stored in a table named Comments and the table is as under

ID Comment Name Date Blogid

AnandM

0 6

Reply
ANSWER
User: AnandM
Replied: on Nov 18, 2016 10:01 AM
Modified: on Nov 18, 2016 10:03 AM
Report

Hi baijuep,

Please refer below SQL Query and apply according to your requirement

CREATE TABLE #BlogPost (ID INT IDENTITY, Title VARCHAR(MAX),Content VARCHAR(MAX),Author VARCHAR(100),BlogDate DATETIME)
INSERT INTO #BlogPost VALUES('Biju''s First blog','Test','Biju EP','2016/11/11')
INSERT INTO #BlogPost VALUES('Simulator','Test lines','Maj RK Narban','2016/11/11')
INSERT INTO #BlogPost VALUES('Lethal Drones','This page is rese.....','Maj DS Misra','2016/11/11')
 
CREATE TABLE #CommentTable(ID INT IDENTITY,Comment VARCHAR(MAX),Name VARCHAR(100),[Date] DATETIME,BlogId INT)
INSERT INTO #CommentTable VALUES('Good line','Sanath','2016/11/11',1)
INSERT INTO #CommentTable VALUES('This is a good blog','Sanu Kumar','2016/11/12',3)
INSERT INTO #CommentTable VALUES('This is a good blog','Sanu Kumar','2016/11/12',4)
INSERT INTO #CommentTable VALUES('This is a good blog','Sanu Kumar','2016/11/12',3)
INSERT INTO #CommentTable VALUES('This is a good blog','Sanu Kumar','2016/11/12',3)
INSERT INTO #CommentTable VALUES('This is a good blog','Sanu Kumar','2016/11/12',3)
INSERT INTO #CommentTable VALUES('You need not have....','Baiju','2016/11/15',3)
 
SELECT B.ID,Title,Content,Author,BlogDate,ISNULL((SELECT COUNT(BlogId) FROM #CommentTable WHERE BlogId = B.ID GROUP BY BlogId),'0')Replies
FROM #BlogPost B

DROP TABLE #BlogPost
DROP TABLE #CommentTable

OutPut

ID	Title	Content	Author	BlogDate	Replies
1	Biju's First blog	Test	Biju EP	11/11/16 0:00	1
2	Simulator	Test lines	Maj RK Narban	11/11/16 0:00	0
3	Lethal Drones	This page is rese.....	Maj DS Misra	11/11/16 0:00	5

Display Comments count for particular blog in ASP.Net

Company

Explore

Follow us

Contact Us