2 DataReaders in 1 connection

[vpekulas]

I have 2 tables, 1 containing categories (parent) and another one containing sub-categories (children).
The sub-categories table has a reference to the parent in fldPID field:

CREATE TABLE [tbl_m_cate] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [fldNAME] [varchar] (150) NULL ,
) ON [PRIMARY]


CREATE TABLE [tbl_s_cate] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [fldNAME] [varchar] (150) NULL ,
 [fldPID] [int] NULL ,
) ON [PRIMARY]

I'm trying to fill a listbox which would list the parent category with sub-categories listed below each parent
so the results would look like this:

Parent 1 Cate
> Sub 1
> Sub 2
Parent 2 Cate
> Sub 1
Parent 3 Cate
> Sub 1

In classic ASP 3.0 I'd use 2 recordsets to achieve it, but in .NET apparently I can not have 2 datareaders open for 1 connection.
Any ideas ?

"Taxes are the fees we pay for civilized society" G.W.

 

[JCruz063]

vpekulas,
Try using a dataset instead of a datareader, add the two tables to the dataset, and establish relationships between the two tables.

If you don't want to use a dataset. Try binding the first datareader to a list, then close it before you open the other one. It should work!

hth

JC

 

[vpekulas]

Thanks JCruz063, do you have any samplec code that I can look at to see how to do it ?

"Taxes are the fees we pay for civilized society" G.W.

 

[harmmeijer]

A dataset is an in memory representation of data (allthough you have to set all relations yourselve).


A datareader is a stream of data from the database server to the consumer.
That's why you need an open connection for each reader.

Here is an example of how to use a multitable dataset with relations:

http://msdn.microsoft.com/library/d...ref/html/frlrfSystemDataDataSetClassTopic.asp

To get data from a dataset is a bit complicated:
myDataset.tables(0).rows(0).colums(0)
If you want to get your data in a more convenient way you can use strong typed datasets
mStrongTypedSet.tableName(rownumber).fieldname
The above samples might be wrong because I normally use intellisense (and rearly use datasets) and did the 2 samples from what I think is correct so please don't shoot me if I'm wrong.



Greetings, Harm Meijer

 

[cappmgr]

How about,

SELECT tmc.fldNAME
,tsc.fldNAME
FROM tbl_m_cate tmc
,tbl_s_cate tsc
WHERE tmc.ID = tsc.fldPID
GROUP BY tmc.fldNAME
,tsc.fldNAME

then your result should be,
Parent 1 Cate Sub 1
Parent 1 Cate Sub 2
Parent 2 Cate Sub 1
Parent 2 Cate Sub 2
Parent 3 Cate Sub 1

now just bult you list or table and only put the Parent on it when it changes.
Marty