Posted on 2003-11-26
I have 3 tables:
Table1: Col1, Col2
Table2: Col3, Col4,
Table3: Col5, Col1, Col3, Col6
Where There is a 1-to-1 relation between Col1 and Col7, and another relation between Col4 and Col8
I want: Select Col1, Col2 From Table1 Where Col1 In (Select Col1 from Table3 Where Col4 = x)
And I want the results to be sorted by Col 9.
I can't do this with one query! And if I use two queries, I have to open two readers, which interfere with each other. With the two queries, my function is:
TestObj o2 = (TestObj)al2[comboBox1.SelectedIndex];
string strQry ="Select Col1 From Table3 Where Col3 = x Order By Col6
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand(strQry, myConnection)
System.Data.OleDb.OleDbDataReader myReader = myCommand.ExecuteReader();
bool stay = true;
strQry2 = "Select TestID, TestName from Test Where TestID =" +Convert.ToInt16(myReader);
System.Data.OleDb.OleDbCommand myCommand2 = new System.Data.OleDb.OleDbCommand(strQry2, myConnection)
System.Data.OleDb.OleDbDataReader myReader2 = myCommand2.ExecuteReader();
TestObj obj1 = new TestObj(Convert.ToInt16(myReader2), Convert.ToString(myReader2));
stay = false;
myReader2 = myCommand2.ExecuteReader();
This doesn't work. I get an error that says 1 reader must be closed before another is used. If I close the reader, I can't use it again.
The long and short of it is, if I use two queries, I need to readers that interfere with each other. And I don't know of a way to write one query. Any help is greatly appreciated!