[Solved] Colors in a DBGrid [Yes Really]

[madref]

Nope both don't work

[wp]

BUT when is start scrolling up with my mouse scroll button I never really reach the top off my grid and i get groups of 4 or even groups of 3.

How do you know that you don't reach the top of the grid? I understand you such that there is no group of 5 equally-colored records at the top when the scrollbar is at its top-most position. In this state, do you always see the same data in the first line?

[madref]

See what happens!!
https://youtu.be/viCeofvh-MQ

[wp]

Since the scroll down and back up returns to the same record it can only be that the RecNo is the guilty guy. Since the DBGrid fetches only a block of records around the visible window I could imagine that the RecNo is reset if a new block has be be loaded due to scrolling the preloaded block out of the visible window.

I only see the solution to copy the records into a stringgrid. How many records will be in the dataset displayed in the DBGrid? Copying to a stringgrid does not make sense if there are millions of records...

[madref]

i can select ALL and for now that are 700 records
but for a season it's no more than 75 records

[wp]

700 records is no problem...

So, here's the code which transfers the records of the dataset to a stringgrid:

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

1. procedure TForm_Wedstrijden_Overzicht.PopulateGrid;
2. var
3.   n: Integer;
4.   c: TGridColumn;
5.   F: TField;
6.   r: Integer;
7.   i: Integer;
8.   w: Integer;
9.   a: TAlignment;
10. begin
11.   TQ_Wedstrijden.First;
12.   TQ_Wedstrijden.Last;
13.   n := TQ_Wedstrijden.RecordCount;
14.   StringGrid1.Columns.Clear;
15.   a := taLeftJustify;
16.   for i:=0 to TQ_Wedstrijden.FieldCount-1 do begin
17.     F := TQ_Wedstrijden.Fields[i];
18.     case F.FieldName of
19.       'Date': w := 90;
20.       'Time': w := 45;
21.       'Division': w := 64;
22.       'Team A': w :=200;
23.       'Team B': w := 200;
24.       'Referee 1': w := 129;
25.       'Referee 2': w := 129;
26.       'Linesman 1': w := 129;
27.       'Linesman 2': w := 129;
28.       'S': begin w := 19; a := taCenter; end;
29.       'C': begin w := 19; a := taCenter; end;
30.       'A': begin w := 19; a := taCenter; end;
31.       else
32.       continue;
33.     end;
34.     c := StringGrid1.Columns.Add;
35.     c.Width := w;
36.     c.Title.Caption := F.FieldName;
37.     c.Title.Font.Style := [fsBold];
38.     c.Alignment := a;
39.     c.Tag := PtrInt(F);
40.   end;
41.   StringGrid1.RowCount := n + StringGrid1.FixedRows;
42.  
43.   TQ_Wedstrijden.First;
44.   r := 0;
45.   while not TQ_Wedstrijden.EoF do begin
46.     inc(r);
47.     for i:=0 to StringGrid1.Columns.Count-1 do begin
48.       c := StringGrid1.Columns[i];
49.       F := TField(c.Tag);
50.       StringGrid1.Cells[i+StringGrid1.FixedCols, r] := F.AsString;
51.     end;
52.     TQ_Wedstrijden.Next;
53.   end;
54. end;

Call this in the AfterOpen event of the dataset you had linked to the DBGrid (TQ_Wedstrijden) and whenever the records change.

Then add a stringgrid to the form, you can keep the dbgrid for comparison but you will remove it later.
Set these properties of the stringgrid:
- FixedCols = 0
- Options: add goRowSelect and goThumbtracking (if you want)
- and, to colorize 5 adjacent rows in yellow use this OnPrepareCanvas of the stringGrid:

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

1. procedure TForm_Wedstrijden_Overzicht.StringGrid1PrepareCanvas(sender: TObject;
2.   aCol, aRow: Integer; aState: TGridDrawState);
3. begin
4.   if gdFixed in AState then
5.     exit;
6.   if (ARow-1) div 5 mod 2 = 0 then begin
7.     if aState * [gdFocused, gdSelected] <> [] then
8.       TStringGrid(Sender).Canvas.Brush.Color := clOlive
9.     else
10.       TStringGrid(Sender).Canvas.Brush.Color := clYellow;
11.   end;
12. end;

[paweld]

problem with:

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

1. procedure Format_Grid_Wedstrijden;

https://drive.google.com/open?id=1Ck7avrEq0EuHX-6iTipUR9Z-_w8rYVRm

[wp]

I don't see any problem. Please explain.

[madref]

i think what he means that it has become an obsolete procedure.
If you delete DBGrid then you get an error because Format_Grid_Wedstrijden sets the columns in DBGrid.

[madref]

But i have another question.


When i double clicked on DBGrid i accessed the record with the following code:

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

1.   wID := TQ_Wedstrijden.FieldByName('Wed_ID').AsInteger;

I can do this because DBGrid is linked to TQ_Wedstrijden.


But how do i do that with the StringGrid?


PS the ID i need is stored in the first (non-visible) column.

[wp]

I only added the visible columns. To add the column with the ID proceed like with the others but set the "Visible" of the new column to false.

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

1. procedure TForm_Wedstrijden_Overzicht.PopulateGrid;
2. var
3.   [...]
4.   vis: Boolean;
5. begin
6.   [...]
7.   for i:=0 to TQ_Wedstrijden.FieldCount-1 do begin
8.     vis := true;
9.     F := TQ_Wedstrijden.Fields[i];
10.     case F.FieldName of
11.       'Wed_ID': vis := false;
12.       [...]
13.     end;
14.     c := StringGrid1.Columns.Add;
15.     [...]
16.     c.Visible := vis;
17.   end;
18.   [...]
19.  

Then, in the OnDblClick, evaluate the mouse position (Mouse.CursorPos) and determine the col/row of the clicked cell. Read the Wed_ID from the grid's cells at this row and the column index of the Wed_ID:

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

1. procedure TForm_Wedstrijden_Overzicht.StringGrid1DblClick(Sender: TObject);
2. var
3.   grid: TStringGrid;
4.   P: TPoint;
5.   wed_ID: Integer;
6.   c: Integer;
7.   gc: TGridColumn;
8. begin
9.   grid := TStringGrid(Sender);
10.  
11.   // Get col/row of clicked cell
12.   P := grid.ScreenToClient(Mouse.CursorPos);
13.   P := grid.MouseToCell(P);
14.  
15.   // Find column index of 'Wed_ID' column; takes care of column re-arrangement by user.
16.   wed_ID := MaxInt;
17.   for c := 0 to grid.Columns.Count-1 do begin
18.     gc := grid.Columns[c];
19.     if gc.Title.Caption = 'Wed_ID' then begin
20.       // Read ID value from the cell
21.       wed_ID := StrToInt(grid.Cells[gc.Index, P.y]);
22.       break;
23.     end;
24.   end;
25.   if wed_ID = MaxInt then exit;  // Not found
26.  
27.   // Locate this record in the dataset
28.   TQ_Wedstrijden.Locate('Wed_ID', wed_ID, []);
29. end;  

[wp]

If you delete DBGrid then you get an error because Format_Grid_Wedstrijden sets the columns in DBGrid.

Of course I assume that when the DBGrid is deleted all the code which is no longer needed is cleaned up. I am not going to completely debug this program here.

[madref]

Thanx for the code.


But TQ_Wedstrijden is filled with the following SQL-code:

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

1.   cSQL := 'SELECT ' + 'tbl_Wedstrijden.Wed_ID, tbl_Seizoenen.SEI_ID AS Seizoen, ' +
2.     'StrFTime("%d-%m-%Y",Wed_Datum) AS Date, ' + 'StrFTime("%H:%M",Wed_Tijd) AS Time, ' +
3.     'tbl_Divisie.Divisie_Tekst AS Division, ' +
4.     'qry_Team_A.Team AS [Team A], qry_Team_B.Team AS [Team B], ' +
5.     'qry_Scheidsrechters_1.Scheidsrechter AS [Referee 1], ' +
6.     'qry_Scheidsrechters_2.Scheidsrechter AS [Referee 2], ' +
7.     'qry_Scheidsrechters_3.Scheidsrechter AS [Linesman 1], ' +
8.     'qry_Scheidsrechters_4.Scheidsrechter AS [Linesman 2], ' +
9.     'CASE WHEN Wed_Begeleiding THEN "x" ELSE NULL END AS S, ' +
10.     'CASE WHEN Wed_Meetellen THEN "x" ELSE NULL END AS C, ' +
11.     'CASE WHEN Wed_Outlook THEN "x" ELSE NULL END AS A, ' +
12.     'tbl_Wedstrijden.Wed_Scheids_1, tbl_Wedstrijden.Wed_Scheids_2, ' +
13.     'tbl_Wedstrijden.Wed_Scheids_3, tbl_Wedstrijden.Wed_Scheids_4, ' +
14.     'tbl_Wedstrijden.Wed_Divisie AS Div, tbl_Wedstrijden.Wed_Meetellen, ' +
15.     'tbl_Wedstrijden.Wed_TeamA, tbl_Wedstrijden.Wed_TeamB, ' +
16.     'tbl_Wedstrijden.Wed_Show, qry_Team_A.Team_Plaats AS Speelplaats, ' +
17.     'tbl_Wedstrijden.Wed_Plaats, tbl_IJsbanen.IJS_Plaats, ' +
18.     'tbl_Wedstrijden.Wed_Datum, tbl_Wedstrijden.Wed_Tijd, ' +
19.     'tbl_IJsbanen.IJS_Kilometers, tbl_Wedstrijden.Wed_Opmerkingen, ' +
20.     '2*[IJS_Kilometers]*0.29 AS Euro, ' +
21.     'tbl_Divisie.Divisie_Vergoeding_Ref, tbl_Divisie.Divisie_Vergoeding_Lmn, ' +
22.     'tbl_IJsbanen.IJS_Parkeren, ' +
23.     'StrFTime("%Y-%m-%d",Wed_Datum) AS MyDate, ' +
24.     'StrFTime("%H:%M",Wed_Tijd) AS MyTime ' +
25.     'FROM tbl_Wedstrijden ' +
26.     'INNER JOIN tbl_Divisie ON tbl_Divisie.Divisie_ID = tbl_Wedstrijden.Wed_Divisie ' +
27.     'INNER JOIN tbl_Seizoenen ON tbl_Seizoenen.SEI_ID = tbl_Wedstrijden.Wed_Seizoen_ID '+
28.     'INNER JOIN tbl_IJsbanen ON tbl_IJsbanen.IJS_ID = tbl_Wedstrijden.Wed_Plaats ' +
29.     'INNER JOIN qry_Teams AS qry_Team_A ON qry_Team_A.Team_ID = tbl_Wedstrijden.Wed_TeamA '+
30.     'INNER JOIN qry_Teams AS qry_Team_B ON qry_Team_B.Team_ID = tbl_Wedstrijden.Wed_TeamB ' +
31.     'LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_1 ON tbl_Wedstrijden.Wed_Scheids_1 = qry_Scheidsrechters_1.Scheids_ID ' +
32.     'LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_2 ON tbl_Wedstrijden.Wed_Scheids_2 = qry_Scheidsrechters_2.Scheids_ID ' +
33.     'LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_3 ON tbl_Wedstrijden.Wed_Scheids_3 = qry_Scheidsrechters_3.Scheids_ID ' +
34.     'LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_4 ON tbl_Wedstrijden.Wed_Scheids_4 = qry_Scheidsrechters_4.Scheids_ID ' +
35.     'WHERE ' +
36.     '((Sei_ID=:Sei_ID) or :Sei_ID=0) ' +
37.     'AND ' +
38.     '((Div=:Divisie_ID) or :Divisie_ID=0) ' +
39.     'AND (' +
40.     '(Wed_Scheids_1=:Scheids_ID) OR (Wed_Scheids_2=:Scheids_ID) OR ' +
41.     '(Wed_Scheids_3=:Scheids_ID) OR (Wed_Scheids_4=:Scheids_ID) OR :Scheids_ID=0) ';
42.   if CB_ClubGames.Checked = True then  // Clubwedstrijden zijn geselecteerd
43.     cSQL := cSQL + 'AND (ABS(Wed_Meetellen) = 1) '
44.   else                                 // Clubwedstrijden zijn NIET geselecteerd
45.     cSQL := cSQL + '';
46.   cSQL := cSQL + 'ORDER BY tbl_Seizoenen.SEI_ID DESC, MyDate DESC, MyTime DESC ';

And i disabled the rearranging of the columns. So the first column contains the Wed_ID.

But now arises a new problem. I also have a button to edit the selected record.
The grid shows the selected record and then i still need to get the 'WedID'.


How do i do that then because the mouse cursor points at the button, not the record.
Is it possible to get the selected row of the StingGrid?


Because then the code to get the ID would be simpler and something like this (i think).

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

1. wed_ID := StrToInt((sender as TStringGrid).Cells[1, aRow]);

[wp]

StringGrid1.Row is the row index of the currently selected row. Therefore you get the Wed_ID of the selected row from the StringGrid1.Cells[c, r] where r = StringGrid1.Row and c = 0 (since the user is not allowed to reorder columns - why do you use index 1?).

After editing you must write the new record back into the string grid. Just use the "for" loop beginning in line 47 in the PopulateGrid routine a few posts above.

[madref]

I thought that start index was 1