DBGrid - too many colums side by side

[Nicole]

I have a table with many fields and only one value inside each.
They shall be shown in a DBGrid.

Is there a short way to display those "field & one value" vertically instead of horizontally?

Or is DBGrid not the optimal vehicle for it?

[vfclists]

I have a table with many fields and only one value inside each.
They shall be shown in a DBGrid.

Is there a short way to display those "field & one value" vertically instead of horizontally?

Or is DBGrid not the optimal vehicle for it?

If by vertically you mean with field titles in one column and the values in another you are probably looking at the TRxDBCtrlGrid.

https://forum.lazarus.freepascal.org/index.php/topic,66077.msg504788.html#msg504788

[wp]

I have a table with many fields and only one value inside each.
They shall be shown in a DBGrid.

Is there a short way to display those "field & one value" vertically instead of horizontally?

Or is DBGrid not the optimal vehicle for it?

If "one value" mean "one record", I would use a 2-column stringgrid for it: FixedCols = 0, ColCount = 2, RowCount = Dataset.FieldCount+1, iterate over all records and write the field name and field value into the cells. Not "super-short", but in the order of 10 lines...

[rvk]

If you are sure there is only 1 record in the table you can use something like a EXECUTE BLOCK to return it transposed.

Something like this (change the TBL variable to your desired table):

Code: SQL  [Select][+][-]

1. EXECUTE BLOCK
2. RETURNS (FLD VARCHAR(200), VAL VARCHAR(200)) AS
3. DECLARE TBL VARCHAR(200);
4. BEGIN
5.   TBL = 'LAND';
6.   FOR SELECT X.RDB$FIELD_NAME
7.       FROM RDB$RELATION_FIELDS X
8.       WHERE (X.RDB$RELATION_NAME = :TBL)
9.       ORDER BY X.RDB$FIELD_POSITION INTO FLD DO
10.   BEGIN
11.       EXECUTE STATEMENT 'SELECT FIRST 1 ' || FLD || ' FROM ' || TBL INTO VAL;
12.       SUSPEND;
13.   END
14. END

You could even create a separate STORED PROCEDURE for this. But this is just to show it works.

(Below are my results for tables LAND and BTWCODE)

[Nicole]

Thank you for all answers.
What I want to do: I want to edit the values from my interface.
So I am not sure, if all display options would allow this.
I do not want to damage my data by overwriting false values in my db.

This "execute block" of which I never heard of thrills me.
Would this be possible in my table as well?
The table with its fields is only temporary, which means, that I will have to redo my work again several times.
(IBQuery, UpdateQuery, field-editors, comumn-editors).
I will take some testing to find out, which need this table has.

Do you think, I shall use one or more ideas from above, when you see how many fields there are?
At the moment I ask myself if I shall do them in a crowd of DBEdit-fields instead.

here is the temp-table

Code: MySQL  [Select][+][-]

1. CREATE TABLE TBEINKOMMEN
2. (
3.   ID_EINKOMMEN integer NOT NULL,
4.   TYP varchar(20),
5.   ISIN varchar(12),
6.   WKZ varchar(12),
7.   BEZEICHNUNG varchar(30),
8.   EINTRAGSDATUM date,
9.   EMITTENT varchar(30),
10.   EMISSIONSDATUM date,
11.   AGIO decimal(4,2) DEFAULT 0,
12.   RENDITE_P_A decimal(5,3),
13.   EMISSIONSPREIS decimal(9,3),
14.   LAND varchar(30),
15.   BRANCHE varchar(30),
16.   EMISSIONSVOLUMEN decimal(12,0),
17.   ZINSBERECHNUNG char(30),
18.   BESONDERHEITEN char(30),
19.   KUPONTEXT varchar(30),
20.   "KUPONFREQUENZ PRO JAHR" smallint DEFAULT 1,
21.   NOMINALZINSSATZ decimal(5,3),
22.   MODIFIED_DURATION decimal(7,3),
23.   RATING varchar(3),
24.   WAEHRUNG varchar(3),
25.   ZUHANDELNAN varchar(20),
26.   MATURITY date,
27.   KLEINSTEEINHEIT integer,
28.   ERSTERKUPON date,
29.   LETZTERKUPON date,
30.   EMISSIONSKURS_IN_PROZENT decimal(7,3),
31.   TILGUNGSKURS_IN_PROZENT decimal(7,3),
32.   KAUFKURS_IN_PROZENT decimal(7,3),
33.   STEUER_IN_PROZENT decimal(7,3),
34.   STEUER_ALS_BETRAG integer
35. );
36.  
37. GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
38.  ON TBEINKOMMEN TO  SYSDBA WITH GRANT OPTION GRANTED BY SYSDBA;

[rvk]

This "execute block" of which I never heard of thrills me.
Would this be possible in my table as well?

Yes, it is. Just paste the given code into a TIBQuery and see the result (change the TBL variable at the top).

The problem however is that this does not include editing capabilities in the TDBGrid.
(You also didn't ask about that in your original post...)

The number of fields don't matter. They are presented in a TDBGrid in vertical layout so will scroll when there are too many for the view.

Do you think, I shall use one or more ideas from above, when you see how many fields there are?
At the moment I ask myself if I shall do them in a crowd of DBEdit-fields instead.

Normally, for a editable config screen I would choose TDBEdit or TValueListEditor.

Going with the TDBEdit's you could do something like this:

I just made the simple example real quick... with left side the TDBGrid (non editable) vertical view of the table.
(That's the IBQuery1/Datasource1)

At the right I have a ScrollBox1 where I dynamically put in some TDBEdit's.
When there are more TDBEdit's for the view you'll get the scrollbars (as seen in the image).
Of course you can also use the TValueListEditor or TStringGrid or anything you want, but the TDBEdit's are easy because you can just use .Post/.Commit in a button.

Code: Pascal  [Select][+][-]

1. procedure TForm1.FormCreate(Sender: TObject);
2. var
3.   S: string;
4.   I, X, Y: integer;
5.   Ed: TDBEdit;
6. begin
7.   IBDatabase1.Connected := True;
8.  
9.   // setup the transposed TDBGrid view
10.   s := '';
11.   s := s + 'EXECUTE BLOCK' + #13;
12.   s := s + 'RETURNS (FLD VARCHAR(20), VAL VARCHAR(200)) AS' + #13;
13.   s := s + 'DECLARE TBL VARCHAR(20);' + #13;
14.   s := s + 'BEGIN' + #13;
15.   s := s + '  TBL = ''LAND'';' + #13;
16.   s := s + '  FOR SELECT X.RDB$FIELD_NAME' + #13;
17.   s := s + '      FROM RDB$RELATION_FIELDS X' + #13;
18.   s := s + '      WHERE (X.RDB$RELATION_NAME = :TBL)' + #13;
19.   s := s + '      ORDER BY X.RDB$FIELD_POSITION INTO FLD DO' + #13;
20.   s := s + '  BEGIN' + #13;
21.   s := s + '      EXECUTE STATEMENT ''SELECT FIRST 1 '' || FLD || '' FROM '' || TBL INTO VAL;' + #13;
22.   s := s + '      SUSPEND;' + #13;
23.   s := s + '  END' + #13;
24.   s := s + 'END' + #13;
25.   IBQuery1.SQL.Text := s;
26.   IBQuery1.Active := True;
27.  
28.   // example of just putting everything in TScrollbox
29.   IBQuery2.SQL.Text := 'SELECT * FROM LAND';
30.   IBQuery2.Active := True;
31.  
32.   // we now fill the TScrollBox
33.   Y := 5; // we begin at Y position 5
34.   for I := 0 to IBQuery2.FieldCount - 1 do
35.   begin
36.     Ed := TDBEdit.Create(ScrollBox1);
37.     Ed.Parent := ScrollBox1;
38.     Ed.Top := Y;
39.     Ed.Left := 100; // adjust if you have larger descriptions/labels
40.     Ed.DataSource := DataSource2;
41.     Ed.DataField := IBQuery2.Fields[I].FieldName;
42.  
43.     // we also want a label in front of the TDBEdit
44.     with TLabel.Create(Ed) do
45.     begin
46.       Parent := Ed.Parent;
47.       Left := 10;
48.       Top := Y;
49.       Text := IBQuery2.Fields[I].FieldName;
50.     end;
51.  
52.     Y := Y + Ed.Height + 3; // now increase for the next TDBEdit
53.  
54.   end;
55.  
56. end;
57.  

BTW... You could also design a real screen but then you need to know all the fields.

In my own program I have dynamic procedure where I can easily create and change such screens.
This is because some fields also need to be TDBComboxes instead of TDBEdit for the lookup or foreign keys.

For LAND it's looks something like this:

Code: Pascal  [Select][+][-]

1.     AddBox(0, 0, 100, 'Financieel');
2.     AddEdit(true, 1, 50, 'EXPORTCODE', 'Exportcode');
3.     AddEdit(true, 1, 50, 'DAGBOEK', 'Dagboek');
4.     AddEdit(true, 1, 50, '1.L_Valuta', 'Valuta');
5.     AddEdit(true, 1, 30, '2.L_BTWHOOG', 'Btw hoog');
6.     AddEdit(true, 1, 30, '3.L_BTWLAAG', 'Btw laag');
7.     AddEdit(true, 1, 30, '4.L_BTWNUL', 'Btw nul');
8.     AddEdit(true, 1, 30, '5.L_BTWVERLEGD', 'Btw verlegd');

And with the AddBox and AddEdit I can also put inputboxes next to each other.

But when you don't know the number of content of the fields, the first method with TScrollBox would be fine.

[Nicole]

Thank you for the useful code. I will save it.

I am afraid, I did it by hand.

For dummies, I do this (one time task):
- place a panel on your form, make it larger
- place on this panel a second panel
- place the header of the panel 's top in the Object Inspector
- write a sense-making name to the header
- place a DBEdit below the header = in the middle of the panel

So you have a nice tile with header and DB value.
This tile you can copy and have more of the templates.

Then you can copy the whole first panel to have many templates on a new panel.
If the panel-parent does not match, you just drag the panels up in the object manager.

If the new form looks nicely, I change all the DBEdit-fields to those wanted from the db.

[vfclists]

Have you looked at the TRxDBCtrlGrid I mentioned earlier?

When you roll your own stuff in an adhoc manner like this there may be some bugs waiting to trip you up.

[rvk]

For dummies, I do this (one time task):
- place a panel on your form, make it larger
- place on this panel a second panel
- place the header of the panel 's top in the Object Inspector
- write a sense-making name to the header
- place a DBEdit below the header = in the middle of the panel

All of which could be done with a single line of code (and a proper constructed procedure to do all those things).
It seems a lot of work to do via the designer (also with resizing for accommodating the values etc).

But ok. It's the choice of the programmer how to do this.
(I wonder how this would look, all those TPanels with TDBEdits. Seems a bit crowded but would effectively work.)

[Nicole]

LOL, to my mind the result will look nicely. The background panels are colored differently and so I find the correct field quickly as they are in groups.

To sort them was hard. I made a screenshot of the fields and sorted the groups by color and drawing in mspaint.
Was not able to get this groups just by sorting them within the table and the move up and move down arrows.

Yes, it is done by one line of code.
Unfortunately I will not be able to find the correct field in my too long list.

Before this concept I had a DBGrid with scrollbar and could not find the fields far too long.
Where is this input field and where that? Chaos at form.

About the component: Thank you for posting
No, I did not try.
I had wonderful components in the past and - all kept in busy in some ways:
on installing, on using, on learning them, on debugging ....
And worst of all, as I had to give them up, because the developer left them abandoned.

In the meanwhile I try to reduce my components to a minimum.