Retreiving the schema for oledbadapter failed

[hog]

Hi,

 

this is the generated sql I get when using the dataadapter query builder:

 

SELECT     tblJobs.jobdone, tblJobs.duedate, tblContracts.po_number, tblSuppliers.name, tblEquipment.model, tblEquipment.serial, tblEquipment.type
FROM         tblJobs INNER JOIN
                     tblContracts ON tblJobs.contractid = tblContracts.contractid INNER JOIN
                     tblEquipment ON tblContracts.equipmentid = tblEquipment.equipmentid INNER JOIN
                     tblSuppliers ON tblJobs.supplierid = tblSuppliers.supplierid
WHERE     (tblJobs.jobdone = 0)

 

When I try to generate the dataset I get the error message

 

Syntax error (missing operator) in query expression 'tblJobs.contractid=tblContracts.contractid INNER JOIN tblEquipment ON tblContracts.equipmentid=tblEquipment.equipmentid INNER JOIN tblSuppliers On tblJobs.supplierid=tblSuppliers.supplierid

 

I have created the same query in Access which works fine using this sql:

 

SELECT tblJobs.*, tblContracts.po_number, tblSuppliers.name, tblContracts.equipmentid, tblEquipment.serial, tblEquipment.model, tblEquipment.type
FROM ((tblJobs INNER JOIN tblContracts ON tblJobs.contractid = tblContracts.contractid) INNER JOIN tblSuppliers ON tblContracts.Supplierid = tblSuppliers.supplierid) INNER JOIN tblEquipment ON tblContracts.equipmentid = tblEquipment.equipmentid
WHERE (((tblJobs.jobdone)=False));

 

I have tried placing the brackets in the vb code but still get the same error.

 

Any ideas?

[Nerseus]

Where did you put the square brackets? Did you put them on the SQL string, around the columns names "name" and "type" (in your SELECT list)? I would guess at least one of them is a reserved word.

 

-Nerseus

[hog]

I put the brackets in exactly the same place as that genereated by Access. I removed the ; and changed False to 0

[Nerseus]

Also check what columns Access is showing. Since you're using "tblJobs.*", I can't see what those columns are. If any have the same name as the other columns in your select, then that might cause a problem as I don't think you can have two of the same named columns in one select. Access will allow that, but ADO.NET won't.

 

For example, if tblJobs has a "name" column then that might be the problem.

 

If the columns are all unique, I'd try simplifying the query one bit at a time until you figure it out. For example, try this query in VB.NET:

Select tblJobs.* FROM tblJobs WHERE tblJobs.jobdone=0

 

If that works, then add one more table, then one more, then the final table. Hopefully you can figure it out doing it that way (if even the simple query works).

 

-Ner

[hog]

OK, got it sorted:

 

m_strSQL = "SELECT tblJobs.duedate, tblJobs.jobdone, tblContracts.po_number, tblSuppliers.name, " & _
                  "tblContracts.equipmentid, tblEquipment.serial, tblEquipment.model, tblEquipment.type " & _
                  "FROM ((tblJobs INNER JOIN tblContracts ON tblJobs.contractid = tblContracts.contractid) " & _
                  "INNER JOIN tblEquipment ON tblContracts.equipmentid = tblEquipment.equipmentid) INNER JOIN " & _
                  "tblSuppliers ON tblJobs.supplierid = tblSuppliers.supplierid WHERE (((tblJobs.jobdone) = 0))"

 

You were right about the field names, tblJobs and tblEquipment both have a field named Type. The fact that Access did not complain threw me, now I know better....

 

Thx

[Nerseus]

In my experience it's always best to be explicit with SQL, never use the shortcuts. That includes putting brackets on all column names, never using table.* in a select list, and always explicitly naming the columns in an INSERT (instead of "INSERT INTO table1 VALUES ..." use "INSERT INTO Table1 (col1,...) VALUES ..."). Just my opinion, but it usually saves trouble (for future reference).

 

-Nerseus

[hog]

Thanks Nerseus, I agree with you about coding to try save trouble later down the line. Just to confirm you note above do you mean sql like this:

 

SELECT [supplierid], [name], [contractid] FROM tblSuppliers

 

Thnx