ms-accessdelphicomboboxdatabase-tabletadoquery

Populate ComboBox with Title Case Query Result


with TdmBCElections.Create(Self) do
begin
  with dmBCElections, qryParties do
  begin
    SQL.Clear;
    if rgpParty.ItemIndex = 0 then
      SQL.Text := 'SELECT StrConv(P_Surname, 3), StrConv(P_Names, 3) ' +
      'FROM Parties WHERE P_Type = "HEAD"'
    else
      SQL.Text := 'SELECT StrConv(P_Surname, 3), StrConv(P_Names, 3) ' +
      'FROM Parties WHERE P_Type = "TEACHER"';
    Open;
    while not Eof do
    begin
      cmbDetails.Items.Add(qryParties['StrConv(P_Surname, 3)'] + ', ' +
        qryParties['StrConv(P_Names, 3)']);
      Next;
    end;
  end;
end;

The code above gives me the following error message:

qryPartiesError

How do I call the table fields when StrConv is applied to them?


Solution

  • You can assign an alias to the fields:

    with TdmBCElections.Create(Self) do
    begin
      with dmBCElections, qryParties do
      begin
        if rgpParty.ItemIndex = 0 then
          SQL.Text := 'SELECT StrConv(P_Surname, 3) as ConvertedSurname, StrConv(P_Names, 3) as ConvertedNames ' +
          'FROM Parties WHERE P_Type = "HEAD"'
        else
          SQL.Text := 'SELECT StrConv(P_Surname, 3) as ConvertedSurname, StrConv(P_Names, 3) as ConvertedNames ' +
          'FROM Parties WHERE P_Type = "TEACHER"';
        Open;
        while not Eof do
        begin
          cmbDetails.Items.Add(qryParties['ConvertedSurname'] + ', ' +
            qryParties['ConvertedNames']);
          Next;
        end;
      end;
    end;
    

    Otherwise, you can use field indexes instead of names:

    with TdmBCElections.Create(Self) do
    begin
      with dmBCElections, qryParties do
      begin
        if rgpParty.ItemIndex = 0 then
          SQL.Text := 'SELECT StrConv(P_Surname, 3), StrConv(P_Names, 3) ' +
          'FROM Parties WHERE P_Type = "HEAD"'
        else
          SQL.Text := 'SELECT StrConv(P_Surname, 3), StrConv(P_Names, 3) ' +
          'FROM Parties WHERE P_Type = "TEACHER"';
        Open;
        while not Eof do
        begin
          cmbDetails.Items.Add(qryParties.Fields[0].AsString + ', ' + qryParties.Fields[1].AsString);
          Next;
        end;
      end;
    end;
    

    Either way, I suggest you consider using a parameterized query instead:

    SQL.Text := 'SELECT ... FROM Parties WHERE P_Type = :PType';
    if rgpParty.ItemIndex = 0 then
      Parameters.ParamByName('PType').Value := 'HEAD'
    else
      Parameters.ParamByName('PType').Value := 'TEACHER';