Two DataReaders, one Connection

[WilliamR]

Good day for everybody!

I new .NET developer... How I can to do this:

Dim cmdCommand1 As SqlCommand
Dim cmdCommand2 As SqlCommand

Dim dataReader1 As SqlDataReader
Dim dataReader2 As SqlDataReader

cmdCommand1 = New SqlCommand("SELECT * FROM Table1", cnnSPE)

cnn.Open()

dataReader1 = cmdCommand1.ExecuteReader
Do While dataReader1.Read

cmdCommand2 = New SqlCommand("Select * from Table2 Where Field1=" &
dataReader1("Field1"), cnn)
dataReader2 = cmdCommand2.ExecuteReader

While drCandidatos.Read

End While
dataReader1.Close()
Loop
cnn.Close()

When i execute the program show this error:
An unhandled exception of type 'System.InvalidOperationException' occurred
in system.data.dll
Additional information: There is already an open DataReader associated with
this Connection which must be closed first.

Are there other solution for that?

Thanks for your help.

[Bennie Haelen]

Hello,

You can only have one DataReader open per connection. In your case,would
it not be easier to write your query as a join over both "Table1" and
"Table2"? This way, you only need to open one reader, and overall
performance would be a lot better

HTH,

Bennie Haelen

WilliamR wrote:

Good day for everybody!

I new .NET developer... How I can to do this:

Dim cmdCommand1 As SqlCommand
Dim cmdCommand2 As SqlCommand

Dim dataReader1 As SqlDataReader
Dim dataReader2 As SqlDataReader

cmdCommand1 = New SqlCommand("SELECT * FROM Table1", cnnSPE)

cnn.Open()

dataReader1 = cmdCommand1.ExecuteReader
Do While dataReader1.Read

cmdCommand2 = New SqlCommand("Select * from Table2 Where Field1=" &
dataReader1("Field1"), cnn)
dataReader2 = cmdCommand2.ExecuteReader

While drCandidatos.Read

End While
dataReader1.Close()
Loop
cnn.Close()

When i execute the program show this error:
An unhandled exception of type 'System.InvalidOperationException' occurred
in system.data.dll
Additional information: There is already an open DataReader associated with
this Connection which must be closed first.

Are there other solution for that?

Thanks for your help.

[WilliamR]

Thank you Benny... In my case not is easy:
In the first Table i need to read all records one to one (each record have a
column with a value); for each record to seek in the second Table; in this, i
need to take the amount of columns = to the value that read in the first
Table.

I think to read the firts table and storage the fields that i need in one
array; and then read the array, so i can to open a Datareader.

excuse me for my english.

"Bennie Haelen" wrote:

Hello,

You can only have one DataReader open per connection. In your case,would
it not be easier to write your query as a join over both "Table1" and
"Table2"? This way, you only need to open one reader, and overall
performance would be a lot better

HTH,

Bennie Haelen

WilliamR wrote:

Good day for everybody!

I new .NET developer... How I can to do this:

Dim cmdCommand1 As SqlCommand
Dim cmdCommand2 As SqlCommand

Dim dataReader1 As SqlDataReader
Dim dataReader2 As SqlDataReader

cmdCommand1 = New SqlCommand("SELECT * FROM Table1", cnnSPE)

cnn.Open()

dataReader1 = cmdCommand1.ExecuteReader
Do While dataReader1.Read

cmdCommand2 = New SqlCommand("Select * from Table2 Where Field1=" &
dataReader1("Field1"), cnn)
dataReader2 = cmdCommand2.ExecuteReader

While drCandidatos.Read

End While
dataReader1.Close()
Loop
cnn.Close()

When i execute the program show this error:
An unhandled exception of type 'System.InvalidOperationException' occurred
in system.data.dll
Additional information: There is already an open DataReader associated with
this Connection which must be closed first.

Are there other solution for that?

Thanks for your help.

[John A. Bailo]

Bottom line:

Why not just open two (2) SqlConnection's ?
WilliamR wrote:

Thank you Benny... In my case not is easy:
In the first Table i need to read all records one to one (each record have a
column with a value); for each record to seek in the second Table; in this, i
need to take the amount of columns = to the value that read in the first
Table.

I think to read the firts table and storage the fields that i need in one
array; and then read the array, so i can to open a Datareader.

excuse me for my english.

"Bennie Haelen" wrote:

Hello,

You can only have one DataReader open per connection. In your case,would
it not be easier to write your query as a join over both "Table1" and
"Table2"? This way, you only need to open one reader, and overall
performance would be a lot better

HTH,

Bennie Haelen

WilliamR wrote:

Good day for everybody!

I new .NET developer... How I can to do this:

Dim cmdCommand1 As SqlCommand
Dim cmdCommand2 As SqlCommand

Dim dataReader1 As SqlDataReader
Dim dataReader2 As SqlDataReader

cmdCommand1 = New SqlCommand("SELECT * FROM Table1", cnnSPE)

cnn.Open()

dataReader1 = cmdCommand1.ExecuteReader
Do While dataReader1.Read

cmdCommand2 = New SqlCommand("Select * from Table2 Where Field1=" &
dataReader1("Field1"), cnn)
dataReader2 = cmdCommand2.ExecuteReader

While drCandidatos.Read

End While
dataReader1.Close()
Loop
cnn.Close()

When i execute the program show this error:
An unhandled exception of type 'System.InvalidOperationException' occurred
in system.data.dll
Additional information: There is already an open DataReader associated with
this Connection which must be closed first.

Are there other solution for that?

Thanks for your help.

[Tony Dong]

I use two connect to solve this problem.

^_^
Tony
"WilliamR" <Wi******@discussions.microsoft.com> wrote in message
news:36**********************************@microsof t.com...

Thank you Benny... In my case not is easy:
In the first Table i need to read all records one to one (each record have
a
column with a value); for each record to seek in the second Table; in
this, i
need to take the amount of columns = to the value that read in the first
Table.

I think to read the firts table and storage the fields that i need in one
array; and then read the array, so i can to open a Datareader.

excuse me for my english.

"Bennie Haelen" wrote:

Hello,

You can only have one DataReader open per connection. In your case,would
it not be easier to write your query as a join over both "Table1" and
"Table2"? This way, you only need to open one reader, and overall
performance would be a lot better

HTH,

Bennie Haelen

WilliamR wrote:

> Good day for everybody!
>
> I new .NET developer... How I can to do this:
>
> Dim cmdCommand1 As SqlCommand
> Dim cmdCommand2 As SqlCommand
>
> Dim dataReader1 As SqlDataReader
> Dim dataReader2 As SqlDataReader
>
> cmdCommand1 = New SqlCommand("SELECT * FROM Table1", cnnSPE)
>
> cnn.Open()
>
> dataReader1 = cmdCommand1.ExecuteReader
> Do While dataReader1.Read
>
> cmdCommand2 = New SqlCommand("Select * from Table2 Where Field1="
> &
> dataReader1("Field1"), cnn)
> dataReader2 = cmdCommand2.ExecuteReader
>
> While drCandidatos.Read
>
> End While
> dataReader1.Close()
> Loop
> cnn.Close()
>
> When i execute the program show this error:
> An unhandled exception of type 'System.InvalidOperationException'
> occurred
> in system.data.dll
> Additional information: There is already an open DataReader associated
> with
> this Connection which must be closed first.
>
> Are there other solution for that?
>
> Thanks for your help.
>