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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
__________________________________
 
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
 
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...
 
Back
Top