can not use 2 datareader to browse data???

[cool_man77]

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

[bruce]

I don't think you can do that.

[b]quote:Originally posted by cool_man77

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






</blockquote id="quote"></font id="quote">

[cool_man77]

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

[cool_man77]

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

[KPayne]

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

[cool_man77]

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

[KPayne]

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

[cool_man77]

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)

[steurm]

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[quote]IDName
1Design
2Web
3Coding
4Varia</CODE>
SubCategory[quote]
SubCategoryIDCategoryIDSubcategoryName
13ASP
23PHP
32Browser
42HTML
51Photoshop
63C#</CODE>

If I then use following query : [quote]SELECTCategory.ID,Category.Name,SubCategory.Subcat egoryName
FROMSubCategoryrightJOINCategoryONSubCategory.Cate goryID=Category.ID
ORDERBYCategory.ID,SubCategory.SubCategoryID;</CODE>

I get following result
[quote]IDNameSubcategoryName
1DesignPhotoshop
2WebBrowser
2WebHTML
3CodingASP
3CodingPHP
3CodingC#
4Varia(null)</CODE>

If I now loop through my dataset using a datareader I could create what you where seeking originally:

[quote]
stringmemCategory="";
stringtoWrite="";
while(dr.Read())
{
if(memCategory!=dr["Name"].ToString())
{
toWrite+=dr["Name"].ToString()+"[b]";
if(dr["SubCategoryName"].ToString().Length!=0)
toWrite+="--"+dr["SubCategoryName"].ToString()+"[b]";
}
else
toWrite+="--"+dr["SubCategoryName"].ToString()+"[b]";

memCategory=dr["Name"].ToString();
}
Response.Write(toWrite)</CODE>

Hopefully this helps !


--
Steurm
www.steurm.net/steurm

[KPayne]

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

Keith Payne
Technical Marketing Solutions