Connect to more than one table in database

Question

3.33/5 (3 votes)

See more:

SQL-Server-2005

Hi guys,
I'm having 2 db table(TableOne,TableTwo). I need to compare whether the Code in TableOne available in TableTwo. Below is how I created the command..guess it is wrong.

SqlCommand cmdOne = new SqlCommand("select Code from TableOne", conn);
rdrOne = cmdOne.ExecuteReader();

SqlCommand cmdTwo = new SqlCommand("select ID from TableTwo", conn);
rdrTwo = cmdTwo.ExecuteReader();

while (rdrOne.Read())
{
     string Code1 = (string)rdrOne["Code"];
     while (rdrTwo.Read())
     {
         string Code2 = (string)rdrTwo["ID"];
     }
}

I'm getting error at the bold line :
"There is already an open DataReader associated with this Command which must be closed first."

Could anyone help me out?

Skunkhead :)

Posted 9-May-11 23:38pm

skunkhead

Add a Solution

3 solutions

Solution 3

hello friend as pratik said. better go for dataset or join is the better way to do these things... hope this one will help u
try dis

Posted 9-May-11 23:53pm

beginner in C#.net

Solution 2

Well, it's because DataReaders are connected-mode, forward only thing. As long as a reader is open, connection is active and working on it. Thus if you try to use another reader then, you get the error.

Have a look at these:
MSDN blog describing the same in much more detail[^]
Workaround suggested with implication[^]
Similar discussion[^]

If needed, look here[^] for more.

Posted 9-May-11 23:50pm

Sandeep Mewara

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pratik Bhesaniya · Accepted Answer · 2011-05-09T23:43:00

you have first close your first reader then only you can use the second one ......

The reason behind this is,only one datareader per connection can be active at the same time.
So, you will have to create not only another reader but also another
connection to use two of them at the same time.

Instead of that what you are doing you can use DataSet and Retrieve both table into it....

And then simply compare both table values like,

ds.Table[0].rows[0]["Column_name"] == ds.Table[1].rows[0]["Column_name"]

I hope this will help you.