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

[hog]

This is driving me nuts so if anyone out there can help you'll be on my Xmas card list for life!

 

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

 

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:

 

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:

[hog]

OK some more info on this as I still can't 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 ?????

[Derek Stone]

The query builder is a piece of garbage. I don't know how else to put it to you. Try generating your SELECT statements by hand.

[hog]

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??

[hog]

Crap I can't 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 :-)

[Nerseus]

Don't forget his Christmas card come December :)

 

-ner

[hog]

Hey guys?.......now would I forget?