TIBDynamicGrid - Query with where dows not display. lazarus / 16-Databases ---------------------------------------------------------------------- Nicole · 2024-02-12 11:45 There is a TIBDynamicGrid and a button for display data inside it. The code below is part of this buttonClick. This does display data 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   IBQuery_Kupons.SQL.Text:='Select ID_KUPON, DATUM,BETRAG, FK_EINKOMMEN ' +                           ' From TBKUPONS';  IBQuery_Kupons.Active:=true; This does not displays data 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   IBQuery_Kupons.SQL.Text:='Select ID_KUPON, DATUM,BETRAG, FK_EINKOMMEN ' +                           ' From TBKUPONS where FK_EINKOMMEN = :FK_EINKOMMEN';  IBQuery_Kupons.ParamByName('FK_EINKOMMEN').AsInteger:=FK_EINKOMMEN;  IBQuery_Kupons.Active:=true; The difference is the "where". And yes, I made sure, the parameter is working by enforcing a value. To my mind the trouble occurs where the datasource connects to the query and the DBGrid connects to the Datasource. In the direct input of the query there is NO where-clause. One year ago I had the problem as well, but I cannot remember the solution and my postings containing "where" are 4 pages. Please be so kind to tell me again. It was anything with an internal storage of the queries and components. The solution was not so hard but not very intuitive neither. ---------------------------------------------------------------------- rvk · 2024-02-12 11:51 What type is FK_EINKOMMEN? Are you sure ut is INTEGER in the database? Are you sure the given variable FK_EINKOMMEN contains an integer present in the table? ---------------------------------------------------------------------- Nicole · 2024-02-12 12:16 yes and yes and yes. The paramenter is not the problem, at least I am sure about it at the moment. I should have something to do with IBQuey, somwhere the SQL is saved not as it should / or as I want it. This "Spalteneditor" and "Feldeditor" and "SQL-Editor" which needs to be refreshed at every change. Unfortunately this time I do not find out, which code has to be deleted or added although not needed. ---------------------------------------------------------------------- rvk · 2024-02-12 12:23 Then you are not showing all the relevant code. If you fill the parameter with an existing integer for FK_EINKOMMEN then it should just work. But you might have filled IBQuery with other things and as long as we don't see with what, it's just guessing (AGAIN). Maybe you filled TIBQuery.Datasource. In that case your parameters are taken from that Datasource and not your assigned parameter. Quote from: Nicole on February 12, 2024, 01:16:43 pmThis "Spalteneditor" and "Feldeditor" and "SQL-Editor" which needs to be refreshed at every change. Unfortunately this time I do not find out, which code has to be deleted or added although not needed. BTW. Seeing that... you probably SHOULD work with TIBQuery.Datasource. Then with every change of the current record in Datasource, your SQL will automatically follow that record depending on the WHERE. You shouldn't use ParamByName in that case on each change. But we see way too little of your program and code to make a judgement here. You might want to explain what components you have and how they should interact with each other. ---------------------------------------------------------------------- tonyw · 2024-02-12 12:35 Quote from: Nicole on February 12, 2024, 12:45:55 pmThere is a TIBDynamicGrid and a button for display data inside it. The code below is part of this buttonClick. This does display data 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   IBQuery_Kupons.SQL.Text:='Select ID_KUPON, DATUM,BETRAG, FK_EINKOMMEN ' +                           ' From TBKUPONS';  IBQuery_Kupons.Active:=true; This does not displays data 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   IBQuery_Kupons.SQL.Text:='Select ID_KUPON, DATUM,BETRAG, FK_EINKOMMEN ' +                           ' From TBKUPONS where FK_EINKOMMEN = :FK_EINKOMMEN';  IBQuery_Kupons.ParamByName('FK_EINKOMMEN').AsInteger:=FK_EINKOMMEN;  IBQuery_Kupons.Active:=true; The difference is the "where". And yes, I made sure, the parameter is working by enforcing a value. To my mind the trouble occurs where the datasource connects to the query and the DBGrid connects to the Datasource. In the direct input of the query there is NO where-clause. One year ago I had the problem as well, but I cannot remember the solution and my postings containing "where" are 4 pages. Please be so kind to tell me again. It was anything with an internal storage of the queries and components. The solution was not so hard but not very intuitive neither. You need to move the "ParmByName" line to the IBQueries BeforeOpen event handler. It needs to be there so that it is called everytime the IBDynamicGrid requeries the dataset. ---------------------------------------------------------------------- Nicole · 2024-02-12 13:07 At the moment I have it like this: 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    FK_EINKOMMEN:= 1;  IBQuery_Kupons.SQL.Text:='Select ID_KUPON, DATUM,BETRAG, FK_EINKOMMEN ' +                           ' From TBKUPONS where FK_EINKOMMEN = :FK_EINKOMMEN';  IBQuery_Kupons.ParamByName('FK_EINKOMMEN').AsInteger:=FK_EINKOMMEN;   IBQuery_Kupons.Active:=true;  To use "open" instead of "active" results in a stack overflow. And keying in the same SQL into FlameRobin - works. ---------------------------------------------------------------------- rvk · 2024-02-12 13:21 Quote from: Nicole on February 12, 2024, 02:07:37 pmTo use "open" instead of "active" results in a stack overflow. That's really strange because they should be the same. Did you check if TIBQuery.Datasource is NOT used at the moment? ---------------------------------------------------------------------- Nicole · 2024-02-12 14:17 Rechecked, I cannot find any use. I used the full-search to find a line and the object insspector ti check my Grids and sets. Three of them, so I should have seen it. Can you recommend any code around? Before the sql.text= and after it? Perhaps I can avoid the situation at runtime, if something is taken over wrongly from design-time. PS: There must be a bug inside the use. My other query worked perfectly alright, suddenly I see stack overflow. The thing, which may be wrong, are that I used its parts in further methods. e.g. id_einkommen:=IBQuery_EinAssetID_EINKOMMEN.AsInteger; Can you imagine a problem there? Sorry, I cannot post my whole code, this never will work because it is nested to deeply in my environment. I think, I will go ahead and avoid this constructs and use instead a second query getting my values new. Is this a good idea? Not too many fields involved. ---------------------------------------------------------------------- rvk · 2024-02-12 14:44 Quote from: Nicole on February 12, 2024, 03:17:05 pmRechecked, I cannot find any use. I used the full-search to find a line and the object insspector ti check my Grids and sets.If used, it would have probably be seen in the Object Inspector for the IBQuery. But you code is correct. If the query was already open you would have gotten an error on Active := true; BTW. It's still strange that you get a crash on Open. Quote from: Nicole on February 12, 2024, 03:17:05 pmid_einkommen:=IBQuery_EinAssetID_EINKOMMEN.AsInteger;You said that hard setting it to 1 also didn't work. Quote from: Nicole on February 12, 2024, 03:17:05 pmSorry, I cannot post my whole code, this never will work because it is nested to deeply in my environment. I think, I will go ahead and avoid this constructs and use instead a second query getting my values new. Is this a good idea? Not too many fields involved. If this is a master/client dataset you should actually work with IBQuery.Dataset. Then you don't even need to set the parameter for ID_EINKOMMEN. You can just step through the master dataset and the IBquery will follow (if you set IBQuery.Dataset correctly). BTW. You say you used IBQuery_EinAssetID_EINKOMMEN.AsInteger. This seems to be a hard coded field. So if some of your hardcoded fields are incorrect, it could mess up things. Didn't we suggest you shouldn't use hardcoded fields earlier? What fields are defined if you right click on IBQuery_Kupons and choose Edit fields?????? ---------------------------------------------------------------------- Nicole · 2024-02-12 19:56 There is another topic involved: Queries returning ONE results and queries returning MANY results: The system has 2 tables, tbeinkommen and tbkupons. And 3 results to list / edit: 1) first task: One Query shows many rows of tbeinkommen. The results are displayed in at DBDynamicGrid. 2) second task: ONE row of tbeinkommen is split into many DBEdit fields, which can be edited one by one. 3) third task: The tbKupon has a field fk_einkommen,  which is a foreign key at id_einkommen. The second DBDynamicGrid lists the rows of ONE of the fk_einkommen and shows MANY lines of tbkupons. ====================== This is what you see in the above code is the third task: The WHERE is the selection of the specific foreign key. But out of some reason, task ONE does not work any more, what it did fine already. Unfortunately it is very likely that they interact anywhere. But how to find where? And there is potential for mess, sure: The primary key for tbeinkommen is used for 3 times: As multy-request, as single-request and as foreign key. I made 3 separate TBQueries for this reason. Checked many times for NOT mixing them up, but may be I overlooked anything. About "hard coded". You mean, that I should not write the code at design time? There was a very old thread, where I got the hint, that those SQL fields must not left blank. This was a time of the previous IBX version. After having clicked on "generate code" that far away day, something worked, although I changed the SQL-text after this as well. PS: I found something very strange, what I never have seen before. You remember my troubles with the database being too slow? I was given this very line "select * from RDB$INDICES where RDB$SYSTEM_FLAG = 0" I have no idea, what it means. The only thing, I understood, that there must not be inactive indices. To check this from time to time, i saved the query. And today I called it again and I saw a strange entry. Can this be the solution to this thread? There is an entry for a foreign key, which I do not understand. I have a lot of foreign keys, but none of them is listed in that way. I attach you the screenshot as I am quite sure, YOU understand it. ---------------------------------------------------------------------- Nicole · 2024-02-12 20:07 did I mess the tbtable? ---------------------------------------------------------------------- rvk · 2024-02-12 20:09 Quote from: Nicole on February 12, 2024, 08:56:57 pm3) third task: The tbKupon has a field fk_einkommen,  which is a foreign key at id_einkommen. The second DBDynamicGrid lists the rows of ONE of the fk_einkommen and shows MANY lines of tbkupons.And this should be done via the IBQuery.Datasource property. If you fill the datasource of that id_einkommen table in the datasource of the tbKupon one, you don't even need to write any code. The parameter :fk_einkommen is automatically taken from that datasource and that query is updated automatically when the record in the query for tbfeinkommen changes. That's the whole point of that IBQuery.Datasource property. Read the docs about it. You'll see it will make your life/programming a lot easier. Quote from: Nicole on February 12, 2024, 08:56:57 pmAnd there is potential for mess, sure: The primary key for tbeinkommen is used for 3 times: As multy-request, as single-request and as foreign key. I made 3 separate TBQueries for this reason.And when you do that, you don't have to write a single line of code to make that work (if you connect them correctly). Quote from: Nicole on February 12, 2024, 08:56:57 pmThere was a very old thread, where I got the hint, that those SQL fields must not left blank. This was a time of the previous IBX version. After having clicked on "generate code" that far away day, something worked, although I changed the SQL-text after this as well. Did you right click and looked at the field? Can you tell me which field are there in that IBQuery_Kupons? Because open instead of active shouldn't give you a stock overflow error. ---------------------------------------------------------------------- Nicole · 2024-02-13 12:45 I found a way to work with it, but I do not understand why. "Open" does not give a stack overflow any more. I cared to give every tasks its own method. But the rest stays strange: 2 versions: displays data in the DBDynamicGrid 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   s:='Select A.ID_KUPON, A.DATUM, A.BETRAG, A.FK_EINKOMMEN From TBKUPONS A where FK_EINKOMMEN = ' + IntToStr(1);    IBQuery_Kupons.SQL.Text:= s;  IBQuery_Kupons.Open; leaves the Grid empty: 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   s:='Select A.ID_KUPON, A.DATUM, A.BETRAG, A.FK_EINKOMMEN From TBKUPONS A where FK_EINKOMMEN = :X';  IBQuery_Kupons.SQL.Text:= s;  IBQuery_Kupons.ParamByName('X').AsInteger:= 1;   IBQuery_Kupons.Open; Perhaps I have not seen a thing. I tried a lot, e.g. changed the order of the fields, re-added and so much more. This is a way for me to work with it. "solved" it is not. Last Update: It works. With ParamByName as well as without. And with a nicely done DisplayFormat, which had troubled me as well. What I did? I changed the TDBDynamicGrid to TDBGrid. A pity! I like the sorting option much. ---------------------------------------------------------------------- rvk · 2024-02-14 14:42 Quote from: Nicole on February 13, 2024, 01:45:59 pm Last Update: It works. With ParamByName as well as without. And with a nicely done DisplayFormat, which had troubled me as well. What I did? I changed the TDBDynamicGrid to TDBGrid. A pity! I like the sorting option much. I don't know how TDBDynamicGrid works (never used it). BUT... did you see the post from Tony? Quote from: tonyw on February 12, 2024, 01:35:59 pmYou need to move the "ParmByName" line to the IBQueries BeforeOpen event handler. It needs to be there so that it is called everytime the IBDynamicGrid requeries the dataset. Did you do that? (move the ParamByName to the IBQueries BeforeOpen ???) If you don't, then that's probably the reason it didn't work in TDBDynamicGrid. Anyway... you could implement the sorting in TDBGrid but you would have to do it yourself through code. ---------------------------------------------------------------------- Nicole · 2024-02-14 15:36 yes, I have seen this, that I must put the Param BEFORE opening. And sure I did. Probably it would not even compile the other way round. This I did not try, ;-) You are German? - "Da ist der Wurm drin." Maybe the problem is, that I used TWO DBDynamicGrids at ONE form. The first one works as it should. The second one behaves just FUZZY in combination with this "where"-condition. Fuzzy means e.g. that it remains empty where it has data, if there is the parambyname there. And if there is added the where condition with IntToStr - the data are there, but not correct in the sense of the where-clause. So if the where is NOT set by parambyname, the Grid is filled, but with the complete dataset instead of the where-filter. And I had problems with the scrollbar. I added it and it was not added. This game I did three times. After this I chose another option for display. This is not worth posting here, however it is fuzzy. As the DBGrid just as replacement works, - so the trouble shall be somewhere in the DBDynamicGrid. Tony has written the DynamicGrid. Unfortunately it is not too well known. Otherwise it would have been tested as verbose as DBGrid. The problem in debug is, that it is fuzzy and usually nested. Yes, this of my unit and the 2 tables are in a way stand-alone, that I can send it to somebody, if he provides me an e-mail which allows attachements. However I am not sure, if the problem will be reproducible. If somebody likes to take time for it, I will do this as well and prepare the files. The unit is a frame, which just must be created on a form. ---------------------------------------------------------------------- rvk · 2024-02-14 15:43 Quote from: Nicole on February 14, 2024, 04:36:13 pmyes, I have seen this, that I must put the Param BEFORE opening. And sure I did. Probably it would not even compile the other way round. No no. You misunderstand. You need to put this in the OnBeforeOpen EVENT. That's something different from "before opening". The BeforeOpen event is executed automatically before Open. And the TIBDynamicGrid does Close and Open itself. So create the OnBeforeOpen event by doub;e clicking the event and put the ParamByName there. Quote from: Nicole on February 14, 2024, 04:36:13 pmAs the DBGrid just as replacement works, - so the trouble shall be somewhere in the DBDynamicGrid.The point is that TDBDynamicGrid changes the SQL statement and need to do a Close and Open for that Query. And doing that will loose the ParamByName. So you need to do the ParamByName in the OnBeforeOpen event so it gets executed every time TDBDynamicGrid calls Open. ---------------------------------------------------------------------- Nicole · 2024-02-14 16:42 ah!!!!!!!!!! Thank you for the explanation. This explains all. Yes, I thought before opening the query, this event I never used. As I have a working version with DBGrid in the meanwhile, I will leave it like this, but save this hint for next time. ---------------------------------------------------------------------- egsuh · 2024-02-15 07:00 Try transaction and prepare explicitly. 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       IBQuery_Kupons.SQL.Text:='Select ID_KUPON, DATUM,BETRAG, FK_EINKOMMEN ' +                               ' From TBKUPONS where FK_EINKOMMEN = :FK_EINKOMMEN';       Transaction.Active := True;      Prepare;           IBQuery_Kupons.ParamByName('FK_EINKOMMEN').AsInteger:=FK_EINKOMMEN;    IBQuery_Kupons.Active:=true; ---------------------------------------------------------------------- rvk · 2024-02-15 07:47 Quote from: egsuh on February 15, 2024, 08:00:55 amTry transaction and prepare explicitly. That won't work because the TIBDynamicGrid changes the SQL internally (because of the sorting etc). At that moment the (implicit or explicit) prepare is undone and parameter is lost. So this has to be done in BeforeOpen (in case you work with a TIBDynamicGrid).