Nested Listviews (vb.net and access databases)
Hey guys,
For a module at university, I have to create a social networking site - basically a simplified version of Facebook.
I am trying to display recent posts and comments made to those posts. Showing the posts is easy - I use a listview using an accessdatasource. I am now trying to make the comments show up underneath them in a nested listview, and having spent all of yesterday and half of today trying, I've gotten nowhere.
I've looked all over the internet, and found some examples of what I want to do. The problem though, is that I have to use VB.net and an Access Database. None of the examples I've found have used them, and I have been unsuccessful in converting them for various reasons, mainly because I've only been using .NET for a month or so and struggle to correct the errors created through the conversion.
Below is my code. I have two accessdatasources, one for the messages, one for the comments. The shared database value is "Post_ID".
Any help would be very much appreciated, I am completely stumped by this! And apologies if there is a solution on this website, I've only just signed up and had a quick look but there didn't appear to be one covering exactly what I needed. Will keep looking though whilst I wait for a reply!
For a module at university, I have to create a social networking site - basically a simplified version of Facebook.
I am trying to display recent posts and comments made to those posts. Showing the posts is easy - I use a listview using an accessdatasource. I am now trying to make the comments show up underneath them in a nested listview, and having spent all of yesterday and half of today trying, I've gotten nowhere.
I've looked all over the internet, and found some examples of what I want to do. The problem though, is that I have to use VB.net and an Access Database. None of the examples I've found have used them, and I have been unsuccessful in converting them for various reasons, mainly because I've only been using .NET for a month or so and struggle to correct the errors created through the conversion.
Below is my code. I have two accessdatasources, one for the messages, one for the comments. The shared database value is "Post_ID".
Any help would be very much appreciated, I am completely stumped by this! And apologies if there is a solution on this website, I've only just signed up and had a quick look but there didn't appear to be one covering exactly what I needed. Will keep looking though whilst I wait for a reply!
<%@ Page Language="VB" MasterPageFile="~/master_normal.master" AutoEventWireup="false" CodeFile="home.aspx.vb" Inherits="index" title="Visage // Home" %>
<asp:Content ID="Content1" ContentPlaceHolderID="cph_head" Runat="Server">
<script>
Imports System.Data.OleDb
Imports System.Collections
Imports System.Web.UI
Imports System.Web
Imports System.Collections.Generic
Partial Class index
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'user must be logged in
If Session("Email") = "" Then
Response.Redirect("index.aspx")
End If
Dim thisuserid As Integer
thisuserid = Session("User_ID")
'lv_recentmessages.FindControl(p_test.FindControl("p_test"))
ads_friendrequests.SelectCommand = "SELECT * FROM [qryfriendrequests] WHERE ([User_ID] = " & thisuserid & " AND [Who_Added] <> " & thisuserid & ")"
ads_recentmessages.SelectCommand = "(SELECT tblposts.User_ID, tblposts.Post_ID, tblposts.Post, tblposts.TimeStamp, tblusers.First_Name, tblusers.Surname FROM (tblfriendships INNER JOIN tblposts ON tblfriendships.Friend_ID = tblposts.User_ID) INNER JOIN tblusers ON tblfriendships.Friend_ID = tblusers.User_ID WHERE ((tblfriendships.User_ID)=" & Session("User_ID") & " AND tblfriendships.Status=1)) UNION(SELECT tblposts.User_ID, tblposts.Post_ID, tblposts.Post, tblposts.TimeStamp, tblusers.First_Name, tblusers.Surname FROM (tblposts INNER JOIN tblusers ON tblposts.User_ID = tblusers.User_ID) WHERE tblposts.User_ID = " & Session("User_ID") & ") ORDER BY tblposts.TimeStamp DESC;"
'onfocus/onblur effects for various fields
tb_postmessage.Attributes.Add("onfocus", "if(this.value.toLowerCase() == 'enter your message') this.value = ''; this.className='a';")
tb_postmessage.Attributes.Add("onblur", "if(this.value.toLowerCase() == '') this.value = 'Enter your message'; this.className='d';")
'end onfocus/onblur effects
End Sub
Protected Sub btn_postmessage_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_postmessage.Click
'This SQL string adds the new message
Dim strSQL As String
strSQL = "INSERT INTO [tblposts] ([User_ID], [Post], [TimeStamp]) VALUES ('" _
& Session("User_ID") & "', '" & tb_postmessage.Text & "' , '" & Date.Now & "')"
Dim PostMessageDataConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/database.mdb"))
Dim PostMessageCommand As New OleDbCommand(strSQL, PostMessageDataConn)
PostMessageDataConn.Open()
PostMessageCommand.ExecuteNonQuery()
PostMessageDataConn.Close()
Response.Redirect(Request.RawUrl)
End Sub
Protected Sub lv_recentmessages_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ListViewItemEventArgs) Handles lv_recentmessages.ItemDataBound
If e.Item.ItemType = ListViewItemType.DataItem Then
lv_recentmessages.Items.Clear()
ads_comments.SelectParameters("postid").DefaultValue = DirectCast(e.Item.FindControl("hfId"), Label).Text
End If
End Sub
End Class
</script>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="cph_left" Runat="Server">
<h1>Welcome <%=Session("First_Name")%> <%=Session("Surname")%></h1>
<p><strong>Post Message | Post Image</strong></p>
<asp:Panel runat="server" DefaultButton="btn_postmessage" >
<p><asp:TextBox runat="server" ID="tb_postmessage" Text="Enter your message" CssClass="d" /></p>
<p><asp:Button runat="server" id="btn_postmessage" Text="Post Message" /></p>
</asp:Panel>
<asp:ListView ID="lv_recentmessages" runat="server" DataSourceID="ads_recentmessages" DataKeyNames="Post_ID" >
<ItemTemplate>
<asp:Label ID="hfId" runat="server" Value='<%#Eval("Post_ID") %>' />
<p><asp:Literal ID="Literal1" runat="server" Text='<%# Eval("Post_ID") %>' /></p>
<p><asp:Literal ID="Label1" runat="server" Text='<%# Eval("First_Name") %>' /> <asp:Literal ID="Label2" runat="server" Text='<%# Eval("Surname") %>' /></p>
<p><asp:Literal ID="PostLabel" runat="server" Text='<%# Eval("Post") %>' /></p>
<p><asp:Literal ID="TimeStampLabel" runat="server" Text='<%# Eval("TimeStamp") %>' /></p>
<p><asp:Literal ID="p_Test" runat="server" /></p>
<asp:ListView ID="lv_comments" runat="server" DataSourceID="ads_comments" DataKeyNames="Post_ID" >
<ItemTemplate>
<p><asp:Literal ID="Commenter" runat="server" Text='<%# Eval("First_Name") %>' /> <asp:Literal ID="Label2" runat="server" Text='<%# Eval("Surname") %>' /></p>
<p><asp:Literal ID="Comment" runat="server" Text='<%# Eval("Comment") %>' /></p>
</ItemTemplate>
<LayoutTemplate>
<p><strong>Recent Comments</strong></p>
<table ID="itemPlaceholderContainer" runat="server" border="0" style="">
<tr ID="itemPlaceholder" runat="server">
</tr>
</table>
</LayoutTemplate>
</asp:ListView>
<hr />
</ItemTemplate>
<LayoutTemplate>
<h2>Recent Postings</h2>
<table ID="itemPlaceholderContainer" runat="server" border="0" style="">
<tr ID="itemPlaceholder" runat="server">
</tr>
</table>
</LayoutTemplate>
</asp:ListView>
<p><asp:AccessDataSource ID="ads_recentmessages" runat="server" DataFile="~/App_Data/database.mdb" /></p>
<p><asp:AccessDataSource ID="ads_comments" runat="server" DataFile="~/App_Data/database.mdb"
SelectCommand = "SELECT tblcomments.*, tblusers.First_Name, tblusers.Surname FROM (tblusers INNER JOIN (tblposts INNER JOIN tblcomments ON tblposts.Post_ID = tblcomments.Post_ID) ON tblusers.User_ID = tblcomments.User_ID) WHERE tblcomments.Post_ID= @postid;"
>
<SelectParameters>
<asp:ControlParameter ControlID="lv_recentmessages" Name="postid" PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
</asp:AccessDataSource></p>
</asp:Content>
ASKER
Thanks for your reply.
I've added that code, and a few problems came up.
It didn't recognise lv_comments (due to "lv_comments.DataBind"), seeing as it's nested.
Added "Dim lv_comments As ListView", but I believe I need a FindControl thing after that? I'm not sure on the correct way of doing this.
It also said that postid in the two subs wasnt declared. How can I make it cross over? Because surely if I redeclare it as an integer, it will overwrite the value?
I've also got an error saying:
" An OleDbParameter with ParameterName '@postid' is not contained by this OleDbParameterCollection."
Am I missing an Import? No idea what this means!
I've added that code, and a few problems came up.
It didn't recognise lv_comments (due to "lv_comments.DataBind"), seeing as it's nested.
Added "Dim lv_comments As ListView", but I believe I need a FindControl thing after that? I'm not sure on the correct way of doing this.
It also said that postid in the two subs wasnt declared. How can I make it cross over? Because surely if I redeclare it as an integer, it will overwrite the value?
I've also got an error saying:
" An OleDbParameter with ParameterName '@postid' is not contained by this OleDbParameterCollection."
Am I missing an Import? No idea what this means!
ASKER
Worked out the FindControl:
Dim lv_comments As ListView = DirectCast(lv_recentmessag
The postid thing was just me being stupid. I had it in Page_load, which is why it was unrecognised in the other subs. Didn't realise I could do that, that should help me clear up some code elsewhere on my site.
So that leaves just the OleDbParameter bug.
Dim lv_comments As ListView = DirectCast(lv_recentmessag
The postid thing was just me being stupid. I had it in Page_load, which is why it was unrecognised in the other subs. Didn't realise I could do that, that should help me clear up some code elsewhere on my site.
So that leaves just the OleDbParameter bug.
try changing the SelectParamter for lv_comments like:
<SelectParameters>
<asp:Parameter Name="postid" Type="Int32" />
</SelectParameters>
and remove '@' from @postid like below:
Protected Sub ads_comments_Selecting(ByV
e.Command.Parameters("post
End Sub
<SelectParameters>
<asp:Parameter Name="postid" Type="Int32" />
</SelectParameters>
and remove '@' from @postid like below:
Protected Sub ads_comments_Selecting(ByV
e.Command.Parameters("post
End Sub
ASKER
No errors, but I'm not seeing any comments underneath each post on my page.
I do have lv_comments.Databind commented out though, as it was giving me an error. Maybe it needs that?
Here's an update on my code:
I do have lv_comments.Databind commented out though, as it was giving me an error. Maybe it needs that?
Here's an update on my code:
Imports System.Data.OleDb
Imports System.Collections
Imports System.Web.UI
Imports System.Web
Imports System.Collections.Generic
Partial Class index
Inherits System.Web.UI.Page
Dim postid As Integer = 0
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'user must be logged in
If Session("Email") = "" Then
Response.Redirect("index.aspx")
End If
Dim postid As Integer = 0
Dim thisuserid As Integer
thisuserid = Session("User_ID")
ads_friendrequests.SelectCommand = "SELECT * FROM [qryfriendrequests] WHERE ([User_ID] = " & thisuserid & " AND [Who_Added] <> " & thisuserid & ")"
ads_recentmessages.SelectCommand = "(SELECT tblposts.User_ID, tblposts.Post_ID, tblposts.Post, tblposts.TimeStamp, tblusers.First_Name, tblusers.Surname FROM (tblfriendships INNER JOIN tblposts ON tblfriendships.Friend_ID = tblposts.User_ID) INNER JOIN tblusers ON tblfriendships.Friend_ID = tblusers.User_ID WHERE ((tblfriendships.User_ID)=" & Session("User_ID") & " AND tblfriendships.Status=1)) UNION(SELECT tblposts.User_ID, tblposts.Post_ID, tblposts.Post, tblposts.TimeStamp, tblusers.First_Name, tblusers.Surname FROM (tblposts INNER JOIN tblusers ON tblposts.User_ID = tblusers.User_ID) WHERE tblposts.User_ID = " & Session("User_ID") & ") ORDER BY tblposts.TimeStamp DESC;"
'onfocus/onblur effects for various fields
tb_postmessage.Attributes.Add("onfocus", "if(this.value.toLowerCase() == 'enter your message') this.value = ''; this.className='a';")
tb_postmessage.Attributes.Add("onblur", "if(this.value.toLowerCase() == '') this.value = 'Enter your message'; this.className='d';")
'end onfocus/onblur effects
End Sub
Protected Sub btn_postmessage_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_postmessage.Click
'This SQL string adds the new message
Dim strSQL As String
strSQL = "INSERT INTO [tblposts] ([User_ID], [Post], [TimeStamp]) VALUES ('" _
& Session("User_ID") & "', '" & tb_postmessage.Text & "' , '" & Date.Now & "')"
Dim PostMessageDataConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/database.mdb"))
Dim PostMessageCommand As New OleDbCommand(strSQL, PostMessageDataConn)
PostMessageDataConn.Open()
PostMessageCommand.ExecuteNonQuery()
PostMessageDataConn.Close()
Response.Redirect(Request.RawUrl)
End Sub
Protected Sub lv_recentmessages_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ListViewItemEventArgs) Handles lv_recentmessages.ItemDataBound
Dim lv_comments As ListView = DirectCast(lv_recentmessages.FindControl("lv_comments"), ListView)
If e.Item.ItemType = ListViewItemType.DataItem Then
lv_recentmessages.Items.Clear()
Dim pid As String = DirectCast(e.Item.FindControl("hdn_postid"), Label).Text
If Not String.IsNullOrEmpty(pid) Then
postid = Convert.ToInt32(pid)
End If
'lv_comments.DataBind()
End If
End Sub
Protected Sub ads_comments_Selecting(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs) Handles ads_comments.Selecting
e.Command.Parameters("postid").Value = postid
End Sub
End Class
-----------------------
<%@ Page Language="VB" MasterPageFile="~/master_normal.master" AutoEventWireup="false" CodeFile="home.aspx.vb" Inherits="index" title="Visage // Home" %>
<asp:Content ID="Content1" ContentPlaceHolderID="cph_head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="cph_left" Runat="Server">
<h1>Welcome <%=Session("First_Name")%> <%=Session("Surname")%></h1>
<p><strong>Post Message | Post Image</strong></p>
<asp:Panel runat="server" DefaultButton="btn_postmessage" >
<p><asp:TextBox runat="server" ID="tb_postmessage" Text="Enter your message" CssClass="d" /></p>
<p><asp:Button runat="server" id="btn_postmessage" Text="Post Message" /></p>
</asp:Panel>
<asp:ListView ID="lv_recentmessages" runat="server" DataSourceID="ads_recentmessages" DataKeyNames="Post_ID" >
<ItemTemplate>
<asp:Label ID="hdn_postid" runat="server" Value='<%#Eval("Post_ID") %>' />
<p><asp:Literal ID="lit_postid" runat="server" Text='<%# Eval("Post_ID") %>' /></p>
<p><asp:Literal ID="lit_firstname" runat="server" Text='<%# Eval("First_Name") %>' /> <asp:Literal ID="Label2" runat="server" Text='<%# Eval("Surname") %>' /></p>
<p><asp:Literal ID="lit_post" runat="server" Text='<%# Eval("Post") %>' /></p>
<p><asp:Literal ID="lit_timestamp" runat="server" Text='<%# Eval("TimeStamp") %>' /></p>
<asp:ListView ID="lv_comments" runat="server" DataSourceID="ads_comments" DataKeyNames="Post_ID" >
<ItemTemplate>
<p><asp:Literal ID="lit_comfirstname" runat="server" Text='<%# Eval("First_Name") %>' /> <asp:Literal ID="lit_comsurname" runat="server" Text='<%# Eval("Surname") %>' /></p>
<p><asp:Literal ID="lit_comment" runat="server" Text='<%# Eval("Comment") %>' /></p>
</ItemTemplate>
<LayoutTemplate>
<p><strong>Recent Comments</strong></p>
<table ID="itemPlaceholderContainer" runat="server" border="0" style="">
<tr ID="itemPlaceholder" runat="server">
</tr>
</table>
</LayoutTemplate>
</asp:ListView>
<hr />
</ItemTemplate>
<LayoutTemplate>
<h2>Recent Postings</h2>
<table ID="itemPlaceholderContainer" runat="server" border="0" style="">
<tr ID="itemPlaceholder" runat="server">
</tr>
</table>
</LayoutTemplate>
</asp:ListView>
<p><asp:AccessDataSource ID="ads_recentmessages" runat="server" DataFile="~/App_Data/database.mdb" /></p>
<p><asp:AccessDataSource ID="ads_comments" runat="server" DataFile="~/App_Data/database.mdb"
SelectCommand = "SELECT tblcomments.*, tblusers.First_Name, tblusers.Surname FROM (tblusers INNER JOIN (tblposts INNER JOIN tblcomments ON tblposts.Post_ID = tblcomments.Post_ID) ON tblusers.User_ID = tblcomments.User_ID) WHERE tblcomments.Post_ID = ?;" >
<SelectParameters>
<asp:Parameter Name="postid" Type="Int32" />
</SelectParameters>
</asp:AccessDataSource></p>
</asp:Content>
try this:
Protected Sub lv_recentmessages_ItemData
Dim lv_comments As ListView = DirectCast(lv_recentmessag
If e.Item.ItemType = ListViewItemType.DataItem Then
lv_recentmessages.Items.Cl
Dim pid As String = DirectCast(e.Item.FindCont
If Not String.IsNullOrEmpty(pid) Then
postid = Convert.ToInt32(pid)
End If
Dim lvcomments As ListView = CType(e.Item.FindControl("
If (Not (lvcomments) Is Nothing) Then
lvcomments.DataBind
End If
End If
Note: Set breakpoints in code above and see if you are getting right postid and also lvcomments.DataBind is called.
End Sub
Protected Sub lv_recentmessages_ItemData
Dim lv_comments As ListView = DirectCast(lv_recentmessag
If e.Item.ItemType = ListViewItemType.DataItem Then
lv_recentmessages.Items.Cl
Dim pid As String = DirectCast(e.Item.FindCont
If Not String.IsNullOrEmpty(pid) Then
postid = Convert.ToInt32(pid)
End If
Dim lvcomments As ListView = CType(e.Item.FindControl("
If (Not (lvcomments) Is Nothing) Then
lvcomments.DataBind
End If
End If
Note: Set breakpoints in code above and see if you are getting right postid and also lvcomments.DataBind is called.
End Sub
ASKER
That didn't work either :( The page loads, but no comments are displayed under the posts.
Is this the best way to do what I want to do? Is there an easier way, i.e. not use a nested listview?
Is this the best way to do what I want to do? Is there an easier way, i.e. not use a nested listview?
i think you will have to nest the databound controls...
-->That didn't work either
Did you set the breakpoints and see if lvComments is datbound?
Are you getting right "postid"?
-->That didn't work either
Did you set the breakpoints and see if lvComments is datbound?
Are you getting right "postid"?
Did you try running this query directly in database?
SelectCommand = "SELECT tblcomments.*, tblusers.First_Name, tblusers.Surname FROM (tblusers INNER JOIN (tblposts INNER JOIN tblcomments ON tblposts.Post_ID = tblcomments.Post_ID) ON tblusers.User_ID = tblcomments.User_ID) WHERE tblcomments.Post_ID= @postid;"
Is it returning correct results with appropriate postid?
SelectCommand = "SELECT tblcomments.*, tblusers.First_Name, tblusers.Surname FROM (tblusers INNER JOIN (tblposts INNER JOIN tblcomments ON tblposts.Post_ID = tblcomments.Post_ID) ON tblusers.User_ID = tblcomments.User_ID) WHERE tblcomments.Post_ID= @postid;"
Is it returning correct results with appropriate postid?
ASKER
If I change
Dim postid As Integer = 0
to
Dim postid As Integer = 1
at the top of my code-behind, it displays comments (post 1's comments for every post), and the query works in my database. So the select command is fine, it just isn't picking up the Post_ID.
I didn't know what a breakpoint does, so I didn't try before. Doing it now, set one for each of the lines in the two codebehind sections. What is interesting is that it is dealing with ads_comments_Selecting before lv_recentmessages_ItemData
"i think you will have to nest the databound controls..."
Can you elaborate?
Dim postid As Integer = 0
to
Dim postid As Integer = 1
at the top of my code-behind, it displays comments (post 1's comments for every post), and the query works in my database. So the select command is fine, it just isn't picking up the Post_ID.
I didn't know what a breakpoint does, so I didn't try before. Doing it now, set one for each of the lines in the two codebehind sections. What is interesting is that it is dealing with ads_comments_Selecting before lv_recentmessages_ItemData
"i think you will have to nest the databound controls..."
Can you elaborate?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I don't know why, but that worked!
Thank you very much.
Btw, there's still functionality I need to add to this (being able to add comments etc), so I may repost here if anything comes up.
Thank you very much.
Btw, there's still functionality I need to add to this (being able to add comments etc), so I may repost here if anything comes up.
as said the cluprit was Value='<%#Eval("Post_ID") %>' as Value is property for hiddenfield control and and Label.
-->I may repost here if anything comes up.
Sure....but start a new thread with reference to this thread, so you get attention from other experts.
-->I may repost here if anything comes up.
Sure....but start a new thread with reference to this thread, so you get attention from other experts.
2: set this variable in recentmessages_ItemDataBou
3: bind the comments LV
4: set ads_comments - postid parameter in ads_comments onselecting method
Dim postid As Integer = 0
Protected Sub lv_recentmessages_ItemData
If e.Item.ItemType = ListViewItemType.DataItem Then
lv_recentmessages.Items.Cl
string pid = DirectCast(e.Item.FindCont
If Not String.IsNullOrEmpty(pid) Then
postid = Convert.ToInt32(pid)
End if
lv_comments.DataBind()
End If
End Sub
Protected Sub ads_comments_Selecting(ByV
e.Command.Parameters("@pos
End Sub