Last post Jun 06, 2009 04:37 AM by Mikesdotnetting
Jun 05, 2009 06:51 PM|Vournelis|LINK
Hello everyone, I am not sure if this is a bug in VS or my query became too complicated but I would appreciate any help I can get.
BACKGROUND: I have an MSAccess DB that has 3 tables (ITSystems, ITPrintingDevices and ITPrintersSystems). the relation is many to many and it relies on the ITPrintersSystems table which contains 3 fields (SystemsID, PrintersID and ID). ID is just an autonumber
that represents the key of the table. SystemsID and PrintersID are the respective primary keys for the tables ITSystems and ITPrintingDevices. When a system is associated to a printer, a new row is created to keep record of which system is connected to which
THE QUERY: I am trying to create a query that will return the printers that are not connected to the selected system. I am entering this query in a DataSet file under a DataTable named ITPrintersSystems
THE PROBLEM: When I am in the "Query Builder" section and click "Execute Query" sometimes I am prompted to enter just 1 value and sometimes I am prompted to enter 2. Of course the correct would be to be prompted for 2. If I right click on the query and select
"Preview Data", I get prompted only with 1 parameter (Facility). If I enter % for facility and click Preview I get the message "No value given for one or more required parameters". When I try to use this query in an ObjectDataSource I only have the option
to enter the "Facility" parameter.
NOTE: If I remove the "AND (Facility LIKE ? OR Facility IS NULL) AND (Status <> 'Disposed' AND Status <> 'Decommissioned' OR Status IS NULL)" part from the end of the query, it prompts for the SystemsID normally.
AT LAST HERE IS THE QUERY:
WHERE (NOT EXISTS
(SELECT PrintersID, SystemsID, ID, ITPrintingDevices.ID AS Expr1
FROM (SELECT ITPrintersSystems_1.PrintersID, ITPrintersSystems_1.SystemsID, ITPrintersSystems_1.ID, ITPrintingDevices_1.ID AS Expr1
FROM (ITPrintersSystems ITPrintersSystems_1 INNER JOIN
ITPrintingDevices ITPrintingDevices_1 ON ITPrintersSystems_1.PrintersID = ITPrintingDevices_1.ID)
WHERE (ITPrintersSystems_1.SystemsID = ?)) Table1
WHERE (ITPrintingDevices.ID = PrintersID))) AND (Facility LIKE ? OR
Facility IS NULL) AND (Status <> 'Disposed' AND Status <> 'Decommissioned' OR
Status IS NULL)
Thank you in Advance,
Jun 06, 2009 04:37 AM|Mikesdotnetting|LINK
I learnt a long time ago not to trust the datasource controls' wizards. They don't seem to be that clever when it comes to the Jet provider.