GridView Empty

[Terry Wingfield]

Hey Guys, i am new to all this and very much in need of some advice.

I have constructed a SQL select statement that displays a basic forum structure interface I.E:

Forum Name, Threads and LastPost

But for some reason when i add the SQL statement to the SQLDataSource and then hook the SQLDataSource to a gridview it displays empty.

I have had this working before, but through a serious of individual select statements that use hard coded values which is very ineffeicent.

My SQL select statment as follows:

SELECT TOP (1) ThreadsTable.Subject, MessageTable.Author, MessageTable.Date, TopicTable_1.TopicTableID,

(SELECT Description
FROM TopicTable
WHERE (TopicTableID = @TopicTableID)) AS Description,

(SELECT Name
FROM TopicTable AS TopicTable_2
WHERE (TopicTableID = @TopicTableID)) AS Forums,

(SELECT COUNT(*) AS Expr1
FROM ThreadsTable AS ThreadsTable_1
WHERE (TopicTableID = @TopicTableID)) AS Threads

FROM TopicTable AS TopicTable_1 INNER JOIN
ThreadsTable AS ThreadsTable ON TopicTable_1.TopicTableID = ThreadsTable.TopicTableID

INNER JOIN
MessageTable ON ThreadsTable.ThreadsTableID = MessageTable.ThreadsTableID

WHERE (TopicTable_1.TopicTableID = @TopicTableID)

ORDER BY MessageTable.Date DESC

I hope this can be rectified and Thanks in advance for the advice given.

Regards

Tez

 

[kulrom]

What happens if you execute the SP in MSSQL Management Studio ?
If it returns the wanted record then please explain how you bind the gridview ... post the asp.net code too.

 

[Terry Wingfield]

Hi kulrom,

Thanks for the reply, When i execute this statement it asks for a TopicTableID value, which i enter a corresponding TopicTableID which returns 1 resultset.

In a production environment i cannot have a select statment for each TopicTableID with hard coded values to display my Forum interface.

So if the above statement can be crafted with "Group by" clause rather than the "WHERE" clause, it would be so much easier.

To be honest my asp.net code there is not much, all i did was add the select statment to a SQLDataSource and the SQLDataSource to a gridview, no code because i did it dynamically rather programatically to get it working first.

I am assuming my gridview is empty because i cannot pass the parameter in the QueryString because its just a simple display page.

Thanks in advance.

Tez

 

[kulrom]

set the default value of the select parameter ... it should be enough if want to test it only

 

[Terry Wingfield]

Hi Kulrom,

I have default value but still returns one resultset, sorry if im confusing things.

My query should produce this, and return all resultsets (Forums AKA Topics).

Forum | Threads | Last Post
Name 5 Date
Desc By
In
-
Other row
-
Other row

I am trying to avoid hard coded values for best practises reasons.

Regards

Tez

 

[kulrom]

But you are using SELECT TOP clause so you explicitly request ONE record.

 

[Terry Wingfield]

Hi kulrom,

I am using TOP 1, because i am trying to retrieve the top 1 (lastpost) for each row.

i cant use TOP 2 etc... because it does it for all the records and not for related rows.

I am getting very confused, my desired effect is to output all forum names and threads (Which i can do) but its just that last post section where i am trying to acheive top 1 from all tables where the TopicTableID is the same and then output it all.

My select statement works but i am using the WHERE clause and it wont display.

So thats why i thought is there anyway to use a GROUP BY clause.

My forums are succesfull but just cant get that damn statement to work.

Regards

Tez

P.S thanks for replying

 

[kulrom]

now i am even more confused though. I will have to see the table structures and finally understand what you are trying to achieve.

 

[Terry Wingfield]

Hi kulrom, i confuse my self.

Ok lets go...scaled down version

Table 1 - TopicTable
TopicTableID (int) - PK
Name (varchar)
Description (varchar)

Table 2 - Threads Table
ThreadsTableID (int) - PK
TopicTableID (int) - FK
Subject (varchar)
Replies (int)
Author (varchar)

Table 3 - MessageTable
MessagesTableID (int) - PK
ThreadsTableID (int) - FK
Author (varchar)
Date (datetime)

--------------------------------
I am trying to acheive this output:

Forum-----------Threads------------Last Post
Name---------------6--------------10/12/2010
Desc---------------------------------Author
----------------------------------------In

So my Select statement would work with hard coded values but the method is no good.

Regards

Tez

 

[Terry Wingfield]

Hi Kulrom,

Just to let you know as you didnt reply after the last message. Not only does the TOP 1 clause explicitly calls 1 result set, so does sub queries which no book or no one told, this one was about trial and error.

So anyone out there if you use TOP 1 and sub queries, you CANNOT return more than 1 result set, now i know and feel silly.

Thank you Kulrom for all your help, much appreciated, i ended creating my own simple forums, and didnt use any open source software.

I finally finished my project, and it was very long winded, learning from scratch, but i did it.

DigitDotNet: Bespoke affordable web development and design services.

I now know the basics and thinking about creating some tutorials on the basics from beginner to beginner, the tutorials will be fully functional and in VB, but easily converted to C#
If you all are interested in me implementing these, please Facebook me @ Tez Wingfield or Facebook @ DigitDotNet

 

[Terry Wingfield]

Hey Guys,

Well finally after initial problems i created my simple forums.

So iv'e decided to create a tutorial on how to create a simple forum.

DigitDotNet: Developers: How to implement an online community(Forums) from start to finish



Regards



Tez