Jump to content
bazzer747

SetRange - Doesn't

Recommended Posts

Hi,

I have a table with a column of numbers, which go from -6 to 54. This table is split into 3 divisions -6 to 16, 14-20, and 21 to 54..

 

I display the records in a dbGrid with a radiogroup (rgDivs) showing the 3 divisions, plus 'All'. When a user selects a division I use a SetRange to limit what is shown. The code in the radiogroup OnClick event looks like this:

 

cChoice:= rgDivs.Items[ rgDivs.ItemIndex ]

with dm.vEnrolments do
  begin
    if cChoice = 'All' then CancelRange
    else
    if cChoice = 'Div 1' then SetRange( [ -6 ],[ 16 ] );
    if cChoice = 'Div 2' then SetRange( [ 14 ],[ 20 ] );
    if cChoice = 'Div 3' then SetRange( [ 21 ],[ 54 ] );
  end;

 

dm.vEnrolments is a View of a table with all the records and cChoice is the value selected in the radiogroup.

 

Clicking 'All', 'Div 2' and 'Div 3' does exactly what it is supposed to do, but clicking 'Div 1', which should show all records from -6 to 16 does not show the negative numbers.

 

Any thoughts on why this should be would be appreciated.

Share this post


Link to post

Only a few TDataSet descendant introduce a SetRange method. Which component do you use?

Share this post


Link to post
7 hours ago, Javier Tarí said:

If it is not supported, it should throw an exception. Is the query sorted with an order by?

 

The order by is not necessary, but an local index on the field in question is.

 

If the problem lies in FireDAC it should be possible to create a small test program with a TFDMemTable.

Share this post


Link to post

Hi,

 

What I didn't show in my code snippet was this:

 

dm.vEnrolments.IndexFieldNames:= 'Exact';

 

'Exact' being the field the SetRange is acting upon. I did read Cary Jensen's FireDac book and took this idea of using SetRange setting the IndexFieldName to the field in question. Which works fine for all values from 0 upwards - it's just why does it not work on negative numbers that puzzles me. 

 

Share this post


Link to post

Does it work when you replace the SetRange call with a sequence like this?

SetRangeStart;
FieldByName('Exact').AsInteger := -6;
SetRangeEnd;
FieldByName(Exact').AsInteger := 16;
ApplyRange;

 

Share this post


Link to post

Hi Uwe,

Darn, was hopeful there. 🙂 But just the same. However, it got me thinking deeper! An issue I have sometimes when a seemingly obvious problem might not actually be so 'clear cut'.

 

The Field 'Exact' is used in a View made up from several tables, one of which owns the 'Exact' field. When I look at the design I can see the field is set as VarChar(4)! The field is 'supposed' to be  (and should be) set as a float field as it shows golf handicaps that range from -6 to 54. I'm 'guessing' that the field was set as a varchar as some people don't have a handicap so the field is either NULL or has been set to '-'. I saw that just perusing all the values (about 100,000 of them) and then it dawned on me!

 

I can see that this is why the SetRange doesn't work, as I've assumed (that dreadful word) the field is a number and it's actually a character.

 

My solution is to (maybe) create a new float field which the SetRange will actually work on correctly. 

 

Sorry for this error on my part, but many thanks for steering me which caused me to find this out....

 

 

 

Share this post


Link to post

What I've done now is created an internal calculated float field 'Exact2', converting the hyphen and Null values to 99, and set the IndexFieldNames to 'Exact2' and the SetRange now works correctly.

  • Like 1

Share this post


Link to post
Guest
On 11/11/2020 at 6:29 PM, bazzer747 said:

Hi,

I have a table with a column of numbers, which go from -6 to 54. This table is split into 3 divisions -6 to 16, 14-20, and 21 to 54..

 

I display the records in a dbGrid with a radiogroup (rgDivs) showing the 3 divisions, plus 'All'. When a user selects a division I use a SetRange to limit what is shown. The code in the radiogroup OnClick event looks like this:

 

cChoice:= rgDivs.Items[ rgDivs.ItemIndex ]

with dm.vEnrolments do
  begin
    if cChoice = 'All' then CancelRange
    else
    if cChoice = 'Div 1' then SetRange( [ -6 ],[ 16 ] );
    if cChoice = 'Div 2' then SetRange( [ 14 ],[ 20 ] );
    if cChoice = 'Div 3' then SetRange( [ 21 ],[ 54 ] );
  end;

 

dm.vEnrolments is a View of a table with all the records and cChoice is the value selected in the radiogroup.

 

Clicking 'All', 'Div 2' and 'Div 3' does exactly what it is supposed to do, but clicking 'Div 1', which should show all records from -6 to 16 does not show the negative numbers.

 

Any thoughts on why this should be would be appreciated.

image.thumb.png.26058226c2cbdd4ded602a4e15f3081c.png    image.thumb.png.485e5930770e01dcdbb62854af9cd02d.png

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.btn_All_Range_RecordClick(Sender: TObject);
begin
  FDMemTable1.CancelRange;
end;

procedure TForm1.btn_SetRange_MEClick(Sender: TObject);
var
  lArrays: TArray<string>;
begin
  //
  lArrays := RadioGroup1.Items[RadioGroup1.ItemIndex].Split([',']);
  //
  if (Length(lArrays) = 2) then
    try
      {
        If the current dataset record is within a new range, then it becomes current after a call to SetRange.
        Otherwise, the current position is set to the first record in the range.

        If either AStartValues or AEndValues has fewer elements than the number of fields in the current index, then the remaining entries
        are set to NULL.
      }
      Caption := lArrays[0] + '  #  ' + lArrays[1];
      //
      FDMemTable1.SetRange([StrToInt(lArrays[0])], [StrToInt(lArrays[1])]);
      FDMemTable1.First;
    except
      // ... any exception here
    end;
end;

As the your table have all values in "string" type, would be better that the values has "nn" format, because the "SetRange()" works better with "Integer" values and the value, likes, "3" and "03" would be positioned in diferent place on index. Better, if all values was "Integer" type, then, none conversions it's necessary!

Try using a FDMemTable with same values in my table, but, dont use "0" before the values! you see?

 

hug

Edited by Guest

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×