Reading through 2 dataReaders

[token]

I am quite new to vb.net so you will have to bear with me.

I have these 2 tables in the same database in an SQL Server.

I want to loop through one table and for each record check to see if i can find a record that matches the current one's phone number in the other table.

My newbness shows clearly here. I open a dataReader to the first table and am looping through each of the records. As I am looping through I created an sqlCommand to create a second dataReader object and was going to use the .HasRows method of dataReader to see if anything was returned.

Of course, I didnt know that you could not have 2 dataReaders using the same Connection object. So now I am stuck.

Does anyone have any ideas of a better way to do this. Any help would be much appreciated.

Thanks.

 

[Administrator]

What you may want to look at is returning two resultsets. For example:

Dim strSQL As String = "SELECT x,y,z FROM tbl1;SELECT x,y,z FROM tbl2;"

myDataReader = cmd.ExecuteReader()

While myDataReader.Read()
'Iterate tbl1 data

End While

myDataReader.NextResultSet() 'or whatever the method is!

While myDataReader.Read()
'Iterate tbl2 data

End While

myDataReader.Close()

---

The moral of the story is many don't realize you can retrieve multiple result sets "in one shot" and really make using a DataReader efficient and effective.

HTH