ADOQuery Master/Detail

Data:

Nate Pond - LakeMaps.Lake_Name

Brook trout - Species.Species_Name

Creek chub

Golden shiner

Black Pond

Brook trout

Brown bullhead

Common shiner

Lake Placid

Lake trout

Smallmouth bass

Yellow perch

MDB Database

ADoTable1 = LakeMaps MASTER

ADOTable2 = Species DETAIL

Relationship

LakeMaps Table
LakeMaps.Field[0] = Lake_ID: Autonumber --- ]
LakeMaps.Field[1] = Lake_Name: Text---      |
                                            |Relationship set in the access database
Species Table                               |
Species.Field[0] = Species_ID: numeric ---  ]    
Species.Field[1] = Species_Name: text

The Species Table is a Detail the LakeMaps is the Master

How can I setup a ADOQuery to filter the data to display all lakes that have Brook trout in a DBGrid?

Filtered Data:

Nate Pond

Brook trout

Creek chub

Golden shiner

Black Pond

Brook trout

Brown bullhead

Common shiner

From stackoverflow

• You can set Filtered = true and then use OnFilterRecord event and check if detail dataset contains requested value (this can be done in loop or with Locate procedure of dataset)

  This will probably be very slow on larger amount of data. In those situations I usually filter master records directly in SQL. Something like this:

  SELECT * FROM LakeMaps 
  WHERE Lake_ID in (SELECT Lake_ID 
           FROM Species INNER JOIN SpeciesLakesRelation 
               ON (Species.Species_ID = SpeciesLakesRelation.Species_Id) 
           WHERE SPECIES_NAME = 'Brook Trout')
  

  This SQL returns records from Lakes that have 'Brook Trout'.

  SpeciesLakesRelation is table that contains relation between LakeMaps and Species.

• Any idea why this produces a syntax error exception?

  procedure TFormMain.CheckBoxFilterBySpeciesClick( Sender: TObject );
  begin
    if CheckBoxFilterBySpecies.Checked then
    begin
      ADOQuery1.Close;
      ADOQuery1.SQL.Add( 'SELECT * FROM LakeMaps WHERE Lake_ID in ' +
        '(SELECT Lake_ID FROM Species INNER JOIN LakeMaps ON ' +
        '(Species.Species_ID = LakeMaps.Lake_Id) ' +
        'WHERE SPECIES_NAME = ' + ComboBoxSpecies.Text + ')');
      ADOQuery1.Open;
      ADOQuery1.First;
      ADOQuery1.Active := True;
    end
    else
    begin
      ADOQuery1.Active := False;
    end;
  end;
  

  Fabricio Araujo : Change ComboboxSpecies.Text to QuotedStr(ComboboxSpecies.Text)

• Problem with your query in is that text in query must be in apostrophes. If ComboBoxSpecies.Text has value Brook Trout, then SQL evaluates to:

  SELECT * FROM LakeMaps WHERE Lake_ID in 
    (SELECT Lake_ID FROM Species INNER JOIN LakeMaps ON 
       (Species.Species_ID = LakeMaps.Lake_Id) 
     WHERE SPECIES_NAME = Brook Trout)
  

  Note that Brook Trout is not in apostrophes, so you get syntax error from MsAccess.

  Edit:
  As Gerry noted in comment:

  • apostrophes should be added using QuotedStr function, instead of double apostrophe.
  • best solution is to use query parameter

  Delphi code, using QuotedStr, should look like this:

  ADOQuery1.SQL.Add( 'SELECT * FROM LakeMaps WHERE Lake_ID in ' +
    '(SELECT Lake_ID FROM Species INNER JOIN LakeMaps ON ' +
    '(Species.Species_ID = LakeMaps.Lake_Id) ' +
    'WHERE SPECIES_NAME = ' + QuotedStr(ComboBoxSpecies.Text) + ')');
  

  Now, if ComboBoxSpecies.Text has value Brook Trout, then this string:

  'WHERE SPECIES_NAME = ' + QuotedStr(ComboBoxSpecies.Text) + ')'
  

  evaluates as:

  WHERE SPECIES_NAME = 'Brook Trout')
  

  zendar : You need to do 2 things: set user name and password in connection string and set TAdoConnection property LoginPrompt to false.

  Gerry : The above sample will fail if you get a value in ComboBoxSpecies.Text which has an apostrophe. Either use QuotedStr as suggested by Fabricio above, or better use a parameterised query (safer from SQL injection as well - may not be an issue if Combobox style is csDropDownList).

  zendar : You are right. I corrected the answer.