TADOQuery JET SQL (mdb file) problems

[rtb1]

Hi,

I am currently switching from the BDE to ADO. I have an query with a left join operation. It worked well with A TQuery and the BDE engine. Not with a TADOQuery!

I stripped down the query to the bar minimum:

  DataModule1.QResDB.Close;
  DataModule1.QResDB.SQL.Clear;
  s1 := 'select resdb.ID FROM resdb LEFT JOIN agents ON resdb.MyAgent=agents.ID';
  DataModule1.QResDB.SQL.Add(s1);
  DataModule1.QResDB.Open;
  DataModule1.QResDB.ExecSQL;

This results in an EDatabaseError:

"QResDB: Field 'resdb.ID' not found"

How is this possible? All fields of resdb and agents are added to the TADOQuery (design time) by double clicking on it and selecting 'Add All Fields' from the popup.

Thanks,
Raoul

 

[LucieLastic]

Are you sure the fields are exactly the same as in the database? Try the query in DB Explorer as a test.

Also, you could change it to Inner join as I think an inner join is the same as a left join.

 

[rtb1]

Yes, the fields are exactly the same. I tried some things:

1. Only add the fields of table resdb to the TADOQuery and make it a simple query on resdb with all fields included, this worked.
2. Include result fields of table agents in the query and also the Left Join statement, this also worked.

The problem now is, how do I access the result fields of the table agents?

Example:

select resdb.ID, agents.Name FROM resdb LEFT JOIN agents ON resdb.MyAgent=agents.ID'

How do I access the values for agents.Name?

Thanks,
Raoul

 

[lespaul]

Lou,

An inner join and a left join are not the same thing.

An inner join returns all records where the joining field matches in both tables:

tblBondtypes:
BondTypeID BondTypeDesc
1 Cash Bond
2 Security Bond

tblBondReleases
BondReleaseID BondReleaseName BondTYpeID
1 Joe Blow 1
2 Jane Doe 3
3 John Smith 2

If your query is:

SELECT tblBondReleases.BondReleaseName, tblBondReleases.BondTYpeID, tblBondTypes.BondTypeDesc FROM tblBondReleases INNER JOIN tblBondTypes on tblBondReleases.BondTypeID = tblBondTypes.BondTypeID

your results would be:

Joe Blow 1 Cash Bond
John Smith 2 Security Bond

if you change it to a left or right join, there does not have to be a match in the joining table for the record to return:

SELECT tblBondReleases.BondReleaseName, tblBondReleases.BondTYpeID, tblBondTypes.BondTypeDesc FROM tblBondReleases LEFT JOIN tblBondTypes on tblBondReleases.BondTypeID = tblBondTypes.BondTypeID

(LEFT JOIN: return all the records in the left (green) table and only those fields in the right table that match)

the results from the second query would be:


Joe Blow 1 Cash Bond
Jane Doe 3
John Smith 2 Security Bond

Leslie

 

[rtb1]

Nice Leslie,

And how do you get the values of tblBondTypes.BondTypeDesc when going through the results!?

Raoul

 

[lespaul]

Well, all I use are BDE components, I had real issues trying to get the ADO components to work. I just wanted to clear up any misconceptions about SQL joins!

In the BDE components I just use:

qryName.FieldByName('BondTypeDesc').AsString

but like you I couldn't figure out how to get the values with ADO, so I didn't switch!

Leslie

 

[LucieLastic]

Thanks LesPaul for putting me right. Apologies to rtb1 if I added extra confusion there.

lou

 

[petertickler]

Can I throw in a couple of points?

(1) DataModule1.QResDB.Open;
DataModule1.QResDB.ExecSQL;

You 'Open' a 'SELECT ...' query, and ExecSQL one which updates the database (i.e. 'INSERT ...', 'UPDATE ...', or 'DELETE ...')

(2) To access the results of and ADOQuery, you use FieldByName: e.g. 'MyQuery.FieldByName('Agents.Name').asString

Peter Tickler