DBGrid1.MouseToCell(x,y, acol, arow); lazarus / 16-Databases ---------------------------------------------------------------------- pcurtis · 2020-06-19 13:14 Hi All, by using DBGrid1.MouseToCell(x,y, acol, arow); I can get the row number in a DBGrid, this is not the same as the rownumber in the actual dataset. How can I get the actual row in the underlying dataset? Thanks for your help. ---------------------------------------------------------------------- eljo · 2020-06-19 13:52 a quick idea is to count the distance of the selected row to the one returned by the MoustToCell method and add or subtracted from the dataset's recordno which should be on the dbgrid selected row. ---------------------------------------------------------------------- pcurtis · 2020-06-19 13:59 I don't completely understand could you sgow it in code please? ---------------------------------------------------------------------- eljo · 2020-06-19 14:11 Quote from: pcurtis on June 19, 2020, 03:59:40 pmI don't completely understand could you sgow it in code please? later tonight when I get access to a lazarus installation I'll try to write a sample. ---------------------------------------------------------------------- wp · 2020-06-19 14:32 Quote from: pcurtis on June 19, 2020, 03:14:33 pmby using DBGrid1.MouseToCell(x,y, acol, arow); I can get the row number in a DBGrid, this is not the same as the rownumber in the actual dataset. How can I get the actual row in the underlying dataset? "rownumber" is the RecNo of the Dataset? Read the docs: https://www.freepascal.org/docs- html/current/fcl/db/tdataset.recno.html QuoteThis property should not be used if exact positioning is required. it is inherently unreliable. Which TDataset component are you using, which database engine? Quote from: pcurtis on June 19, 2020, 03:14:33 pmHow can I get the actual row in the underlying dataset? You can't. Forget about the concept with rows and columns, a DBGrid is different from a StringGrid in this aspect. There is no random access to any cell of the grid like in a StringGrid. Work with the properties/methods that the Dataset provides rather than with the properties/methods of the grid. When you click on a cell the record belonging to the clicked row becomes the active record of the dataset and you can access its data as Dataset.Fields[index] or Dataset.FieldByName(fieldname). What are you trying to achieve so that you need the row number? ---------------------------------------------------------------------- pcurtis · 2020-06-19 15:21 I am trying to sort rows by dragging and dropping. I am using SQLite3 ---------------------------------------------------------------------- wp · 2020-06-19 15:38 This is not possible. In order to sort you must sort the dataset and the grid will adjust itself. ---------------------------------------------------------------------- pcurtis · 2020-06-19 15:55 Can it be done in another visual control? I mean if I copy the dataset to, for example to a TStringList, and then use a visual control to sort it. ---------------------------------------------------------------------- eljo · 2020-06-19 16:32 Quote from: pcurtis on June 19, 2020, 05:55:53 pmCan it be done in another visual control? I mean if I copy the dataset to, for example to a TStringList, and then use a visual control to sort it. Why are you ignoring the controls that where design for data manipulation? Are you trying to write or test sorting algorithms? Some other reason? In short explore the Dataset and its abilities. Have a look on the Indexdefs and indexFieldNames properties and how they are used. Search for indexing and sorting for the datasets and if you do not find what you need then ask again. ---------------------------------------------------------------------- pcurtis · 2020-06-19 17:04 I want to sort via drag and drop - I don't know of any visual data control that does that. Or am I missing something? ---------------------------------------------------------------------- wp · 2020-06-19 17:05 Quote from: pcurtis on June 19, 2020, 05:55:53 pmCan it be done in another visual control? Of course. Copy the dataset to a StringGrid (not a StringList) and write drag-and-drop handler to rearrange the rows. But you must be aware that the new order will not be persistent - the next time you open the dataset it will have the old order again. To make the order persistent the dataset must have a column such as "SortOrder" which must correspond to an index of the dataset. When you are ready with rearranging in the stringgrid you must write the stringgrid row indexes to this field of the dataset. ---------------------------------------------------------------------- pcurtis · 2020-06-19 17:17 I've just found how to do this in a Listbox. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.ListBox1DragDrop(Sender, Source: TObject; X, Y: Integer);var  DropPosition, StartPosition: Integer;    DropPoint: TPoint;begin    DropPoint.X := X;    DropPoint.Y := Y;    with Source as TListBox do  begin      StartPosition := ItemAtPos(StartingPoint,True) ;      DropPosition := ItemAtPos(DropPoint,True) ;       Items.Move(StartPosition, DropPosition) ;    end;end;  Whats the equivalent procedure in a StringGrid? ---------------------------------------------------------------------- howardpc · 2020-06-19 20:01 Quote from: pcurtis on June 19, 2020, 07:17:42 pmWhats the equivalent procedure in a StringGrid? In theory you should be able to do something of this sort: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i unit Unit1; {$mode objfpc}{$H+} interface uses  Classes, SysUtils, Forms, Controls, Grids; type   TForm1 = class(TForm)    grid: TStringGrid;  procedure FormCreate({%H-}Sender: TObject);    procedure gridDragOver(Sender, Source: TObject; X, Y: Integer; {%H-}State: TDragState; var Accept: Boolean);    procedure gridMouseDown(Sender: TObject; Button: TMouseButton; Shift: TShiftState; {%H-}X, {%H-}Y: Integer);  private    StartingPoint: TPoint;  end; var  Form1: TForm1; implementation  procedure TForm1.FormCreate(Sender: TObject);var  r, c: Integer;begin  grid.FixedCols := 0;  grid.FixedRows := 0;  grid.DefaultColWidth := 80;  grid.DragMode := dmAutomatic;  grid.Options := grid.Options - [goRangeSelect, goDrawFocusSelected, goRowSelect] + [goRowMoving];  for r := 0 to Pred(grid.RowCount) do    for c := 0 to Pred(grid.ColCount) do      grid.Cells[c, r] := Format('row %d, col %d',[r, c]);  StartingPoint := TPoint.Create(0, 0);end; procedure TForm1.gridDragOver(Sender, Source: TObject; X, Y: Integer; State: TDragState; var Accept: Boolean);var  grd: TStringGrid absolute Source;  c, r: Integer;begin  if not (Source is TStringGrid) or not (Sender is TStringGrid) or not Source.Equals(Sender) then    Exit;  r := -1;  c := -1;  grd.MouseToCell(X, Y, c, r);  Accept := (r <> StartingPoint.y);  if Accept then    begin      grd.DisableAlign;      grd.DisableAutoSizing;      grd.ExchangeColRow(False, StartingPoint.y, r);      grd.EnableAutoSizing;      grd.EnableAlign;    end;end; procedure TForm1.gridMouseDown(Sender: TObject; Button: TMouseButton; Shift: TShiftState; X, Y: Integer);begin  if (button = mbLeft) and (Shift = []) then    StartingPoint := (Sender as TStringGrid).ColRow;end; {$R *.lfm} end.In practice this does not work reliably. No doubt it can be improved, and perhaps the flicker could be removed. But I would not use such code in production.  I think TStringGrid is not really designed with drag and drop operations in mind. ---------------------------------------------------------------------- wp · 2020-06-19 20:22 If you can live with dragging the fixed row header you simply can add goRowMoving to the grid's Options and you can rearrange the rows for free by dragging the headers with the mouse. ---------------------------------------------------------------------- wp · 2020-06-19 22:01 Find in the attachment a small demo showing the idea of copying the dataset to a stringgrid, rearranging the rows in the grid by drag and drop, and writing the new order to an index column. ---------------------------------------------------------------------- pcurtis · 2020-06-19 23:00 @wp - Absolutely perfect. Thank you very much ---------------------------------------------------------------------- pcurtis · 2020-06-20 06:39 I found this code on the net (see attachment), but its for Delphi and I'm not good enough to convert it so that it will run ---------------------------------------------------------------------- wp · 2020-06-20 15:31 Find the converted project in the attachment. The conversion is relatively straightforward after replacing Delphi's ClientDataSet by its Lazarus cousin TBufDataset. In principle, the author (Cary Jensen) follows the same idea to introduce a "sequence" column. But he performs the drag and drop directly in the DBGrid based on the dataset's RecNo. And returning to the initial question, how to determine the RecNo of a given row in the grid, he uses a grid helper "RecNoFromVisibleRow" which calculates the RecNo from the row and top row indexes of the grid - too simple to be true... ---------------------------------------------------------------------- pcurtis · 2020-06-20 16:10 Once again I thank you for your help. Now I have something to look at. Again thanks.