Populate ComboBox with Title Case Query Result

Question

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:

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

The multiple "with"s should be avoided. More to the point, are yuo sure StrConv is supported by your db engine? It is not a standard Sql function. Try SubString() instead. — MartynA, Aug 16 '16 at 18:59
These days most people seem to agree that "with" is an abomination, it introduces far more errors than it saves in typing, "With A, B" is even worse - unless you know exactly the properties of A & B, you cannot "sight read" the code. — MartynA, Aug 16 '16 at 19:05
@MartynA `SubString()` is not the same thing as `StrConv()`. — Remy Lebeau, Aug 16 '16 at 19:20
I strongly agree. It's bad enough there's one `with`. Okay, I can live with that. But a `with` inside of another `with` is making me pull my hair out. — Jerry Dodge, Aug 16 '16 at 19:23
@JerryDodge: Ironic that the OP proved the point in his query to Remy. — MartynA, Aug 16 '16 at 19:25
Isn’t that what abbreviatons are for @MartynA? I would understand if someone were to question dmBCElections (which is a `Data Module`), but `qryParties` is, the way I see it, undoubtedly a `Query`. I understand that it can cause potential errors, but not if you use it correctly. It saves a lot of time and space, so it would be a waste not to use it. And FYI, it wasn’t the `with` to blame for my query to Remy, it was an incorrect call. — Reginald Greyling, Aug 16 '16 at 19:53
Obviously you are lucky enough never to have had to debug someone else's multiple-`with`ed code. See @JerryDodge's comment. Anyway, go right ahead, if you seriously believe `with` will save you time in the long run. — MartynA, Aug 16 '16 at 19:57
I am not trying to be arrogant as I am only defending my style of programming, so forgive me if I come across that way. First of all I personally pay close attention to what’s been presented before jumping the gun and making unnecessary comments. And if I ever were unsure, there’s no harm in asking. — Reginald Greyling, Aug 16 '16 at 20:05

Remy Lebeau · Accepted Answer · 2016-08-16T19:46:16.123

6

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';

edited Aug 16 '16 at 19:46

answered Aug 16 '16 at 18:56

Remy Lebeau

555,201
31
458
770

Thank you very much for your answer Remy. Unfortuantely I tried that. I assigned the aliases “Surname” and “Names” and still received the same error message, but instead of it reading “StrConv(P_Surname, 3)” it only reads “Surname”. – Reginald Greyling Aug 16 '16 at 19:01
1

I have never heard of parameterized queries before. Mind explaining how they work? – Reginald Greyling Aug 16 '16 at 19:07
1

A parameterized query lets you define an SQL statement one time, even pre-prepare it on the server side for faster running, and then supply different parameter values each time the statement is executed. `ParamByName()` is a method of `TQuery`, which I assume `qryParties` is declared as. If it is not, you will have to lookup what capabilities your chosen DB library supports. – Remy Lebeau Aug 16 '16 at 19:18
"And where" - if you hadn't used "with", you wouldn't have to ask ;) – MartynA Aug 16 '16 at 19:18
And there I learn something new. Thank you @RemyLebeau! Ah I see. I discovered that soon after I asked. The reason I couldn’t call it is because it’s a function of the `Parameters` property of TADOQuery. I also presume that it should `ToString` and not `AsString`? – Reginald Greyling Aug 16 '16 at 19:25
The parameterized query worked like a charm! I just had to call `Paramterers` first before `ParamByName`, and I used `Value` instead of `AsString`, as `ParamByName` has no such property, only `ToString`, which gives an error. – Reginald Greyling Aug 16 '16 at 19:38
Would you mind elaborating on why I’m not seeing an `AsString` property when calling `ParamByName` @RemyLebeau? – Reginald Greyling Aug 16 '16 at 19:46
@LuiP3rd: yes, you would use `Value` instead of `ToString`. Or, you can use `qryParties['FieldName']` like you were before (`TQuery` does not support that syntax) or `qryParties.Fields[0].AsString`. – Remy Lebeau Aug 16 '16 at 19:46
2

@LuiP3rd: because ADO's `TParameter` does not have an `AsString` property, like `TField` and `TParam` do. The code I originally posted was assuming `TQuery`, not `TADOQuery` (see what happens when you provide incomplete details?). I updated the code for `TADOQuery` now. – Remy Lebeau Aug 16 '16 at 19:47
@RemyLebeau: Forgive me, I am still learning as you have already witnessed. I didn’t take into consideration the different types of database engines as I have always used ADO. – Reginald Greyling Aug 16 '16 at 20:15
@LuiP3rd The difference between `TQuery` and `TADOQuery` has nothing to do with the database engine. Either can work on numerous database engines. And for that matter, since I always work with FireDAC, I would have assumed `TFDQuery`. – Jerry Dodge Aug 16 '16 at 20:26
Of course it does @JerryDodge. I have always primarily used ADO instead of DBE, especially after it was removed in 2014 when Embarcadero launched XE7. So in my mind whenever I think of database components I automatically think of ADO. – Reginald Greyling Aug 16 '16 at 20:39
@LuiP3rd Those are not database engines. MSSQL, MySQL, Access, Oracle, those are database engines. – Jerry Dodge Aug 16 '16 at 20:42
BDE stands for Borland Database Engine @JerryDodge – Reginald Greyling Aug 16 '16 at 20:43
@LuiP3rd And that's a unique scenario - it's connectivity software which can connect a Delphi application to numerous database engines. They should have never named it a "Database Engine" in the first place, as BDE itself doesn't actually store any data. ADO, FireDAC, BDE, ODBC, dbExpress, those are essentially drivers which allow you to connect to database engines (aka database servers). – Jerry Dodge Aug 16 '16 at 20:47
@LuiP3rd Some helpful reading, where BDE is actually called "BDE SQL Links": http://edn.embarcadero.com/article/28688 and also explains how BDE was originally designed for *local* databases such as Paradox and dBase - in which case it did actually serve as an "engine". – Jerry Dodge Aug 16 '16 at 21:12

Populate ComboBox with Title Case Query Result

1 Answers1