[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

complex sorting

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:

...
ArrayList al2
...
func1(){
   
   myConnection.Open();
   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();

   myReader.Read();
   bool stay = true;
   strQry2 = "Select TestID, TestName from Test Where TestID =" +Convert.ToInt16(myReader[0]);
   System.Data.OleDb.OleDbCommand myCommand2 = new System.Data.OleDb.OleDbCommand(strQry2, myConnection)
   System.Data.OleDb.OleDbDataReader myReader2 = myCommand2.ExecuteReader();

    while(stay){
        myReader2.Read();
        TestObj obj1 = new TestObj(Convert.ToInt16(myReader2[0]), Convert.ToString(myReader2[1]));
        al3.Add(obj1);
       
        if(!myReader.Read)
        {
           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!
0
WhatupE
Asked:
WhatupE
  • 2
1 Solution
 
WhatupEAuthor Commented:
There is an end bracket missing that closes out the function, just in case you were wondering.
0
 
TonylmillerCommented:
I don't see Col7, Col8, or Col9 in your tables. . .
0
 
WhatupEAuthor Commented:
Those three lines should read:

Where There is a 1-to-1 relation between Col1(Table 1) and Co1(Table 3), and another relation between Col3 (Table 2) and Col3 (Table 3)

I want: Select Col1, Col2 From Table1 Where Col1 In (Select Col1 from Table3 Where Col3 = x)

And I want the results to be sorted by Col6.
0
 
TheAvengerCommented:
SELECT Table1.Col1, Table1.Col2
FROM Table1, Table3
WHERE (Table1.Col1 = Table3.Col1)
AND (Table3.Col3 = X)
ORDER BY Table3.Col6
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now