can not use 2 datareader to browse data???

I want to browse data fields with 2 datareader but in ASP.NET is it not done?

Example: I have a table:

[category] ID int primary key, Name varchar, SubID int default=0

ID Name SubID
1 Design 0
2 Web 0
3 Coding 0
4 Asp 3
5 Php 3

In webpage I want to browse this table like:

Coding
--Asp 'child of Coding
--Php 'child of Coding
Design
Web

With this case, I had think, I use 2 query and 2 datareader(like recordset)

query 1: I collect all ID with SubID=0
"select * from Category where SubID=0"
then I make a datareader for this query:
dim myReader As SQLDataReader
myReader = oCmd.ExecuteReader()
now I use this datareader to browse data:
While myReader.Read()
response.write(myReader.Item("Name") & "
")
'with each ID I want to browse its child Category
'I make query 2:
"select * from Category where SubID="&myReader.Item("ID")
'then I make a new datareader this query2
'dim myReader2 As SQLDataReader
myReader2 = oCmd2.ExecuteReader()
'browse Child for this ID
While myReader2.read()
response.write("--"&myReader2.Item("Name") & "
")
End While
myReader2.Close()
End while
myReader.Close()

I seem right for asp code(use recordset) but ASP.net have error:

There is already an open DataReader associated with this Connection which must be closed first.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: There is already an open DataReader associated with this Connection which must be closed first.

I try to replace with another Connection but I sitll error. Datareader in ASP.NET can not use like that??? anyone had experience with this case? Please help me, thanks

 

Ok, I know what wrong with that code. We must use 2 connection for 2 datareader. ;-)

 

Actually I can not do that. Have another solution for this case? Help me pls

 

Your code is selecting a record from the Category table, and then selecting the same record from the Category table in the second command. You can get the desired results with one SELECT command and one DataReader:

oCmd.CommandText = "SELECT Category.ID, Category.Name
FROM Category INNER JOIN (SELECT Category.ID FROM Category WHERE SubID = 0) sub
ON Category.ID = sub.ID
ORDER BY Category.ID"
.
.
Dim tempID as Integer = Int32.MinValue
While myReader.Read()
If Not myReader.Item("ID") = tempID
tempID = myReader.Item("ID")
response.write(myReader.Item("Name") & "
")
Else
response.write("--"&myReader.Item("Name") & "
")
End If
End While
myReader.Close()

Keith Payne
Technical Marketing Solutions

 

Hi Kpayne, I have ran your query but sql notice: Ambiguous column name 'CategoryID'. Can U check for me? Thanks

 

There is not a CategoryID column in the SELECT. You have probably mistyped Category.ID as CategoryID.

P.S. The code I posted is based on what I could infer about the relationship of the rows in your table from the psuedo-code in your original post. In 99.9% of cases, there are better ways of organizing your database to avoid self-joining tables (which is what you have in your Category table). If it is not too late in the development process, I suggest re-normalizing your database to separate your Categories and Sub-categories into three tables with a many-to-many relationship:

Category {CategoryID, Name}
CategorySubCategory {CategoryID, SubCategoryID}
SubCategory {SubCategoryID, Name}

Keith Payne
Technical Marketing Solutions

 

Thanks KPayne, and fix the query like:

SELECT Category.CategoryID, Category.Name
FROM Category INNER JOIN (SELECT Category.CategoryID FROM Category WHERE SubCategoryID = 0) sub
ON Category.CategoryID = sub.CategoryID

It run but this query just show a list of CategoryID(SubCategory=0)

 

KPayne is correct about the redesigning of your database. It would be the best solution for you, since self-joining tables isn't the best practice in the DB-world.

You could really normalize it like KPayne described, or a little bit different, using two tables: Category and Subcategory

Category

 

To get all of the records, remove "WHERE SubCategoryID = 0" from the SELECT.

Keith Payne
Technical Marketing Solutions