Solved

complex sorting

Posted on 2003-11-26
4
213 Views
Last Modified: 2010-04-16
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
Comment
Question by:WhatupE
  • 2
4 Comments
 

Author Comment

by:WhatupE
ID: 9828946
There is an end bracket missing that closes out the function, just in case you were wondering.
0
 
LVL 4

Expert Comment

by:Tonylmiller
ID: 9829280
I don't see Col7, Col8, or Col9 in your tables. . .
0
 

Author Comment

by:WhatupE
ID: 9829338
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
 
LVL 20

Accepted Solution

by:
TheAvenger earned 200 total points
ID: 9830177
SELECT Table1.Col1, Table1.Col2
FROM Table1, Table3
WHERE (Table1.Col1 = Table3.Col1)
AND (Table3.Col3 = X)
ORDER BY Table3.Col6
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
c# string handling 1 27
Need to pass connection string through config 2 26
Problem of RegEx to match the first occurence of 10 36
Remove greater than sign 3 44
Introduction                                                 Was the var keyword really only brought out to shorten your syntax? Or have the VB language guys got their way in C#? What type of variable is it? All will be revealed.   Also called…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now