Query problem - returns no details.

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>

Re: Query problem - returns no details.

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..

Re: Query problem - returns no details.

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

Re: Query problem - returns no details.

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)

Re: Query problem - returns no details.

Do you want to get data selected for 3 GridViews in three different pages? Please make it clear.

Re: Query problem - returns no details.

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).

Re: Query problem - returns no details.

Yes there are three pages, One gridview for each.

Re: Query problem - returns no details.

The query doesnt return any values, just the feilds(Column Titles).

Re: Query problem - returns no details.

I have tried it in many combinations but it just does not produce any data, just the Column Names.

Re: Query problem - returns no details.

bbcompent1

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

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.

Re: Query problem - returns no details.

This join right here seems to be the problem:

LEFT JOIN Menu Mn ON Mn.MenuName =@Category

@Category isn't a table so it doesn't know how to relate that properly. Try your query like this to see if you get something and then if you get results, we will debug this Menu table.

SELECT * FROM Forum Fm, Menu Mn
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 Md.Menuname = @Category AND Pt.PostTime = @dete

Re: Query problem - returns no details.

Sorry, mistyped:

SELECT * FROM Forum Fm, Menu Mn
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 Mn.Menuname = @Category
AND Pt.PostTime = @dete

Re: Query problem - returns no details.

Then why do you combine them?

Write 3 separate simple quries for each Datasource in each page.

First you do it on Forum page.

Re: Query problem - returns no details.

basheerkal

Then why do you combine them?

Write 3 separate simple quries for each Datasource in each page.

First you do it on Forum page.

I dont intend to combine them on one page. I am just trying to get some help on each query for each datasource.

Re: Query problem - returns no details.

bbcompent1

Sorry, mistyped:

SELECT * FROM Forum Fm, Menu Mn   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 Mn.Menuname = @Category   AND Pt.PostTime = @dete

Error message: The multi-part identifier "Fm.ForumId" could not be bound

Re: Query problem - returns no details.

I dont understand why its not producing any data. The query seems sound.

Re: Query problem - returns no details.

Again, the error msg in the query tester for the sqldatasource is

Error message: The multi-part identifier "Fm.ForumId" could not be bound

the query is

SELECT * FROM Forum Fm, Menu Mn
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 Mn.MenuName = @Category
AND Pt.PostTime = @dete

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"]),

Re: Query problem - returns no details.

Could you post a few rows from each table so I can emulate your tables here? Then I'll be able to better work on this problem.

Re: Query problem - returns no details.

MenuId varchar(50) MenuName varchar(50) MenuDesc nvarchar(MAX)

News

News Headlines.

ForumId varchar(50) MenuId nvarchar(50) ForumName varchar(50) ForumDesc varchar(MAX)

World News

News that has International implications.

ThreadId varchar(50) ForumId varchar(50) UserId varchar(50) ThreadName varchar(50) Views varchar(50) ThreadText varchar(150) IsApproved bit ThreadTime date

f8a61143-a418-4dfe-8a82- e83a2e756abe

Test News Thread

This is a test thread and it is the reason why this site so popular.

True

03/04/2012

PostId varchar(50) ThreadId varchar(50) PostTime datetime PostApproved bit PostText varchar(150) UserId uniqueidentifier

03/04/2012 12:00:00 AM

True

This is a test Post for the News Thread.

f8a61143-a418-4dfe-8a82-e83a2e756abe

UserId uniqueidentifier

f8a61143-a418-4dfe-8a82-e83a2e756abe

Re: Query problem - returns no details.

Ok, now how do you want this data to look exactly in each gridview?