-1

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.

Johan
  • 74,508
  • 24
  • 191
  • 319
Rinalds Gudriks
  • 65
  • 2
  • 12
  • In `ShowMessage` show variable `mas`. In `mas` I save item name what i get from repeating query from 1st to last by id. But code doesn't matter.. I ask how to show right information in `DBGrid` and if it's possible without deleting relationship in DB. Because in db it's number field, but in `DBGrid` I need to show it as string. – Rinalds Gudriks Apr 10 '16 at 19:58
  • Forget the DBGrid, it is a "red herring", just say in plain words what you are trying to do. It sounds like you are wanting to look up a text value based on a numeric key. – MartynA Apr 10 '16 at 20:30
  • What do you mean by update query? In `DBGrdi` what you see in picture I connected to `services` table, and there is field `item_id`, `client_id`, `employee_id`. `item_name` (same as client name, employee name) field is in another table for example `item_specification` table have `item_name` field. So how can I change `item_name` field from `item_specification` table with `item_id` field in `services` table in `DBGrid`? – Rinalds Gudriks Apr 10 '16 at 20:34
  • Yep, i want some kind of look up like in MS Access or mby some peace of code or query to change 3 fields in `DBGrid` with other. And I don't know why link don't work for you, because I just tried in `Chrome, Firefox` (using Win10) and links are working. If you have any suggestions how to make links better tell me. – Rinalds Gudriks Apr 10 '16 at 20:35

1 Answers1

5

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

Community
  • 1
  • 1
Johan
  • 74,508
  • 24
  • 191
  • 319
  • Seems like it's what I need, but I still can't understand how to do that. I can't even explain what is wrong. Maybe there are some way how to cantact with you to show you what I done and maybe you could explain me what i'm doing wrong and how to make calculated field on my example. – Rinalds Gudriks Apr 11 '16 at 17:08
  • If you want to combine data from different tables, then you'll need to do a join: `select names.name, items_specification.id, items_specification.someotherfield from items_specification inner join names on (items_specification.name_id = names.id)` Read up on SQL and joins. – Johan Apr 11 '16 at 20:45
  • The "answer" currently marked as THE answer does not seem to address the OP's ACTUAL problem (which he doesn't know how to express) - which appears to be actually an SQL issue and needing to do a join as Johan suggests in a comment. It appears that the OP wants to replace the ID's in one table that are referencing another table with a value from a different column in that table. The marked answer - answers the title queestion, but not the underlying problem. – Toby May 17 '17 at 21:03