Query works in Query Analizer but not as part of a table adapter

I'm trying to run a query to return all of the customers who have not placed
an order. This query runs fine in Query Analizer but when I try to run it as
part of a table adapter it returns all rows (customers with orders AND
customers without orders). It appears that the second select is not being
utilized. What am I missing?

SELECT CustomerID, Name
FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders WHERE (OrderMonth =
9) AND (OrderYear = 2006))

Thanks,
Tom

William
Thu Sep 28 15:48:25 CDT 2006

Is this SQL Server? If so, turn on the Profiler and see what SQL is being
executed.
In any case you can dig into the TableAdapter code to see what the
DataAdapter.SelectCommand.CommandText looks like. Since this is not a
parameter query (while I expect you'll want it to be), it should be simple
to see what you're asking the backend to execute.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
_________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and November 9th 2006 you can sign up for a substantial
"earlybird" discount.


INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Tom" <Tom@discussions.microsoft.com> wrote in message
news:D9BA98B7-796F-43F0-9F63-A2A686B9136A@microsoft.com...
> I'm trying to run a query to return all of the customers who have not
> placed
> an order. This query runs fine in Query Analizer but when I try to run it
> as
> part of a table adapter it returns all rows (customers with orders AND
> customers without orders). It appears that the second select is not being
> utilized. What am I missing?
>
> SELECT CustomerID, Name
> FROM Customers
> WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders WHERE (OrderMonth =
> 9) AND (OrderYear = 2006))
>
> Thanks,
> Tom

Tom
Thu Sep 28 21:09:02 CDT 2006

Thanks William,

I didn't mention it but It was a paramater query (I thought I was making it
simpler by not including that part) and I was using the profiler to look at
the SQL and everything looked correct. As it turns out pointing me to
DataAdapter.SelectCommand.CommandText helped me solve the problem. The
autogenerated code had the two paramaters swapped. Is this common? I've
looked at the code for two days before I asked the question and I didn't pick
up on it. Do I need to worry about autogenerated code?

Thanks for the help!!!

Tom

"William (Bill) Vaughn" wrote:

> Is this SQL Server? If so, turn on the Profiler and see what SQL is being
> executed.
> In any case you can dig into the TableAdapter code to see what the
> DataAdapter.SelectCommand.CommandText looks like. Since this is not a
> parameter query (while I expect you'll want it to be), it should be simple
> to see what you're asking the backend to execute.
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> _________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> Between now and November 9th 2006 you can sign up for a substantial
> "earlybird" discount.
>
>
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
>
> "Tom" <Tom@discussions.microsoft.com> wrote in message
> news:D9BA98B7-796F-43F0-9F63-A2A686B9136A@microsoft.com...
> > I'm trying to run a query to return all of the customers who have not
> > placed
> > an order. This query runs fine in Query Analizer but when I try to run it
> > as
> > part of a table adapter it returns all rows (customers with orders AND
> > customers without orders). It appears that the second select is not being
> > utilized. What am I missing?
> >
> > SELECT CustomerID, Name
> > FROM Customers
> > WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders WHERE (OrderMonth =
> > 9) AND (OrderYear = 2006))
> >
> > Thanks,
> > Tom
>
>
>

William
Fri Sep 29 14:59:13 CDT 2006

The wizard-generated code might swap the parameter order, but not the names.
Order is not important since SS 7.0.
I'm glad you got it working.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

"Tom" <Tom@discussions.microsoft.com> wrote in message
news:6ECA0B7E-80F0-450F-8D52-3957CCE0F13C@microsoft.com...
> Thanks William,
>
> I didn't mention it but It was a paramater query (I thought I was making
> it
> simpler by not including that part) and I was using the profiler to look
> at
> the SQL and everything looked correct. As it turns out pointing me to
> DataAdapter.SelectCommand.CommandText helped me solve the problem. The
> autogenerated code had the two paramaters swapped. Is this common? I've
> looked at the code for two days before I asked the question and I didn't
> pick
> up on it. Do I need to worry about autogenerated code?
>
> Thanks for the help!!!
>
> Tom
>
> "William (Bill) Vaughn" wrote:
>
>> Is this SQL Server? If so, turn on the Profiler and see what SQL is being
>> executed.
>> In any case you can dig into the TableAdapter code to see what the
>> DataAdapter.SelectCommand.CommandText looks like. Since this is not a
>> parameter query (while I expect you'll want it to be), it should be
>> simple
>> to see what you're asking the backend to execute.
>>
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> _________________________________
>> Visit www.hitchhikerguides.net to get more information on my latest book:
>> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
>> Between now and November 9th 2006 you can sign up for a substantial
>> "earlybird" discount.
>>
>>
>> INETA Speaker
>> www.betav.com/blog/billva
>> www.betav.com
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> __________________________________
>>
>> "Tom" <Tom@discussions.microsoft.com> wrote in message
>> news:D9BA98B7-796F-43F0-9F63-A2A686B9136A@microsoft.com...
>> > I'm trying to run a query to return all of the customers who have not
>> > placed
>> > an order. This query runs fine in Query Analizer but when I try to run
>> > it
>> > as
>> > part of a table adapter it returns all rows (customers with orders AND
>> > customers without orders). It appears that the second select is not
>> > being
>> > utilized. What am I missing?
>> >
>> > SELECT CustomerID, Name
>> > FROM Customers
>> > WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders WHERE
>> > (OrderMonth =
>> > 9) AND (OrderYear = 2006))
>> >
>> > Thanks,
>> > Tom
>>
>>
>>