There is a problem with my selectcommand. It returns all the forums, not just the news ones. I triend to use Dinstinct but it didnt make a difference. The objective is to pull everything from the forum table that have the same Category (ForumName) but
it is pulling ecerything in the forums table not just the ones with the session variable "news" or the date. Can I have some help with that. Here is the current code.
Default.aspx
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:RTConnectionString %>"
SelectCommand="SELECT * FROM Forum Fm
LEFT JOIN Menu mn ON Mn.MenuName =@MenuName
LEFT JOIN Thread Td ON Td.ForumId = Fm.ForumId
LEFT JOIN Post Pt ON Pt.ThreadId = Td.ThreadId and Pt.DateTime = @Dete "
>
<SelectParameters>
<asp:SessionParameter DefaultValue="News" Name="MenuName"
SessionField="Category" />
<asp:SessionParameter DefaultValue="1/1/2011" Name="Dete" SessionField="Dete" />
</SelectParameters>
</asp:SqlDataSource>
SELECT * FROM Forum Fm
LEFT JOIN Menu mn ON Mn.MenuName =@MenuName
LEFT JOIN Thread Td ON Td.ForumId = Fm.ForumId
LEFT JOIN Post Pt ON Pt.ThreadId = Td.ThreadId and Pt.DateTime = @Dete
The problem here is you aren't telling it to find stuff based on the variable new or the date. You need to write your query more like this:
SELECT * FROM Forum Fm
LEFT JOIN Menu mn ON Mn.MenuName =@MenuName
LEFT JOIN Thread Td ON Td.ForumId = Fm.ForumId
LEFT JOIN Post Pt ON Pt.ThreadId = Td.ThreadId and Pt.DateTime = @Dete
WHERE Td.Title = @News AND Pt.DateTime = @Date
I dont know where the @News and @Date fit in. Let me re-ellaborate. There are two session variables. @dete and @Category. @Category(eg Value= "News"), is a column in the Menu Table, and is there to compare that the data in the forum table and retrieve everything
in the forum Table based on the Category value(eg.News), or MenuName from the Menu Table(The value "News" is a MenuName column in the Menu table). In addition to that we want to pull the ThreadTitle in the Thread Table that matches the specified ForumID with
the Forum Name Value is @Category(eg. News). It is also supposed to pull the Last PostTime that matches the session variable @dete, and UserName of the last Post, of the specified thread, of the specified forum, of the specified MenuName(eg. "News")
Sorry, was giving more of a theory than exact details. So for the dates, I presume that would be your @Dete (referred to as @Date) and the menuname would be @Category (this was that @News I referred to).
SELECT * FROM Forum Fm
LEFT JOIN Menu mn ON Mn.MenuName =@MenuName
LEFT JOIN Thread Td ON Td.ForumId = Fm.ForumId
LEFT JOIN Post Pt ON Pt.ThreadId = Td.ThreadId and Pt.DateTime = @Dete
The problem here is you aren't telling it to find stuff based on the variable new or the date. You need to write your query more like this:
SELECT * FROM Forum Fm
LEFT JOIN Menu mn ON Mn.MenuName =@MenuName
LEFT JOIN Thread Td ON Td.ForumId = Fm.ForumId
LEFT JOIN Post Pt ON Pt.ThreadId = Td.ThreadId and Pt.DateTime = @Dete
WHERE Td.Title = @News AND Pt.DateTime = @Date
Here is the current Query
SELECT * FROM Forum Fm
LEFT JOIN Menu Mn ON Mn.MenuName =@Category
LEFT JOIN Thread Td ON Td.ForumId = Fm.ForumId
LEFT JOIN Post Pt ON Pt.ThreadId = Td.ThreadId and Pt.PostTime = @dete
WHERE Fm.ForumName = @Category AND Pt.PostTime = @dete
Code_warrior...
Member
251 Points
282 Posts
Query problem - returns no details.
Apr 01, 2012 08:38 AM|LINK
There is a problem with my selectcommand. It returns all the forums, not just the news ones. I triend to use Dinstinct but it didnt make a difference. The objective is to pull everything from the forum table that have the same Category (ForumName) but it is pulling ecerything in the forums table not just the ones with the session variable "news" or the date. Can I have some help with that. Here is the current code.
Default.aspx
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RTConnectionString %>" SelectCommand="SELECT * FROM Forum Fm LEFT JOIN Menu mn ON Mn.MenuName =@MenuName LEFT JOIN Thread Td ON Td.ForumId = Fm.ForumId LEFT JOIN Post Pt ON Pt.ThreadId = Td.ThreadId and Pt.DateTime = @Dete " > <SelectParameters> <asp:SessionParameter DefaultValue="News" Name="MenuName" SessionField="Category" /> <asp:SessionParameter DefaultValue="1/1/2011" Name="Dete" SessionField="Dete" /> </SelectParameters> </asp:SqlDataSource>basheerkal
Star
10672 Points
2426 Posts
Re: Query problem - returns no details.
Apr 01, 2012 09:48 AM|LINK
Sorry I am not an expert in sql Queries. Still I can say I don't see a WHERE clause to filter the Data selection..
(Talk less..Work more)
bbcompent1
All-Star
32992 Points
8508 Posts
Moderator
Re: Query problem - returns no details.
Apr 03, 2012 10:21 AM|LINK
Look at your query:
SELECT * FROM Forum Fm
LEFT JOIN Menu mn ON Mn.MenuName =@MenuName
LEFT JOIN Thread Td ON Td.ForumId = Fm.ForumId
LEFT JOIN Post Pt ON Pt.ThreadId = Td.ThreadId and Pt.DateTime = @Dete
The problem here is you aren't telling it to find stuff based on the variable new or the date. You need to write your query more like this:
SELECT * FROM Forum Fm
LEFT JOIN Menu mn ON Mn.MenuName =@MenuName
LEFT JOIN Thread Td ON Td.ForumId = Fm.ForumId
LEFT JOIN Post Pt ON Pt.ThreadId = Td.ThreadId and Pt.DateTime = @Dete
WHERE Td.Title = @News AND Pt.DateTime = @Date
Code_warrior...
Member
251 Points
282 Posts
Re: Query problem - returns no details.
Apr 04, 2012 05:56 AM|LINK
I dont know where the @News and @Date fit in. Let me re-ellaborate. There are two session variables. @dete and @Category. @Category(eg Value= "News"), is a column in the Menu Table, and is there to compare that the data in the forum table and retrieve everything in the forum Table based on the Category value(eg.News), or MenuName from the Menu Table(The value "News" is a MenuName column in the Menu table). In addition to that we want to pull the ThreadTitle in the Thread Table that matches the specified ForumID with the Forum Name Value is @Category(eg. News). It is also supposed to pull the Last PostTime that matches the session variable @dete, and UserName of the last Post, of the specified thread, of the specified forum, of the specified MenuName(eg. "News")
Table Structure
Users(UserId)
Menu(MenuId, MenuName, MenuDesc)
Forum(ForumId, MenuId, ForumName, ForumDesc)
Thread(ThreadId, ForumId, ThreadTitle,ThreadText, UserId, ThreadDate)
Post(PostId, ThreadId, PostText, PostDate, UserId)
The objective is to pull information necessary to populate
1. The forum Page (Menu, Forum, Thread, Post Table Data, where Forum.MenuId = Session["Category"] and (Last) Post.PostDate = Session ["dete"]),
2. The thread page (Thread Table Data(Except for ThreadText where Thread.ThreadId = Session["ThreadId"] and (Last) Post.PostDate = Session ["dete"])
3. The Post Page (Both Thread and associated Post data where Thread.ThreadId=Session["ThreadId"] and Post.ThreadId =Thread.ThreadId)
basheerkal
Star
10672 Points
2426 Posts
Re: Query problem - returns no details.
Apr 04, 2012 06:14 AM|LINK
Do you want to get data selected for 3 GridViews in three different pages? Please make it clear.
(Talk less..Work more)
bbcompent1
All-Star
32992 Points
8508 Posts
Moderator
Re: Query problem - returns no details.
Apr 04, 2012 11:10 AM|LINK
Sorry, was giving more of a theory than exact details. So for the dates, I presume that would be your @Dete (referred to as @Date) and the menuname would be @Category (this was that @News I referred to).
Code_warrior...
Member
251 Points
282 Posts
Re: Query problem - returns no details.
Apr 04, 2012 07:31 PM|LINK
Yes there are three pages, One gridview for each.
Code_warrior...
Member
251 Points
282 Posts
Re: Query problem - returns no details.
Apr 04, 2012 08:57 PM|LINK
The query doesnt return any values, just the feilds(Column Titles).
Code_warrior...
Member
251 Points
282 Posts
Re: Query problem - returns no details.
Apr 05, 2012 04:23 AM|LINK
I have tried it in many combinations but it just does not produce any data, just the Column Names.
Code_warrior...
Member
251 Points
282 Posts
Re: Query problem - returns no details.
Apr 05, 2012 04:41 AM|LINK
Here is the current Query
SELECT * FROM Forum Fm
LEFT JOIN Menu Mn ON Mn.MenuName =@Category
LEFT JOIN Thread Td ON Td.ForumId = Fm.ForumId
LEFT JOIN Post Pt ON Pt.ThreadId = Td.ThreadId and Pt.PostTime = @dete
WHERE Fm.ForumName = @Category AND Pt.PostTime = @dete
Please help.