Sorting records in Delphi DBGrid by Clicking on Column Title

Yes I know, it's me again, just when I thought I was almost there another bit of code has got me stumped. I am trying to sort a DBGrid by clicking on the DBGridTitle, there has been several bits of code all the same on the net that I have tried but it fails. I thought I found some code that worked but have since lost it and can't find it again.

Anyway the code I am using is as follows;

procedure TForm1.DBGrid1TitleClick(Column: TColumn);
{$J+}
const PreviousColumnIndex : integer = -1;
{$J-}
begin
if DBGrid1.DataSource.DataSet is TCustomADODataSet then
with TCustomADODataSet(DBGrid1.DataSource.DataSet) do
begin
try
DBGrid1.Columns[PreviousColumnIndex].title.Font.Style :=
DBGrid1.Columns[PreviousColumnIndex].title.Font.Style - [fsBold];
except
end;

Column.title.Font.Style :=
Column.title.Font.Style + [fsBold];
PreviousColumnIndex := Column.Index;

if (Pos(Column.Field.FieldName, Sort) = 1)
and (Pos(' DESC', Sort)= 0) then
Sort := Column.Field.FieldName + ' DESC'
else
Sort := Column.Field.FieldName + ' ASC';
end;
end;

It reports back with "Ambiguous overloaded call to 'Pos'"

Is it also possible to sort by only one column, say by "date". I know, I don't ask much do I !!!

Once again if anyone can help me with this I would be very grateful, program almost complete now!

kindest regards
Steve

Comments

  • steve53 wrote:
    It reports back with "Ambiguous overloaded call to 'Pos'"
    The POS function you want is the one in the system unit, so you can just change your code to say system.pos wherever it currently says pos. This specified which POS routine you want, removing the amiguity. Offhand I can't think of another POS function. Have you defined a function of your own called POS? If so, I suggest you rename it to remove the ambiguity that way.
    Is it also possible to sort by only one column, say by "date".
    Not quite sure what you are asking here. It looks the code you have will sort by just 1 column.
  • SalmiSoft wrote:
    The POS function you want is the one in the system unit, so you can just change your code to say system.pos wherever it currently says pos. This specified which POS routine you want, removing the amiguity. Offhand I can't think of another POS function. Have you defined a function of your own called POS? If so, I suggest you rename it to remove the ambiguity that way.

    Not quite sure what you are asking here. It looks the code you have will sort by just 1 column.

    Thanks, I'll check it out when I get home from work, what I meant was, can it be set up to only sort by one column, in my case say by date and not to sort by any of the other columns.

    regards
    Steve
  • steve53 wrote:
    what I meant was, can it be set up to only sort by one column, in my case say by date and not to sort by any of the other columns.
    So you still want to change the sort order by clicking on the column header for one column, but not do anything if the user clicks on the title of a different column? Simply check the column number at the start of your routine and if it isn't the column you are interested in, just bail out. For example, to allow sorting by column 4 you might write something like:
    procedure TForm1.DBGrid1TitleClick(Column: TColumn);
    {$J+}
    const PreviousColumnIndex : integer = -1;
    {$J-}
    begin
      if Column.Index <> 4 then
        Exit;
        // Your existing code goes here
    
  • That's looks simple, easy then you know how., but there again it's fairly obvious when you think about it.

    I'll let you know after I get home tonight.

    Cheers
    Steve
  • Hi SalmiSoft, I tried what you suggested and still the same issue, I thought ok lets start a new program with just a DBGrid, DataSource and ADOTable, same results. The errors I am getting are as follows;

    [dcc32 Error] Unit3.pas(45): E2251 Ambiguous overloaded call to 'Pos'
    System.pas(28005): Related method: function Pos(const string; const string; Integer): Integer;
    System.pas(28165): Related method: function Pos(const WideString; const WideString; Integer): Integer;

    [dcc32 Warning] Unit3.pas(45): W1058 Implicit string cast with potential data loss from 'string' to 'ShortString'

    [dcc32 Error] Unit3.pas(45): E2251 Ambiguous overloaded call to 'Pos'
    System.pas(28005): Related method: function Pos(const string; const string; Integer): Integer;
    System.pas(28165): Related method: function Pos(const WideString; const WideString; Integer): Integer;

    [dcc32 Warning] Unit3.pas(45): W1058 Implicit string cast with potential data loss from 'WideString' to 'ShortString'


    [dcc32 Fatal Error] Project2.dpr(5): F2063 Could not compile used unit 'Unit3.pas'

    They are all related to line 45 which is;

    if (Pos(Column.Field.FieldName, Sort) = 1) and (Pos(' DESC', Sort)= 0) then

    Now I have tried it in a basically empty program with only DBGrid, DataSource and ADOTable I thought everything would be fine.

    Once again stumped.

    regards
    Steve
  • Oh WOW! That is really ... well let's be polite and say unexpected. Thanks for putting the full error messages. The situation is clear now.

    The problem is in the Delphi code. My guess is that you got this from somewhere on the net, and it would have worked in an older Delphi version, but the change to unicode broke this code.
    The TField.FieldName is defined as a string, which nowadays is mapped to UnicodeString.
    The TCustomADODataSet.Sort property is defined as a WideString.
    So in your call to the POS function you are searching for a UnicodeString within a WideString. There are several overloaded versions of POS to cope with a variety of parameter types, but no POS version for this combination. Hence the errors.

    To get around that I suggest you make the following changes to your code:
    procedure TForm1.DBGrid1TitleClick(Column: TColumn);
    {$J+}
    const PreviousColumnIndex : integer = -1;
    {$J-}
    [B]const
      DESC : WideString = ' DESC';
      SORT_COL = 4;  // Or whatever your date column is
    var
      SortField : WideString;
    [/B]begin
    [B]if Column.Index <> SORT_COL then
      Exit;[/B]
    if DBGrid1.DataSource.DataSet is TCustomADODataSet then
    with TCustomADODataSet(DBGrid1.DataSource.DataSet) do
    begin
    try
    DBGrid1.Columns[PreviousColumnIndex].title.Font.Style :=
    DBGrid1.Columns[PreviousColumnIndex].title.Font.Style - [fsBold];
    except
    end;
    
    Column.title.Font.Style := 
    Column.title.Font.Style + [fsBold];
    PreviousColumnIndex := Column.Index;
    
    [B]SortField := Column.Field.FieldName;[/B]
    
    [B]if (Pos(SortField, Sort) = 1)
    and (Pos(DESC, Sort)= 0) then[/B]
    Sort := Column.Field.FieldName + ' DESC'
    else
    Sort := Column.Field.FieldName + ' ASC';
    end;
    end;
    
    Not tested but I think this should compile OK. The new/modified code is shown in bold.
  • Morning my friend, many thanks for all the work you are putting in for me, unfortunately it didn't quite work, the error it comes back with is in the 'System.Generics.Collections' which is way out of my depth.

    I have attached 2 images of the errors, hope this helps.

    best wishes
    Steve

    error1.jpg

    error2.jpg

    http://sjweeks.co.uk/images/error1.jpg

    http://sjweeks.co.uk/images/error2.jpg
  • At least your code compiles now and you are able to run it to some extent, so you are making progress. This new error is probably just that , a new error, and only coincidentally related to our previous discussions. This is saying is that you are trying to access item N in a list, but there is no Item N. Either N is negative or is greater than the last list index. For example you are trying to access item 11 from a list with only 7 entries in it.

    The most common cause of this problem is an "off-by-one" error. Suppose there are 8 items in your list, and you are trying to access item 8. This will give an error because the list entries run from 0 to 7 and not 1 to 8. I don't think that is true in your case though.

    BTW, my guess is that if you ran the program outside of the Delphi IDE you would not see the exception at all.

    Consider this:
    procedure TForm1.DBGrid1TitleClick(Column: TColumn);
    {$J+}
    const [B]PreviousColumnIndex : integer = -1;[/B]
    {$J-}
    const
      DESC : WideString = ' DESC';
      SORT_COL = 4;  // Or whatever your date column is
    var
      SortField : WideString;
    begin
    if Column.Index <> SORT_COL then
      Exit;
    if DBGrid1.DataSource.DataSet is TCustomADODataSet then
    with TCustomADODataSet(DBGrid1.DataSource.DataSet) do
    begin
    try
    [B]DBGrid1.Columns[PreviousColumnIndex].[/B]title.Font.Style :=
    DBGrid1.Columns[PreviousColumnIndex].title.Font.Style - [fsBold];
    except
    end;
    
    Do you see the problem? You are trying to use column -1 but there never is a column -1 because the column numbers run from 0 to Columns.Count-1

    The code tries to get around this by catching the exception and ignoring it. That empty except block is poor practise. At the least it should check the exception type before deciding it is OK to ignore.

    Personally in a situation like this I prefer to prevent the exception happening. So I would code it like this:
    procedure TForm1.DBGrid1TitleClick(Column: TColumn);
    const   [COLOR="Red"][I]// Note the revised order in which constants are defined[/I][/COLOR]
      DESC : WideString = ' DESC';
      SORT_COL = 4;  // Or whatever your date column is
      [B]NO_PREV_COL = -1;[/B]
    {$J+}
    const PreviousColumnIndex : integer = [B]NO_PREV_COL[/B];
    {$J-}
    var
      SortField : WideString;
    begin
    if Column.Index <> SORT_COL then
      Exit;
    if DBGrid1.DataSource.DataSet is TCustomADODataSet then
    with TCustomADODataSet(DBGrid1.DataSource.DataSet) do
    begin
    [I]//try[/I]
     [B] if PreviousColumnIndex <> NO_PREV_COL then[/B]
        DBGrid1.Columns[PreviousColumnIndex].title.Font.Style :=
        DBGrid1.Columns[PreviousColumnIndex].title.Font.Style - [fsBold];
    [I]//except[/I]
    [I]//end;[/I]
    
    You don't need to remove the bold text from the previous column header when there is no known previous column.
  • Hooray, We finally got there, wait a minute, did I say 'WE', you did all the work, all I did was to copy and paste, anyway many many thanks for all your help, you are my mentor.

    Delphi is just one a my hobbies together with riding my bike and amateur radio which I have just got into (I should have done it many years ago) that is why I am writing this logbook program for myself, I know there are lots of logbook programs out there but I like programming so that is why I wanted to write my own.

    This is the first database program I have written so I am very pleased it is all working at long last, thanks to you. I am sure I will add or tweak here and there so this probably wont be the last you hear from me, 'Oh god, no more', did I hear you say.

    Hope you are well.

    best wishes
    Steve
  • Log book? just examples of your work so you can revisit at later times if need? good idea...
  • squills wrote:
    Log book? just examples of your work so you can revisit at later times if need? good idea...

    Thanks for your input, SalmiSoft resolved it for me.

    regards
    Steve
Sign In or Register to comment.