Monday, June 29, 2015

Saving grid column widths

The OP returned from America a few days ago and on Friday we had our first meeting. After reviewing the various problems which had arisen during her absence, we turned to the few pieces of code which I had developed. The following request was then made: the ubiquitous dbGrid in the management program always shows each column being displayed with the appropriate width, although sometimes this width is wrong (especially when digits or English characters are displayed with Hebrew letters). The grid allows one to change a column's width, but these changes are not recorded. The OP wanted that once a column width is changed, it should always be displayed with that new width.

I have no experience of this so I had to research the subject. At first, it seemed very easy - simply write code which would store the column widths of a grid to the database when the form closes, then retrieve those widths when the form is opened. I considered the possibility of storing only data for columns whose width has changed, but found it very hard to determine when a change had been made. So I decided to store all the column widths of the grid.

But then I discovered that I was getting errors when I tried restoring those widths. Naturally I was working on the most complicated form in the entire program - but it's also the most heavily used. Data in the grid is often redisplayed by means of dynamic parametric queries and I had great difficulty in determining when to load the saved values. After knocking my head against the wall for at least an hour, I realised that instead of saving values from the grid, I should save (and load) values for the underlying dataset. Once I made this change, the task became much easier: before closing the dataset, I save the column widths, and after opening the dataset, I load the widths. The dataset has suitable events (BeforeClose and AfterPost), so the call to the library code need be written only once per dataset.

Naturally, the actual code to save the widths in the database is stored in a library procedure so every dataset can use it. The actual code to save widths is very simple; basically one iterates over the fields in the dataset and saves each width to the database.
with tsqlquery.create (nil) do begin sqlconnection:= dm.sqlConnection1; sql.add ('insert into usergridwidths (usergrid, colnum, colwidth) '); sql.Add ('values (:p1, :p2, :p3)'); params[0].asinteger:= gridnum; prepared:= true; for i:= 0 to cd.fields.count - 1 do begin params[1].asinteger:= i; params[2].asinteger:= cd.fields[i].displayWidth; execsql end; free end;
The loading code is very similar. 

I'm not sure that this is the best solution - the tdbgrid component has an event called 'OnColumnMoved'  which should provide a trigger for detecting a change in a column's width - but I couldn't get this to work. So the above solution should be taken with a grain of salt: it works, but I'm sure it's not optimal.

No comments: