Use Multi-SqlDataReader to retrive data from multi-table

Hot Topic

How to get the max nest number in a method?
Unable to upgrade for Workstation to full TFS
SP1 - source control explorer tree view
Problem to save a table
Problem saving data,
The TFS Report doesn't update, TF50801 The TFSServerScheduler service did not initialize
Team Build Selective CI for sub projects
Throughput calculation
Exception error, how to solve or handle it?
How can i get parent process Name or Id use .net

VS Team System

VB2005 Express - Adding a Help File
Form shadow
Help, problem with this code...
disappeard items in designer
Limit Assigned To values based on user?
How do I use Code Analysis in VS?
OutOfMemory Exception Error
how to see latest version in solution explorer
Problem of setting the position of textbox, button, etc
Image Conversion

Use Multi-SqlDataReader to retrive data from multi-table

I use two SqlDataReader to retrive data from two tables:

conn = New SqlConnection(connectionstring)

conn.Open()

Dim cmd As SqlCommand

Dim reader1 As SqlDataReader, reader2 As SqlDataReader

sql = "Select * from scheme.opheadm"

cmd = New SqlCommand(sql, conn)

reader1 = cmd1.ExecuteReader

sql = "Select * from scheme.opdetm"

cmd = cmd.CommandText = sql

reader2 = cmd.ExecuteReader

There's an error with the last code line:

"There is already an open DataReader associated with this Command which must be closed first."

I change it to create two SqlCommand for each SqlDataReader:

Dim cmd1 As SqlCommand, cmd2 As SqlCommand

Dim reader1 As SqlDataReader, reader2 As SqlDataReader

sql = "Select * from scheme.opheadm"

cmd1 = New SqlCommand(sql, conn)

reader1 = cmd1.ExecuteReader

sql = "Select * from scheme.opdetm"

cmd2 = New SqlCommand(sql, conn)

reader2 = cmd2.ExecuteReader

But the same error comes with the last code line.

What's the problem How to use Multi-SqlDataReader to retrive data from multi-table Should use multi-SqlConnection for that

Thanks.

yabing




Answer this question

Use Multi-SqlDataReader to retrive data from multi-table

  • Naheem

    What if I want to access data from two tables

    For example, get data from 'order' table, read through each order record and get all order detail lines in 'order detail' table for this order, or get all records from 'products' table for all products in the order details.

    If close the connection for the first DataReader before another ExecuteReader, the data in the first DataReader couldn't be updated, could it

    thanks



  • Araki66

    yabing,

    It seems that you forget to close the connection after one query to the database by DataReader. I'm afraid you are not familiar with the related concept on ADO.NET. Actually there is no change between the two parts of your code.

    Generally speaking, please declare the common variables like conn, command at the beginning of your code, then put the query statements into the try...catch block. Every time you open databse connection and use the ExecuteReader method, there is only one result. If you want to use another, please firstly close the connection, then open again in another try...catch block. So please rewrite your code and look up some reference in msdn on the DataReader sample code :-)



  • kawing0510

    you maybe better filling a datatable with the results using the SqlDataAdapter then add those 2 datatables into a dataset. Would this be an option for you

  • Saad Ahmed

    yabing,

    If you want to access two tables with DataReader, please make sure the two tables have relationship that included the ForeignKey. Then use the proper Select statement in SQL statement such as :

    SELECT Book.BookID AS Number, Book.BookTitle AS Title, Publisher.PublisherName, BookMainTopic AS Topic FROM Book INNER JOIN Publisher on Publisher.PublisherID = Book.BookPublisherID

    This is just an example :-)



  • Use Multi-SqlDataReader to retrive data from multi-table
Answer this question