Receiving data from an SQL table in Delphi

Question

I have a form which needs to fetch and display data from a MYSQL database.
The SQL code which I have written works fine, as I have tried to execute it within MYSQL.

I have also used a similar format of code to the one in my form, within another form, to fetch and display SQL data. The code works perfectly in the other form.
However, my code for this form does not seem to be receiving/displaying the data from the SQL table.

Here's a snippet of the code written for the OnShow event of the form.

procedure TfrmEditBooking.FormShow(Sender: TObject);  
var  
  CustName: string;  
begin  
  if SelectedID > 0 then  
  begin  
    with frmBookingData.ADOQuery1 do  
    begin  
      Open;    
      SQL.Clear;  
      SQL.Add('SELECT Name, Surname ' +
              'FROM customers_main ' +
              'WHERE customers_main.idcustomers_main ' +
              'IN ' +
              '(SELECT bookings_main.customers_main_idcustomers_main ' +
              'FROM bookings_main ' +
              'WHERE bookings_main.idbookings_main = ' + IntToStr(SelectedID) + ')');
      ExecSQL;  
      CustName := FieldByName('Surname').AsString + ' ' + FieldByName('Name').AsString;  
      Label1.Caption := CustName;  
      Close;  
    end;  
  end;  
end;

Answer 1

To get this running you should change it to

procedure TfrmEditBooking.FormShow(Sender: TObject);
var
  CustName: string;
begin
  if SelectedID > 0 then
    begin
      with frmBookingData.ADOQuery1 do
        begin
          Close; // close first
          SQL.Clear;
          SQL.Add(
            'SELECT Name, Surname '+
            'FROM customers_main '+
            'WHERE customers_main.idcustomers_main '+
            'IN '+
            '(SELECT bookings_main.customers_main_idcustomers_main '+
            'FROM bookings_main '+
            'WHERE bookings_main.idbookings_main = '+IntToStr(SelectedID)+')');
          Open; // open the query
          if not Eof then
            CustName := FieldByName('Surname').AsString+' '+FieldByName('Name').AsString
          else
            CustName := 'not found';
          Close; // close when finished
        end;
      Label1.Caption := CustName;
    end;
end;

But you should get some (negative) side effects, if frmBookingData.ADOQuery1 is already in use for something different

Answer 2

One uses 'execsql' when one is not expecting to receive a cursor into a dataset (in other words, use 'execsql' with 'update', 'insert' and 'delete' statements but not with 'select').

You should replace the 'execsql' command with 'open' ... and remove the 'open' before 'sql.clear'.

Don't use a subquery when you should be using a join.

I think that your code ought to look like this

procedure TfrmEditBooking.FormShow(Sender: TObject);
begin
 if SelectedID > 0 then
  with frmBookingData.ADOQuery1 do
   begin
    SQL.Clear;
    SQL.Add ('SELECT Name, Surname ');
    sql.add ('FROM customers_main inner join bookings_main');
    sql.add ('on customers_main.idcustomers_main = ');     
    sql.add ('bookings_main.customers_main_idcustomers_main');
    sql.add ('where bookings_main.idbookings_main = :p1');
    sql.params[0].asinteger:= SelectedID;
    open;  
    Label1.Caption := fieldbyname ('name').asstring + ' ' +
                      fieldbyname ('surname').asstring;
    Close;
   end;
end;