Search code examples
databasedelphidbgrid

How do I display values in a DBGrid that are different from those in the database?

PROBLEM

enter image description here

I have DBGdrid and there are columns what show ID's from an MS Access database. How can I change them to real values like item name, client name, employee name?

I have code (test code, I just tried to get all items names from table and I could save them to array or variable and change with DBGrid values what show id's), but I don't know how to change DBGrid value fields.

procedure TForm2.Button1Click(Sender: TObject);
var i,j:integer; mas:string;
begin
Button1.Enabled := false;
Button2.Enabled := true;
Button3.Enabled := true;

Form1.ADOQuery1.SQL.Text := 'SELECT * FROM items_specification';

Form1.ADOQuery1.Open;
j:= Form1.ADOQuery1.RecordCount;
Form1.ADOQuery1.Close;

i:=1;

repeat
Form1.ADOQuery1.SQL.Text := 'SELECT * FROM items_specification WHERE item_id = :ID';
Form1.ADOQuery1.Parameters.ParamByName('ID').Value := i;
Form1.ADOQuery1.Open;
mas:= Form1.ADOQuery1['item_name'];
Form1.ADOQuery1.Close;
inc(i);
ShowMessage(mas) ;
until (i = j+1);

Maybe you have any suggestions how to solve the problem, I will appreciate that.

In MS Access I have made look up to show names there, mby there is some way to do that in DBGrid?

UPDATED

But code doesn't matter.. My big question is how to set item name, client name and employee name in DBGrid (in values not column title)?! In MS Access those fields where is id is number, so if I even edit DBGrid it don't allow me to change value to string.. Only way what I could imagine is to broke relationships in MS Access and change fields to ShortText, but I think is not the best way.

Solution

  • To show values in a DBGrid cell that are not actually in your database, you can add a calculated field.

    You double click on the source: ADOQuery1 and add all the fields you want to the available field list. Then you add a new field.
    (I've left the other fields empty because I'm lazy, but you should make sure to add all the fields from the database that you want to list)

    enter image description here

    You set the properties as required (don't forget that radiobutton in the middle of the dialog).

    In your form a new Field will be added matching the name you gave in the Name box (prefixed with the dataset name).
    You then double click on the OnCalcFields event of the dataset (ADOQuery1) and insert to code to populate your calculated fields, e.g.:

    procedure TForm44.ADOQuery1CalcFields(DataSet: TDataSet);
begin
  ADOQuery1ExampleCalc1.AsString:= 'Prefix:'+DataSet.FieldByName('Field1').AsString;
  ADOQuery1ExampleCalc2.AsInteger:= DataSet.FieldByName('Amount').AsInteger+100;
end;

    Note
    If you display many rows, you will find that it may display slowly. In that case replace the FieldByName with the actual field reference, e.g. ADOQuery1Field1.
    FieldByName does a lookup every time it's invoked which slows things down.

    Note 2
    You can also combine data from 2 database tables in the OnCalcFields event, but beware that event gets called once for every row on display, so make sure your lookup is snappy.
    If not it may be a better idea to change the SQL statement in your query.

    Further reading
    See here (if you want to do this at run time): Adding a calculated field to a Query at run time

    Here's the official documentation: http://docwiki.embarcadero.com/RADStudio/Seattle/en/Defining_a_Calculated_Field

    Sample code: http://docwiki.embarcadero.com/RADStudio/Seattle/en/Programming_a_Calculated_Field