This ole SQL chestnut....still :-((

hog

hog

Well-known member
Joined
Mar 17, 2003
Messages
984
Location
UK
This is driving me nuts so if anyone out there can help youll be on my Xmas card list for life!

This is the sql generated by the query builder for my oledbadapter:

Code: 
SELECT     tblContracts.po_number, tblContracts.price, tblJobs.active, tblJobs.comments, tblJobs.dated, tblJobs.engineer, tblJobs.grn_no, tblJobs.hours, 
                      tblJobs.invoice_amount, tblJobs.invoice_date, tblJobs.invoice_no, tblJobs.jobdone, tblJobs.reference, tblJobs.sequence, tblJobs.type, 
                      tblContracts.Supplierid, tblContracts.contractid, tblContracts.order_line, tblContracts.pr_number, tblJobs.jobid, tblJobs.duedate, tblEquipment.model, 
                      tblEquipment.serial, tblEquipment.type AS equipmenttype
FROM         tblJobs INNER JOIN
                      tblContracts ON tblJobs.contractid = tblContracts.contractid INNER JOIN
                      tblEquipment ON tblContracts.equipmentid = tblEquipment.equipmentid

This produces a missing operator error whilst trying to generate the dataset.

If I run the configure dataadapter wizard and amend the FROM statement to this:

Code: 
FROM         (tblJobs INNER JOIN
                      tblContracts ON tblJobs.contractid = tblContracts.contractid) INNER JOIN
                      tblEquipment ON tblContracts.equipmentid = tblEquipment.equipmentid

It generates the SQL. But if i then attempt to preview the data the error message about missing operator appears again.
When I look at the SQL the FROM clause has been modified and the brackets removed.

The error appears in the Access query design grid too if the brackets are omitted but with the brackets the SQL returns the correct records.

Questions are why is the FROM clause being modified? And why do I get this error every time I try to include three tables in my SQL?
:confused: :confused: :confused:
 
OK some more info on this as I still cant cure it! The error message is identical in both Access and VB .NET if the brackets are omitted from the FROM clause. In Access if I replace the brackets the query works fine.

The query builder in VB.NET keeps removing the brackets and thus produces an error even though I save the sql with the brackets.

What is gonig on here ?????
 
The query builder is a piece of garbage. I dont know how else to put it to you. Try generating your SELECT statements by hand.
 
Thanks derek, can you tell me more detail please?

What I am doing is this:

I create a dataset in order to have fields available in design time in crystal reports. When I run the app I have a dynamic dataset which returns the records to be used in the report.

When I need to add additional fields to the report I need to modify the design time dataset to make the fields available to the report.

Question is how else can I make the required fields available to the report?

I have modified the .vb and .xsd files which seems to work in as much as when I select to show the schema I can see all the required fields, but the dataset needs to be generated to allow the fields to become visible to the report.

Any ideas??
 
Crap I cant believe I did figure this out sooner??? Of course manually enter the sql into the sql command text property of the oledbdataadapter...bingo!

I appear to be getting blinded by the new stuff in VB as I have entered sql direct into record source properties in Access loads of times.

Thanks Derek for pointing me in the right direction :-)
 
Back
Top