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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tom - 28 Sep 2006 21:42 GMT

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

Reply to this Message

William (Bill) Vaughn - 28 Sep 2006 21:48 GMT

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.

Signature

____________________________________
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.
__________________________________

> I'm trying to run a query to return all of the customers who have not
> placed
[quoted text clipped - 11 lines]
> Thanks,
> Tom

Reply to this Message

Tom - 29 Sep 2006 03:09 GMT

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

> Is this SQL Server? If so, turn on the Profiler and see what SQL is being
> executed.
[quoted text clipped - 18 lines]
> > Thanks,
> > Tom

Reply to this Message

William (Bill) Vaughn - 29 Sep 2006 20:59 GMT

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.

Signature

____________________________________
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...

> Thanks William,
>
[quoted text clipped - 39 lines]
>> > Thanks,
>> > Tom

Reply to this Message

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

Free Magazines