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!
<%@ 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>

Open in new window

matthallettAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

guru_samiCommented:
1:declare a variable global to class
2: set this variable in recentmessages_ItemDataBound
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_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()
            string pid = DirectCast(e.Item.FindControl("hfId"), 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
0
matthallettAuthor Commented:
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!
0
matthallettAuthor Commented:
Worked out the FindControl:

Dim lv_comments As ListView = DirectCast(lv_recentmessages.FindControl("lv_comments"), ListView)

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.
0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

guru_samiCommented:
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(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs) Handles  ads_comments.Selecting
        e.Command.Parameters("postid").Value = postid
    End Sub
0
matthallettAuthor Commented:
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:
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>

Open in new window

0
guru_samiCommented:
try this:
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
           Dim lvcomments As ListView = CType(e.Item.FindControl("lv_comments"),ListView)
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

0
matthallettAuthor Commented:
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?
0
guru_samiCommented:
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"?
0
guru_samiCommented:
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?
0
matthallettAuthor Commented:
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_ItemDataBound. But then it repeats over and over again. Not really sure what I'm looking for.

"i think you will have to nest the databound controls..."

Can you elaborate?
0
guru_samiCommented:
Ok that means it is not able to find the post id.
You have Value='<%#Eval("Post_ID") %>' :
            <asp:Label ID="hdn_postid" runat="server" Value='<%#Eval("Post_ID") %>' />
It should be ----- Text='<%#Eval("Post_ID") %>':
           <asp:Label ID="hdn_postid" runat="server" Text='<%#Eval("Post_ID") %>' />

-->"i think you will have to nest the databound controls..."
That was an vague answer to your question: " Is there an easier way, i.e. not use a nested listview?
By that I meant you will need nested ListView or other similare DataBound controls like nested repeaters or combinations of two.

-->Doing it now, set one for each of the lines in the two codebehind sections.
 mainly you needed to set breakpoints on two statements:    
  1:  postid = Convert.ToInt32(pid)
   2:  e.Command.Parameters("postid").Value = postid

--> Not really sure what I'm looking for.
What you need to look for is
1: The execution hits and stops on those two statements
2: Check that value for "pid" in 1 and "postid" in 2. You can do that by mouse hover on those variables.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
matthallettAuthor Commented:
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.
0
guru_samiCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.