DataSet.Table SELECT method

[Gorkem]

Hi All,

I am a little stumped here.. I have a dataset with 3 tables. I am trying to do a SELECT on one table using a NOT IN or NOT EXIST statement, however it doesn't work. Can this actually be done, if so, how? or if not, is there a work around to do something like this?

Here is my code:

DataRow[] dr = ds.Tables[2].Select("Email NOT IN (Select Email FROM ACTIVE) AND (Email NOT IN (Select Email FROM INACTIVE)");

This is the error message I get back:

Syntax error: Missing operand after 'Email' operator.

Any help would be appreciated.

Thanks,

Gorkem.



Oxigen - Next generation business solutions

http://www.oxigen.ca

-----------------------------
Components/Tools/Forums/Software/Web Services

 

[Gorkem]

Hi again,

Sorry.. I made a typo in my code.. the code should read:

DataRow[] dr = ds.Tables[2].Select("Email NOT IN (Select Email FROM ACTIVE) AND Email NOT IN (Select Email FROM INACTIVE)");

ACTIVE and INACTIVE are the names of the other tables within the dataset.



Oxigen - Next generation business solutions

http://www.oxigen.ca

-----------------------------
Components/Tools/Forums/Software/Web Services

 

[jbenson001]

Here is a good example:

http://www.akadia.com/services/dotnet_filter_sort.html

However, I suggest doing any filter or sorting in SQL.

 

[Gorkem]

Thank you for your suggestion! It is much appreciated.. However, I decided to just do an SqlBulkCopy into an SQL database and do the NOT IN query there. It adds a little bit of an overhead, but the importance out-weighs the extra overhead.

As it turns out, the DataSet.Table Select method filter is not full TSQL.. hence, it doesn't handle NOT IN, NOT EXIST subqueries.. I read somewhere that this can be done using LINQ, but unfortunately, I didn't have the luxury of time on this.

Cheers,

G.



Oxigen - Next generation business solutions

http://www.oxigen.ca

-----------------------------
Components/Tools/Forums/Software/Web Services