Database Reader Question

[lyric0n]

Hello,

I am working with a different tables and I need to make various queries in my application. I am able to establish a connection to my SQL 2000 server and use the reader to read the first table, but when I try to use another reader (and the same connection) to read another table, I get an error saying there is already a DataReader associated with this command.

Here is my code...

'Establish connection
Dim strConn As String = "Data Source=server;Initial Catalog=Sample;Integrated Security=SSPI;"
Dim objSqlConn As New System.Data.SqlClient.SqlConnection(strConn)
objSqlConn.Open()

'Open the first set
Dim strQuery As String = "SELECT * FROM dbo.Temp"
Dim objSqlCommand As New SqlCommand(strQuery, objSqlConn)
Dim myReader As SqlDataReader
myReader = objSqlCommand.ExecuteReader()
myReader.Read() 'This works okay.

'Open/close the second set of data
Dim objReader As SqlDataReader
strQuery = "SELECT * From dbo.Bleh"
Dim objSQLTemp As New SqlCommand(strQuery, objSqlConn)
objReader = objSQLTemp.ExecuteReader() ' This is where I get the error
objReader.Read()
objReader.Close()

'Close the first set
myReader.Close()
objSqlConn.Close()

Do I really need to establish two connection to retrieve two sets of data? Or is there something I am missing?

Thanks for the help,
Chris

 

[chrissie1]

Yes two connection or you need to close the datareader.

Datareader is a readonly forwardonly recordset.



Christiaan Baes
Belgium

"My old site" - Me

 

[ThatRickGuy]

Alternatively, you could use a dataset to pull the data out.

DataReaders are usually used when you need a high performance read of the entire table. Since they are forward only, the are optimize to cruise through a large chunk of data. Datasets on the other hand, are designed to give you a disconnected copy of the data that can easily be flipped through, searched, sorted, pulled apart, updated and saved back to the database. Obviously you take a small performance hit in doing so, but you gain a wide range of functionality.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[chrissie1]

I third that and I would suggest using Settings files thay are xml. And easy to use.

Christiaan Baes
Belgium

"My old site" - Me