DBGrid error on scrolling, after SQLQuery filter applied lazarus / 16-Databases ---------------------------------------------------------------------- xico · 2012-07-21 03:43 Hello community! Specs: Lazarus 1.1 (2012-05-11) FPC 2.6.1 (SVN Rev. 37249) Windows XP SP3 ---- MySQL 5.1 on a Linux Server -------------------------------------- Usually I make my filtering direct with SQL but I wanted to try the Filter property of TSQLQuery. I made some search but I couldn't find an answer for my problem. This is the problem: after applying a filter on a SQLQuery, if I scroll the DBGrid by grabbing the DBGrid scrollbar I get the following error: "SQLQueryMain : Could not find the requested record". But it works if I use the mouse scroll wheel or the DBNavigator! It seems the DBGrid Scrollbar logic always "sees" the whole SQLQuery result ignoring any filtering while scrolling with the mouse or with the DBNavigator it respects the applied filter. Is it so by design? Is there something else I should set after applying the filter? I'll be very thankful for any help I could get. I tried a very simple application, and here is some of the code I used: Code: [Select]// ... procedure TForm1.ButtonOpenQueryClick(Sender: TObject); begin   //...     with SQLQueryMain do     try       // Filtered:= False - designtime       Close;       SQL.Clear;       SQL.Text:= 'SELECT * FROM test.bigtable';       Open;     except       on E: Exception do ShowMessage('excep: '+ E.message);       on E: EDatabaseError do ShowMessage('dbErr: '+ E.message);     end;   //... end; // ... procedure TFormMain.CheckBoxFilterClick(Sender: TObject); begin   with TCheckBox(Sender) do   if State = cbUnchecked then     SQLQueryMain.Filtered:= False   else     if State = cbChecked then       with SQLQueryMain do       begin         if EditFilter.Text = '' then           begin             Filtered:= False;             TCheckBox(Sender).State:= cbUnchecked;           end         else         begin           FilterOptions:= [foCaseInsensitive];           Filter:= EditFilter.Text;           Filtered:= True;           First;         end;       end; end; ---------------------------------------------------------------------- xico · 2012-07-21 20:31 I also tried the above situation with the stable version "lazarus-0.9.30.4-fpc-2.6.0-win32" but I got the same result. Any idea anyone? ---------------------------------------------------------------------- brunello · 2012-07-22 07:29 procedure TForm1.ButtonOpenQueryClick(Sender: TObject); begin   //...     with SQLQueryMain do     try       // Filtered:= False - designtime       Close;       SQL.Clear;       SQL.Add('SELECT * FROM test.bigtable');       SQL.Add('');       Open;     except       on E: Exception do ShowMessage('excep: '+ E.message);       on E: EDatabaseError do ShowMessage('dbErr: '+ E.message);     end;   //... end; procedure TFormMain.CheckBoxFilterClick(Sender: TObject); begin   SQLQueryMain.Close;   if TCheckBox(Sender).State = cbUnchecked then     SQLQueryMain.SQL[1] := ''   else     SQLQueryMain.SQL[1] := 'WHERE field=' + QuotedStr(EditFilter.Text);   SQLQueryMain.Open; end; ---------------------------------------------------------------------- xico · 2012-07-22 15:20 Hi brunello, thank you very much for your comment. Unfortunately this is not what I'm looking for. What you suggest is what I usually do, that is, reQuering the Database. By using the TSQLQuery Filter property, no new Query will be made but instead the DataSet will just be filtered, that is locally done and spares a connection to the Database server. As I stated on my first post, the Filter works correctly, at least with my test program wich is very simple, the problem is when I scroll the DBGrid by grabbing the DBGrid scrollbars I get this error message "SQLQueryMain : Could not find the requested record". I created a test database with five columns, the first one as 'ID' (auto increment) and I generated 100000 (one hundred thousand) records. When I apply a filter, let's say "COL2 <= 50000", I get a result of about 300 records displayed on the DBGrid. I can navigate using the DBNavigator buttons and using the mouse scroll wheel but I get the error stated above when I scroll the DBGrid by grabbing the DBGrid scrollbars. When I look at the DBGrid Vertical Scrollbar after the filter is applied, and I got a result of 300 records out of 100000, I see that the scrollbar size stays the same when it should get bigger because there are far less records on the DBGrid. Well, I hope the Gurus out there have a clue about this! And again, thank you for your suggestion. ---------------------------------------------------------------------- Mike.Cornflake · 2012-08-15 23:46 Yup - I get the error as well.  I'm using TODBCConnection, connecting to MS SQL. When I used the Zeos library, connecting to MySQL I didn't get the error.   Filtering worked perfectly.  That was using TZQuery instead of TSQLQUery.  Mind you, that was also using Laz 0.9.28 a year or two ago.   Maybe something has broken in the meantime... Unfortunately for me, I can't work out to get Zeos to connect to MS SQL in a way that gets me access to more than the first 4Kb of any Blob field... ---------------------------------------------------------------------- Lacak2 · 2012-08-16 05:20 Can you prepare test project (which creates test table populates data etc.), which reproduces error and upload it here se other people can look at it? ---------------------------------------------------------------------- Mike.Cornflake · 2012-08-16 20:55 Yup, I'll try and get onto that over the next day or two... ---------------------------------------------------------------------- Elmug · 2012-08-19 08:29 Seems to me that this could be a timing problem of the scroll bar going too fast for the updating of the data in the grid. You might try scrolling, but at snail speed, to see if the problem still happens? ---------------------------------------------------------------------- teos · 2012-08-20 14:38 The grid is missing an update that lets it read the number of records from the dataset. Maybe disabling->enabling the datasource helps or maybe a dataset.refresh helps? ---------------------------------------------------------------------- piratewar · 2012-08-24 07:59 I GOT THAT PROBLEM TOO WITH FIREBIRd DATABASE AND TIBCONNECTION ---------------------------------------------------------------------- Lacak2 · 2012-08-24 12:43 If you set PacketRecords=-1 in your TSQLQuery does something changes (error disappears) ? ---------------------------------------------------------------------- Mike.Cornflake · 2012-08-24 19:31 G'day, Apologies for the long delay - the test case is still on my TODO, but I'm swamped at work at the moment, could be several weeks before the pressure decreases. In the meatime I have tried both PacketRecords:=-1 and .Refresh.  Neither resolved the issue. Minor update.  The original poster reported "Could not find the requested record".  I had his exact error as well, but I was using my own scrollbar instead of the DBGrid scrollbar.  It's the dataset.RecordCount that doesn't get updated when you apply Filter and my Scrollbar code was using that to determine the range. So, I'm no longer using my own scrollbar, but using entirely DBGrid functionality, and I still get the same error.  It's also in the scrollbar code for the DBGrid, so I'm guessing that uses RecordCount/RecNo as well.  The controls that work (DBNavigator) only use First/Next/Prior/Last, so they don't encounter the issue. It doesn't look like it's the DBGrid missing an update, only the filtered dataset appears in the grid.  As I said, the recordcount of TSQLQuery doesn't get updated following a Filter.   Could be functionality by design in TSQLQuery, I've not used it before.  The ZeosLib TZQuery updates RecordCount, and I've got used to that...  Mind you, then why the DBGrid error?  Sigh.  Enough speculation, I will try to make time and get that test case uploaded. Cheers Mike ---------------------------------------------------------------------- teos · 2012-08-25 09:19 OK. This is definetely a implementation bug of SQLQuery then. I have seen this before on situations where the number of records is read from the database instead of determining it by the number of actual records. ---------------------------------------------------------------------- Mike.Cornflake · 2012-08-25 19:12 May not be a bug, may be design functionality.  I just searched the bugtracker (should have done that first) http://bugs.freepascal.org/view.php?id=12238 I see the bug tracker also has an outstanding request for a sample app/test case.  I will get onto it... :-) ---------------------------------------------------------------------- teos · 2012-08-26 09:29 hm.. I see arguments in both issues that make no sense: keeping a recordcount of filtered records, should require to read the entire database (Bill Todd) but he forgets that all records have to be read allready to determine if they meet the filter. how delphi does this.. well.. Luiz should have read somewhat better. I think I'd re-open the ticket or file a new one. ---------------------------------------------------------------------- Mike.Cornflake · 2012-08-27 03:39 I don't think you'd need to read all the records for the filter - I think Bill was saying they only read far enough ahead to fully populate the grid. Doesn't matter, somethings not right - DB Grid shouldn't fall over, even if RecordCount is wrong.  Mind you, Delphi's DBGrid scrollbar was awful, and I'm guessing we're dancing around the very issue.  I cring when I see commercial apps that implement it. Gosh I miss DevExpress :-) No need to open any new ticket - the one I posted is still open.  Waiting on a Test Case... ---------------------------------------------------------------------- teos · 2012-08-27 19:42 Quote from: Mike.Cornflake on August 27, 2012, 05:39:07 amI don't think you'd need to read all the records for the filter - I think Bill was saying they only read far enough ahead to fully populate the grid. Doesn't matter, somethings not right - DB Grid shouldn't fall over, even if RecordCount is wrong.  Mind you, Delphi's DBGrid scrollbar was awful, and I'm guessing we're dancing around the very issue.  I cring when I see commercial apps that implement it. Gosh I miss DevExpress :-) No need to open any new ticket - the one I posted is still open.  Waiting on a Test Case... Then I wonder how one can determine which records meet the filter and all are read without reading the dataset or dertermine the records. And if one has to implement a scrollbar like DBGrid, one has to find out somehow how far the scrollbar should be able to scroll. I think from 15 years of experience with the standard DBGrid that there is nothing wrong with it, as long as the dataset feeding the grid is inherited properly. And my effords with TDataset tell me that the grid works okay as long as the Dataset works OK. Brings me back to my first remark: how would one determine records that meet the filter, count them and give that number as recordcount when "filtered = true" and expect a scrollbar to behave correct in the situation where that is not known? Tell me.. DevExpress.. 5000 pages of manual.. I then wonder why not use the VirtualTree then. ---------------------------------------------------------------------- Knipfty · 2012-08-27 20:13 Mike/teos, From what I understand, DBGrid is really dependent on the datasource it is attached to.  It is up to the datasource (really, the TTable or TQuery object) to pull in the records.  In reading up on a lot of the documentation on these components, it is up to TTable and TQuery to manage what is brought into memory. These componenents may or may not read all records, but buffer them in order to limit memory usage.  It's one of the reasons why you cannot depend on .RecordCount.  And why you cannot compare RecNo to RecordCount to determine if you are at the last record. Perhaps a way around this would be to make calls to .Last and .First.  That may force TQuery and TTable to load all the records.  I'm just guessing, you'll have to test it out. Knipfty ---------------------------------------------------------------------- Mike.Cornflake · 2012-08-27 22:37 Knipfty said: QuoteThese componenents may or may not read all records, but buffer them in order to limit memory usage.  It's one of the reasons why you cannot depend on .RecordCount.  And why you cannot compare RecNo to RecordCount to determine if you are at the last recordYup, that's my understanding as well, and is why the Delphi DBGrid scrollbar was so awful.  It never assumed it knew anything about the number of records, so never resized itself to give you a clue as to result size.  Dragging the scrollbar down simply loaded the next set of results in.  Mind you, this is a 10 year old memory, things may have changed with later releases... Mind you, knowing what I do is wrong, doesn't stop me from trying it to see if I can useful fuctionality :-)  I was pleasantly surprised when the Zeoslib components worked just fine, and immediately used that to implement a scrollbar that behaves as I expect scrollbars to work. I suspect we'll find that the bug is not within the TSQLQuery - that really looks like functionality by design.  It's DBGrid making assumptions that results in a more useful scrollbar.  And in truth I'll take useful scrollbar behaviour over simple filtering any day.  I've now implemented my filtering at the SQL level (as suggested earlier in this thread).  Means less generic code, but I'm no longer coding in a professional environment, so I can live with that. Which is the reason I miss DevExpress grids.  No filtering was ever done on the dataset, all filtering was done by the grid.  And this feels right to me.  How they achieved their phenomenal speeds on large datasets is beyond my understanding, and is why I never begrudged paying their costs. QuotePerhaps a way around this would be to make calls to .Last and .First.  That may force TQuery and TTable to load all the records.  I'm just guessing, you'll have to test it out.Should have reported this earlier.  Tried this early on, with no effect. You know, I've never, ever, used TTable :-)  All my database work has been with normalised schemas.  I cannot tell you if filtering can be done on a TTable, and if it can, whether this issue reproduces there as well. ---------------------------------------------------------------------- Mike.Cornflake · 2012-08-27 22:45 Teos said: QuoteBrings me back to my first remark: how would one determine records that meet the filter, count them and give that number as recordcount when "filtered = true" and expect a scrollbar to behave correct in the situation where that is not known? Tell me.. Can't :-)  There you have the nub of the matter.  We've one of two issues.  Either TSQLQuery should report a correct RecordCount (and that calculation can be expensive) OR DBGrid should never assume RecordCount is correct.  In fact, I vaguely remember some ADO code that never populated RecordCount at all unless a certain call was made.  QuoteDevExpress.. 5000 pages of manual..Ah, but what riches await the person who reads that manual? :-)   And I never did, there was a nice set of samples that came with it, so I just opened up the appropriate sample and worked out what I needed from there. QuoteI then wonder why not use the VirtualTree then. No idea, never used it.  Are you recommending it for the display/filtering of datasets?  If so, I'll go play - sounds intriguing.  Is this what you're talking about?  http://wiki.lazarus.freepascal.org/VirtualTreeview ---------------------------------------------------------------------- teos · 2012-08-27 23:06 Quote from: Mike.Cornflake on August 28, 2012, 12:45:04 amTeos said: QuoteBrings me back to my first remark: how would one determine records that meet the filter, count them and give that number as recordcount when "filtered = true" and expect a scrollbar to behave correct in the situation where that is not known? Tell me.. Can't :-)  There you have the nub of the matter.  We've one of two issues.  Either TSQLQuery should report a correct RecordCount (and that calculation can be expensive) OR DBGrid should never assume RecordCount is correct.  In fact, I vaguely remember some ADO code that never populated RecordCount at all unless a certain call was made.  Zeoslib works OK because it just does what I say: run a query which gives back all records within the query/filter QuoteDevExpress.. 5000 pages of manual..Ah, but what riches await the person who reads that manual? :-)   And I never did, there was a nice set of samples that came with it, so I just opened up the appropriate sample and worked out what I needed from there. [/quote] Okay when you step in DevExpress on a new project but a PITA if you have to make up large parts of the workings out of someone else's code. QuoteI then wonder why not use the VirtualTree then. No idea, never used it.  Are you recommending it for the display/filtering of datasets?  If so, I'll go play - sounds intriguing. [/quote] VirtualTree could indeed be what you look for in Lazarus. It's not a DevExpress grid but could be very usefull for you. ---------------------------------------------------------------------- Mike.Cornflake · 2012-08-27 23:23 If it's http://wiki.lazarus.freepascal.org/VirtualTreeview you're talking about then bugger - I'm currently working on a vessel in the north sea, and sourceforge is one of the blocked sites :-(  Have to wait until I'm home again... QuoteOkay when you step in DevExpress on a new project but a PITA if you have to make up large parts of the workings out of someone else's codeTrue, DexEv was not an intuitive set of controls to use...  I miss all that extra functionality you got over a normal grid though... ---------------------------------------------------------------------- taazz · 2012-08-27 23:57 Quote from: Mike.Cornflake on August 28, 2012, 01:23:14 amIf it's http://wiki.lazarus.freepascal.org/VirtualTreeview you're talking about then bugger - I'm currently working on a vessel in the north sea, and sourceforge is one of the blocked sites :-(  Have to wait until I'm home again... I would download code typhoon instead it come with a number of extra components including tvirtualtreeview and a few virtualdbgrids and trees it will cut your development time considerably. The virtualtreeviews component pack do not have any DB aware components. http://www.pilotlogic.com/sitejoom/index.php/codetyphon/codetyphon- download ---------------------------------------------------------------------- Mike.Cornflake · 2012-08-28 00:21 Nice  :-)  I *love* open source projects.  So, someone has taken Lazarus/FreePascal and a whole slew of open components and packaged them for simple release. Shame about the 450Mb size though.  Although that download isn't blocked, I wont try it here.  One quick way of getting booted off a vessel ---------------------------------------------------------------------- xico · 2012-08-28 01:44 Hi all! After some weeks away I came back yesterday to the forum and I saw alot of comments and ideas... Well, my conclusion is, I won't use the Filter together with the DBGrid as long as the RecordCount issue isn't corrected. Anyhow, yesterday I was playing around with it and I came with a WORKAROUND. The idea is: Disable the DBGrid Vertical Scrollbar and use a TScrollbar insteadUse a TStringList (or a TList/TArray) to keep tracking of the Records after applying the Filter And this is just the pick of the iceberg. I didn't work on things like: catching exceptions, ie: if the Filter returns zero resullts it will throw a "Scrollbar Out of Range"syncing the DBNavigator with the TScrollbaradd Mouse ScrollWheel logic to the TScrollbar componentset the TScrollbar thumb height to visually match the number of Rows on the Gridetc. Some of these are important other are optional. For my test I ceated a SQLLite test table with two columns, a "ID" (Int, Auto_Increment) and a "DUMMY" (Int). I inserted about 50 records with values of 3,8 and 12. The filter I want to apply is "dummy =" and then use 3,8 or 12. The components are: SQLite3Connection1SQLTransaction1SQLQuery1 (Main Query)SQLQuery2 (to fill ComboBox1 with "DISTINCT" values from the "dummy" column)Datasource1DBGrid1 (where Scrollbars:= ssHorizontal. No vertical Scrollbar!)ScrollBar1ComboBox1 (filled with the values 'OFF' plus the result of SQLQuery2)Edit1 (TEdit component to show the total number of rows) The main procedure here is the one called "SetupScrollbar" wich fills a TStringList with the RecNos that are then used to feed the Scrollbar: Code: [Select]procedure TForm1.SetupScrollbar;   var i: Integer; begin   with SQLQuery1 do     if Filtered then     begin  // FILTER is ON       RecList.Clear;       i:= -1;       DisableControls;       First;       while not EOF do       begin         Inc(i);         RecList.Add(IntToStr(RecNo));         Next;       end;       ScrollBar1.Min:= 0;       ScrollBar1.Max:= RecList.Count-1;       Edit1.Text:= IntToStr(RecList.Count);       First;       EnableControls;     end  // --------------------------------     else     begin  // FILTER is OFF       ScrollBar1.Min:= 1;       ScrollBar1.Max:= RecordCount;       Edit1.Text:= IntToStr(RecordCount);       First;     end; end; CONTINUATION -> ---------------------------------------------------------------------- xico · 2012-08-28 01:53 -> CONTINUING the whole thing is: Code: [Select]unit Unit1; {$mode objfpc}{$H+} interface uses   sqldb, sqlite3conn, db, Forms, DBGrids, StdCtrls, Classes, SysUtils; type   { TForm1 }   TForm1 = class(TForm)     ComboBox1: TComboBox;     DBGrid1: TDBGrid;     Datasource1: TDatasource;     Edit1: TEdit;     Label1: TLabel;     Label2: TLabel;     ScrollBar1: TScrollBar;     SQLite3Connection1: TSQLite3Connection;     SQLQuery1: TSQLQuery;     SQLQuery2: TSQLQuery;     SQLTransaction1: TSQLTransaction;     procedure ComboBox1Select(Sender: TObject);     procedure FormCreate(Sender: TObject);     procedure FormDestroy(Sender: TObject);     procedure ScrollBar1Scroll(Sender: TObject; ScrollCode: TScrollCode;       var ScrollPos: Integer);     procedure SQLQuery1AfterScroll(DataSet: TDataSet);   private     { private declarations }     RecList: TStringList;     procedure SetupQuery1;     procedure SetupQuery2;     procedure SetupScrollbar;   public     { public declarations }   end; var   Form1: TForm1; implementation {$R *.lfm} { TForm1 } procedure TForm1.SetupQuery1; begin   with SQLQuery1 do   begin     Close;     Filtered := False;     SQL.Clear;     SQL.Text := 'SELECT * FROM filter';     Open;     First;   end; end; procedure TForm1.SetupQuery2; begin   with SQLQuery2 do   begin     Close;     SQL.Clear;     SQL.Text := 'SELECT DISTINCT dummy FROM filter ORDER BY dummy ASC';     Open;     First;     with ComboBox1 do     begin       Clear;       Items.Add('OFF');       while not EOF do       begin         Items.Add(FieldValues['dummy']);         Next;       end;       ItemIndex:= 0;     end;     Close;   end; end; procedure TForm1.SetupScrollbar;   var i: Integer; begin   with SQLQuery1 do     if Filtered then     begin  // FILTER is ON       RecList.Clear;       i:= -1;       DisableControls;       First;       while not EOF do       begin         Inc(i);         RecList.Add(IntToStr(RecNo));         Next;       end;       ScrollBar1.Min:= 0;       ScrollBar1.Max:= RecList.Count-1;       Edit1.Text:= IntToStr(RecList.Count);       First;       EnableControls;     end  // --------------------------------     else     begin  // FILTER is OFF       ScrollBar1.Min:= 1;       ScrollBar1.Max:= RecordCount;       Edit1.Text:= IntToStr(RecordCount);       First;     end; end; // ------------------------------------------- procedure TForm1.FormCreate(Sender: TObject); begin   RecList:= TStringList.Create;   SetupQuery1;   SetupQuery2;   SetupScrollbar; end; procedure TForm1.FormDestroy(Sender: TObject); begin   RecList.Free; end; // ------------------------------------------- procedure TForm1.ComboBox1Select(Sender: TObject); begin   if TComboBox(Sender).Text = 'OFF' then   begin     SQLQuery1.Filtered:= False;     SetupScrollbar;   end   else     with SQLQuery1 do     begin       Filter:= 'dummy = '+ ComboBox1.Text;       Filtered:= True;       SetupScrollbar;     end; end; procedure TForm1.ScrollBar1Scroll(Sender: TObject; ScrollCode: TScrollCode;   var ScrollPos: Integer); begin   if SQLQuery1.Filtered then     SQLQuery1.RecNo:= StrToInt(RecList[ScrollPos])   else     SQLQuery1.RecNo:= ScrollPos; end; procedure TForm1.SQLQuery1AfterScroll(DataSet: TDataSet); begin   if SQLQuery1.Filtered then     ScrollBar1.Position:= RecList.IndexOf(IntToStr(DataSet.RecNo))   else     ScrollBar1.Position:= DataSet.RecNo; end; end. As I said, this is a workaround but might be useful for someone out there. Zip file attached, 3 parts! Exe, db and sqlite.dll. Best regards for all you people. ---------------------------------------------------------------------- xico · 2012-08-28 01:55 Sorry for the extra entries, it's for the zip files ---------------------------------------------------------------------- xico · 2012-08-28 01:56 The last one! ---------------------------------------------------------------------- teos · 2012-08-28 19:36 I wonder: what about the onfilter event? If I'm correct that is executed for every record to be matched. My wonder: how about a variable which counts the number of times the event is executed? If I'm correct, the event is triggered from TDataset at the part where records are fetched. ---------------------------------------------------------------------- Mike.Cornflake · 2013-09-17 03:47 Quote from: xico on July 21, 2012, 05:43:50 amHello community! Specs: Lazarus 1.1 (2012-05-11) FPC 2.6.1 (SVN Rev. 37249) Windows XP SP3 ---- MySQL 5.1 on a Linux Server -------------------------------------- Usually I make my filtering direct with SQL but I wanted to try the Filter property of TSQLQuery. I made some search but I couldn't find an answer for my problem. This is the problem: after applying a filter on a SQLQuery, if I scroll the DBGrid by grabbing the DBGrid scrollbar I get the following error: "SQLQueryMain : Could not find the requested record". Many moons ago I said I'd create a test application that reproduces this issue.  For ease of distribution, I created the test application using sqlite.   The original poster saw this issue with MySQL & with SQLite.  I've seen it with MS SQL (via ODBC (but you'll have to take my word for it, creating a test for MS SQL is useful only to people with SQL Server hanging around)) and now this test code reproduces the issue again with SQLite.    However, before I dig further, or post on the bug tracker, could someone please confirm I'm not doing something stupid with filtering? My filtering code is nothing more than... Code: [Select]procedure TForm1.btnFilterClick(Sender: TObject); begin   If SQLQuery1.Active Then   Begin     SQLQuery1.Filtered:= False;     SQLQuery1.FilterOptions:=[];     SQLQuery1.Filter  := 'Field2 < 500';     SQLQuery1.Filtered:= True;   end; end; Here's the complete project, including pre-compiled exe (win 32) and sqllite3.dll.  Run the exe.  Press btnConnect once.  Press btnAddRecords twice (each time adds 10,000 records).  Press btnFilter.  Try to scroll the DBGrid using the vertical scrollbar (grab the thumb, drag to a new position and let go.)  Dragging may work a few times, but you'll eventually hit the problem (obviously when you finally end up on a record that the DBGrid thinks is still in the TDataset, but which the TDataset has filtered out).  Exactly as the original poster reported. Click here to download the project... http://db.tt/L9bzree8 (https://dl.dropboxusercontent.com/u/59503182/Lazarus/DBGrid%20Error/D BGrid_Error.7z for those who don't like shortened URLs) The project was built using Lazarus 1.0.8/FPC 2.6.2.  My SVN copy is 64 bit, and I couldn't find a 64bit sqlite.dll to connect with, so I was unable to test with latest... Previously we got bogged down with assumptions it was RecordCount being wrong and other such.  I'm no longer convinced.  I think the OP and I are either using Filtering incorrectly, or that we do in fact have an issue with DBGrid. Looking for the following assistance: * Clarification .Filter is being used correctly... * If .Filter is being used correctly, then testing with SVN Lazarus to see if the issue has since been solved. Many thanks, and sincere apologies for taking OVER a year before I remembered to investigate this further... :-( UPDATE:  Oh, that's interesting.  The issue doesn't appear to occur until you first drag the scrollbar thumbnail right to the very end of the grid.  Thereafter the issue occurs reasonably quickly.... ---------------------------------------------------------------------- BigChimp · 2013-09-17 05:51 Re filtering syntax: AFAIU/AFAIR sqldb's bufdataset is used for keeping track of the data displayed by the Lazarus data controls (but it has been a while since I looked & I'm not a GUI guru). Bufdataset uses the same filtering code as tdbf. TDBF (filtering) documentation: http://sourceforge.net/projects/tdbf/files/TDbf%20documentation/TDbf%2 0documentation%201.1/ ---------------------------------------------------------------------- Mike.Cornflake · 2013-09-17 06:08 Ta for the info.  Looking now... I can tell you that the error is only raised in two places.  I can't remember the second, it seemed very unlikely.  But the likely point the error was raised was TBufDataset.SetRecNo, so the documentation you've pointed me should be relevant... Quick question:  How I debug lcl and fp units?  (ie step into?) Used to happen for me, but somewhere along the line I've unticked something critical, and I canna find it again... ---------------------------------------------------------------------- BigChimp · 2013-09-17 06:26 Quote from: Mike.Cornflake on September 17, 2013, 08:08:49 amQuick question:  How I debug lcl and fp units?  (ie step into?) Used to happen for me, but somewhere along the line I've unticked something critical, and I canna find it again... Compiling FPC and Lazarus with something like fpcopt=-g -gw and lazopt=-g -gw in your make statements/equivalent options for lazarus (obviously, I'm an fpcup user so have set it in fpcup) to get debug info into the FPC&Laz PPUs. However, in at least on my case, I haven't been able to debug x86 Win FPC trunk applications for a long time now, perhaps due to bugs such as http://bugs.freepascal.org/view.php?id=20296 Recent changes in dwarf crash heaptrc in lnfodwrf.pp (from 2011!) Fortunately, since a while, x64 Win FPC debugs nicely again, so I use that. Linux seems to be a bit better. ---------------------------------------------------------------------- Mike.Cornflake · 2013-09-17 07:29 @Big Chimp - you're the MAN :-)   Once again, many thanks :-) ---------------------------------------------------------------------- Mike.Cornflake · 2013-09-19 05:36 I'm now convinced the Filtering code is correct.  I've no clue what's going on, as I just can't get debugging of fpc code working. I've raised an issue in the tracker... http://mantis.freepascal.org/view.php?id=25052 ---------------------------------------------------------------------- jwhitten · 2013-09-20 15:42 Hello, I am not certain the issue has anything at all to do with TSQLQuery or Filtering. I have an application I'm developing which has this very same issue with the scrolling of TDBGrid-based grids and I'm using Zeos DB lib and no filtering at all. (See my post here: http://forum.lazarus.freepascal.org/index.php/topic,22093.0.html) In contrast, I have also experimented with TKDBGrid and do not experience the problem _at all_. In reading through the various comments here, I can see how a disconnect between the dataset and the grid (with respect to row count) could be an issue, but I don't think it is necessarily related to the specific components which have been identified, but rather to the grids themselves. Although the issue could certainly be exacerbated through lack of proper communication of the row counts for sure. Has there been any progress made on this front? Is there any place else that I could go to get more information on the issue? Thanks, John Whitten ---------------------------------------------------------------------- Mike.Cornflake · 2013-09-21 00:15 @jwhitten:  To be clear:  Are you saying that you are reproducing the "SQLQueryMain : Could not find the requested record" issue outside of the TSQLQuery.Filter/TDBGrid combo, or are you just commenting on the TDBGrid Scrollbar issues? This Topic has been muddied over time (mainly by me ), but should be focused on the "SQLQueryMain : Could not find the requested record" issue. I'll reply to your TDBGrid Scrollbar issues over in your Topic   Very glad you raised them in a different topic, as that helps differentiate the two issues... UDPATE: QuoteHas there been any progress made on this front? Is there any place else that I could go to get more information on the issue? On the "SQLQueryMain : Could not find the requested record" issue?  Progress was delayed for a year by me forgetting to post a sample app.  I've now posted this, and added a BugReport.  No news yet, other than your addition.  Many thanks for that, I agree with your assessment. On the wonky DBGrid scrollbar issue?  It's not officially a bug.  I've explained in your Topic, and offered the workaround I use... ---------------------------------------------------------------------- LacaK · 2013-09-23 09:01 Can you please check your version of dbgrids.pas ? When I use your test application in bug report I can reproduce bug with your EXE byt I cann't reproduce when I recompile application. Please look in dbgrids.pas at method: TCustomDBGrid.WMVScroll Do you see there: Code: [Select]IsSeq := FDatalink.DataSet.IsSequenced and not FDataLink.DataSet.Filtered; and then Code: [Select]else if IsSeq then       FDatalink.DataSet.RecNo := aPos + 1 So for filtered dataset RecNo should be never called ... ---------------------------------------------------------------------- Mike.Cornflake · 2013-09-23 23:58 I can confirm in Lazarus DBGrid 1.0.8, line 1208 says Code: [Select]IsSeq := FDatalink.DataSet.IsSequencedand the bug reproduces. I can confirm that in Lazarus 1.1.99 line 1268 (obviously lots of other changes have occurred as well) Code: [Select]IsSeq := FDatalink.DataSet.IsSequenced and not FDataLink.DataSet.Filtered;and I cannot reproduce the bug. While there is no exception raised, there is now a new minor nusiance.  You cannot effectively drag the scrollbar thumb to locate within a filtered a dataset.  The moment you do, you are taken directly to the last record.  All the other scrollbar behaviour works as expected. So, the serious bug is fixed, and a minor UI nuisance has replaced it.  Definitely progress in the right direction.  I'll see about getting the bug tracker issue closed, and I'll raise a new one for the much more minor UI issue. Many thanks. UPDATE: :-(  This was fixed back in October 2012!!  I could have sworn I checked this in 1.0.12 :-(   Sorry for wasting your time. I have requested that the bug be closed.  I don't seem to have permission to do so. UPDATE to UPDATE:  Oh, I think I see.  I'm guessing bug fix not back- ported to the 1.0.x series.  I should have done my testing with SVN latest :-(  Again, apologies... ---------------------------------------------------------------------- LacaK · 2013-09-24 05:29 Quote from: Mike.Cornflake on September 24, 2013, 01:58:41 amWhile there is no exception raised, there is now a new minor nusiance.  You cannot effectively drag the scrollbar thumb to locate within a filtered a dataset.  The moment you do, you are taken directly to the last record. Yes I also noticed this strange behavior  Quote from: Mike.Cornflake on September 24, 2013, 01:58:41 amUPDATE to UPDATE:  Oh, I think I see.  I'm guessing bug fix not back-ported to the 1.0.x series.  I should have done my testing with SVN latest :-(  Again, apologies... You can request backporting in Lazarus: http://wiki.lazarus.freepascal .org/Lazarus_1.0_fixes_branch#Submitted_by_developer_.2F_committer