TDBLookupcombobox seems useless, unless there is a solution though. lazarus / 16-Databases ---------------------------------------------------------------------- Fantablup · 2024-03-25 18:32 I was starting to use the TDBLookupCombobox, and encountered some issues that results i cannot use it. I have to create a array of record instead and use it in a TCombobox instead. The issue is this: I need to have three combo boxes. The first is countries, second is counties and last one is cities. I need to add to the top in the list in counties and cities combo box. I need the add: << All counties >, and << All cities >>. This seems impossible with the TDBLookupCombobox. I tried to add it with SQL and union, and that worked, but some counties and cities starts with ' character, and then the All counties and All cites will not be on top. So i have now used the standard tcombobox instead for this, and that is working good. Also one issue i encountered. The TCombobox and TDBLookupCombobox dont trim the text. And if the string is like this "Test string                                      ". it will not trim it, so from database retrieval it will have all the spaces in the combo box, and this results in multilines in each item in the combobox. So, in the standard combobox, i first trim the text before i add it, and then all the items shows normal. All other languages automatically trim it, but not here Just some info that i think is very useful. Best Regards everyone Viggo ---------------------------------------------------------------------- rvk · 2024-03-25 18:38 Quote from: Fantablup on March 25, 2024, 07:32:56 pmI need the add: << All counties >, and << All cities >>. This seems impossible with the TDBLookupCombobox. It is possible but you need to add "all counties" with a dummy index of -1. All other records have a dummy index of 1. Now you can sort on dummy index + name of county and only take the name for view. If you need help them show the sql you have now. ---------------------------------------------------------------------- Fantablup · 2024-03-25 18:41 I actually did this. The top item is indexed as -1. But i need it to sort ascending on county names and city names. So it doesn't work. ---------------------------------------------------------------------- rvk · 2024-03-25 18:45 Quote from: Fantablup on March 25, 2024, 07:41:10 pmI actually did this. The top item is indexed as -1. But i need it to sort ascending on county names and city names. So it doesn't work. So, what's the problem? You have 3 different sql. One for country, one for county and one for city. All sorted. You want an extra line a the top. That's possible with a union and adding a dummy index. "All counties" will be on top. Below it will be all the names of the counties in ascending order. Isn't that what you want? ---------------------------------------------------------------------- Fantablup · 2024-03-25 18:49 That is exactly what i did. With union. And it seemed to work good, but after some testing, it didn't. The issue is that if a county or city is starting with a special character this name can jump to the top in the list. Like some names in France, or other countries that have special character at the beginning. ---------------------------------------------------------------------- rvk · 2024-03-25 18:54 Ha, so you mean 's-Hertogenbosch. How would you like that to be sorted? Maybe you can adjust the sorting rule in your database? Or use different collating or use a dummy field by stripping 's- How would you normally sort this in your reports? ---------------------------------------------------------------------- Fantablup · 2024-03-25 19:03 or like this `Ayanot Also it can come new names in the world with special characters that is unknown in the future. No, i will not do any extra work to do some special sorting stuff. It is not worth it. And i dont even know if it will work before testing it. Normally, i just sort it like normal ascending. It is already working very good now when i switched to the standard TComboBox. ---------------------------------------------------------------------- Fantablup · 2024-03-25 19:14 A solution to add an extra to the top without from the database would be a great feature in this component. I can absolutely see many uses of it. ---------------------------------------------------------------------- korba812 · 2024-03-25 22:46 To do this, I use an sql query with a record union containing fixed values, such as (FirebirdSQL): Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT -1 AS ID, '<>' AS COUNTRY FROM rdb$databaseUNIONSELECT ID, COUNTRY FROM COUNTRIES It always gets the item "<>" as the first one. Maybe you sort full result of union query? Also try the TJvDBLookupCombo component - it has "DisplayEmpty" and "EmptyValue" properties that allow you to add an additional item to list - you can assign any name and assign any value to this item. https://wiki.freepascal.org/JVCL_Components#JvDB ---------------------------------------------------------------------- Fantablup · 2024-03-25 22:59 Thanks for helping I actually tried this, and as i said earlier, it seemed to work, but after some testing it did not work good. The problem i told earlier is that if a name starts with a special character, it can jump to the top, and then it is no point. With countries it will work, but not with counties or cities, because some name is starting with a special character. ---------------------------------------------------------------------- Zvoni · 2024-03-26 07:16 Quote from: Fantablup on March 25, 2024, 11:59:26 pmThanks for helping I actually tried this, and as i said earlier, it seemed to work, but after some testing it did not work good. The problem i told earlier is that if a name starts with a special character, it can jump to the top, and then it is no point. With countries it will work, but not with counties or cities, because some name is starting with a special character. This can only happen if there is a sort happening AFTER populating the Combobox. Create a CORRECT SQL-Statement (as the others described) and set any autosort to False. (No idea about TDBWhatever-Control, since i don't use them) ---------------------------------------------------------------------- rvk · 2024-03-26 08:30 Quote from: Fantablup on March 25, 2024, 11:59:26 pmWith countries it will work, but not with counties or cities, because some name is starting with a special character. BTW. With my database (Firebird) and correct Collating, the ' is ignored in the sorting. The ` (other backtick) is too. Only when you have something like 's-Hertogenbosch, and want it to sort on the H instead of s, you need to substitute. BTW. With the correct collating, upper and lowercase are also ignored and just sorted correctly. ---------------------------------------------------------------------- Fantablup · 2024-03-26 14:23 Quote from: Zvoni on March 26, 2024, 08:16:23 amThis can only happen if there is a sort happening AFTER populating the Combobox. Create a CORRECT SQL-Statement (as the others described) and set any autosort to False. (No idea about TDBWhatever-Control, since i don't use them) I didn't try this, but anyway, it is now working with the standard tcombobox. Also, if your suggestion works, it is hard to know about this without searching everywhere. It's working now without tblookupcombobox. Thanks everyone for helping out ---------------------------------------------------------------------- Fantablup · 2024-03-26 20:11 Actually i encountered one more issue on this component. It seams impossible to trim the items before they are inserted in the tbdblookupcombobox. I also tied different trim solutions in th MariaDb SQL, but it dont work. It seems like the component is unable to do any trims, and you cannot do it manually either. This results in multiline in some of the items in the combo box, since it takes the full length of the database column. As i understand, this component is totally useless, if there isn't any solution. ---------------------------------------------------------------------- rvk · 2024-03-26 20:29 Quote from: Fantablup on March 26, 2024, 09:11:49 pmAs i understand, this component is totally useless, if there isn't any solution. No it's not useless. Maybe you are using it wrong. I don't understand what you want to trim. TDBLookupcombobox takes the values from the database. There is no need for trimming. You don't insert anything into it. For the dropdown, values are taken directly from another datasource (read sqlquery). And if you mean viewing of multiple columns... You need to use the correct sql (and maybe trim the field there). Can you make screenshots of exactly what you think the problem is? https://wiki.freepascal.org/TDBLookupComboBox ---------------------------------------------------------------------- Fantablup · 2024-03-26 20:46 Of course This is the listing of a tblookupcombobox. I am testing it now. Sorry for the different things there, but i have to replace the country component with tcombobox too. Right now countries are using tdblookuocombobox, and the others are replaced with tcombobox and now working good. I thought that countries could use tdblookupcombobox, but, no. You can see the two items that becomes multiline? This is not what it should show. It should show in a single line. I cannot understand why these two are multiline either the data in the database on these two items have no extra special chaacter either, only the text showing. ---------------------------------------------------------------------- rvk · 2024-03-26 21:30 Quote from: Fantablup on March 26, 2024, 09:46:36 pmThis is the listing of a tblookupcombobox. I am testing it now. ... You can see the two items that becomes multiline? This is not what it should show. It should show in a single line. I cannot understand why these two are multiline either the data in the database on these two items have no extra special chaacter either, only the text showing. That's really weird. Multi-line shouldn't be possible. Are you sure there are no records between Isle of man and Israel? You also see that it's not the length because Isle of man had fewer characters than some of the others that are single line. Could you show the same table/sql in a sqlmanager to see if you spot something weird? In the database itself, do those records have extra spaces after it? ---------------------------------------------------------------------- Fantablup · 2024-03-26 21:45 Of course, and thanks for the tip Actually i can see that there are a break line here in the database, as you can see. When i trim this before inserting it in a standard tcombobox, it is working good. But this is impossible to do for the tdblookupcombobox. I feel this is a bug in the component indeed. Trimming should also trim breaks actually, when there are only spaces between the breaks. It is only in Lazarus i experienced this issue actually. In javascript, golang and others, this is done automatically, and works good. Of course i can remove that break line in the database, but that is not the correct solution. The solution is that the component should do it. ---------------------------------------------------------------------- rvk · 2024-03-26 22:02 Quote from: Fantablup on March 26, 2024, 10:45:30 pmActually i can see that there are a break line here in the database, as you can see. When i trim this before inserting it in a standard tcombobox, it is working good. But this is impossible to do for the tdblookupcombobox.The break shouldn't even be in the database. You can have lots of other problems with that too. How did it end up in the database? How would you edit this field (including line break) in a TDBEdit? No, line breaks have no place in a string type. Quote from: Fantablup on March 26, 2024, 10:45:30 pmI feel this is a bug in the component indeed. Trimming should also trim breaks actually, when there are only spaces between the breaks.That might be a matter of opinion (although I'm not sure). What is someone really wants two lines for a record? Intentionally. For example firstname linebreak lastname (because the names could be large). Quote from: Fantablup on March 26, 2024, 10:45:30 pmOf course i can remove that break line in the database, but that is not the correct solution. The solution is that the component should do it. If you don't want to fix the database, you could fix the sql and strip out the #13 ---------------------------------------------------------------------- Fantablup · 2024-03-26 22:21 This is the result when trimming and adding to the tcombobox. This is how it should be. In ny other language that i know it is very easy to trim before adding anything to a list. Don't know about Delphi now, since i use Lazarus only now If only it is possible to trim in dblookupcombobox, it would be solved, but it is not possible. I feel this is a missing thing in the component, and therefore it is useless, anyway for me. I see that as a bug. I see your point about break lines, but if you don't have the option to trim it, than it is faulty. Because it doesn't mean you want the returns. No, i will not change the SQL database data, or the SQL for this. I use the same SQL in all of my systems in other languages, and no need to break from this. This is retrieved by data from someone else that gives it away, and there are hundred of thousand records in it. I had never had any problems with this data. I have used it since 2002 in different languages ---------------------------------------------------------------------- rvk · 2024-03-26 22:32 Quote from: Fantablup on March 26, 2024, 11:21:55 pmIn ny other language that i know it is very easy to trim before adding anything to a list. Don't know about Delphi now, since i use Lazarus only now And that's probably not a TDBLookupCombobox. It takes data directly from a datasource without editing. If you want to have it display something different then you need to do that in the SQL statement. That's just how it works. And this wasn't just about trimming. Because the trim() function also doesn't strip line breaks. Quote from: Fantablup on March 26, 2024, 11:21:55 pmIf only it is possible to trim in dblookupcombobox, it would be solved, but it is not possible. I feel this is a missing thing in the component, and therefore it is useless, anyway for me. I see that as a bug.Yes, it's only useless to you. Because you have line breaks in a place where they shouldn't be. But if your consider it a bug (I don't), feel free to report it in the bugtracker. Quote from: Fantablup on March 26, 2024, 11:21:55 pmI see your point about break lines, but if you don't have the option to trim it, than it is faulty. Because it doesn't mean you want the returns. You might not want the line breaks. Others might. And again... Trim isn't the same as removing line breaks. So if you ask for a trim function in that component, you might end up disappointed if it's implemented as trim() because that doesn't remove line breaks. So you should be clear that you would want the line breaks to be removed (and you normally do that in the SQL statement if you really want to keep it in the field). ---------------------------------------------------------------------- rvk · 2024-03-26 22:36 BTW. There is another solution. If you really don't want to fix the database... You can create a OnGetText function for that field where you strip out anything you don't like. You can still use the TDBLookupcombobox but it will take the edited value after the OnGetText function. https://www.freepascal.org/docs-html/fcl/db/tfield.ongettext.html ---------------------------------------------------------------------- Fantablup · 2024-03-26 22:44 Quote from: rvk on March 26, 2024, 11:32:14 pmAnd that's probably not a TDBLookupCombobox. It takes data directly from a datasource without editing. If you want to have it display something different then you need to do that in the SQL statement. That's just how it works. And this wasn't just about trimming. Because the trim() function also doesn't strip line breaks. No, it is not a TDBLookupCombobox. And the trim function is actually removing the break lines as you see here in the image. Like this: LItems[LItemIdx].Text := SQLCountries.FieldByName('countries_name').AsString;   ComboBox1.Items.Clear;   ComboBox1.Items.Add('<< All counties >>');   ComboBox1.Items.Add(trim(LItems[LItemIdx].Text)); Quote from: rvk on March 26, 2024, 11:32:14 pmYes, it's only useless to you. Because you have line breaks in a place where they shouldn't be. It is useless to me, yes. And all others that also have this issue. Quote from: rvk on March 26, 2024, 11:32:14 pmBut if your consider it a bug (I don't), feel free to report it in the bugtracker. Thanks for the tip. i will see. You might not want the line breaks. Others might. And again... Trim isn't the same as removing line breaks. So if you ask for a trim function in that component, you might end up disappointed if it's implemented as trim() because that doesn't remove line breaks. So you should be clear that you would want the line breaks to be removed (and you normally do that in the SQL statement if you really want to keep it in the field). [/quote] That's wrong. It removes the breaks indeed. ---------------------------------------------------------------------- rvk · 2024-03-26 22:47 O sorry. I'm indeed wrong about the trim() https://www.freepascal.org/docs-html/rtl/sysutils/trim.html It does remove line break (all chars < #33) But you can look at the suggestion in my last post about OnGetText for a possible solution and keep TDBLookupcombobox. ---------------------------------------------------------------------- Fantablup · 2024-03-26 22:47 Thanks for the tip rvk But now it is all working with only the standard tcombobox. I replaced it while talking with you. All solutions for this was too much, and it is much easier to just use a tcombobox. ---------------------------------------------------------------------- Fantablup · 2024-03-26 22:49 The onGetText is also too much. If you don't know about this issue, it can take days to just figuring this out for others. ---------------------------------------------------------------------- rvk · 2024-03-26 22:55 Quote from: Fantablup on March 26, 2024, 11:47:59 pmAll solutions for this was too much, and it is much easier to just use a tcombobox. Ok. For you it works. But normally TDBLookupcombobox uses a keyfield (like ID) to connect to another table/foreign keyfield. That won't work with TCombobox. That just returns a string. So after choosing one, you would again need to lookup the ID for that string (which now has changed from the actual value in the database !) to fill in another table. TDBLookupcombobox does that for you. At least, that's what TDBLookupcombobox is meant for. In bounded form that is. https://wiki.freepascal.org/TDBLookupComboBox But if it works for you, then that's great 👍 You probably just wanted to use it unbounded. Of you want to use it bounded in combination with TCombobox you'll have to program a whole lot more (saving the ID of all those entries for lookup when a value is chosen). ---------------------------------------------------------------------- Fantablup · 2024-03-26 23:00 Actually, i use an array of records that holds all the records as you see in my other reply. And when changing to another item in the combobox, it retrievs the itemindex that correspond to the array index. Very easy to do. I use the same code for all three combo boxes, so it is not much extra, and you have full control. only 25 extra lines of code. But, you should get a price for helping me on the issue. I really appreciate it ---------------------------------------------------------------------- rvk · 2024-03-26 23:16 Quote from: Fantablup on March 27, 2024, 12:00:03 amActually, i use an array of records that holds all the records as you see in my other reply. And when changing to another item in the combobox, it retrievs the itemindex that correspond to the array index. Very easy to do. I use the same code for all three combo boxes, so it is not much extra, and you have full control. only 25 extra lines of code.True... And it could be even less I've used the TString.Objects in the past for identifiers in a TStringList. (And TComboBox.Items is a TStringlist) https://stackoverflow.com/a/7237738/1037511 (For Delphi but for FPC it's the same) So just do 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 Combobox1.Items.AddObject('A string', TObject(123));//...ID := integer(ComboBox1.Items.Objects[ComboBox1.ItemIndex]) Happy coding ---------------------------------------------------------------------- Fantablup · 2024-03-26 23:21 Nice rvk  I know about it. you could also use pointers. Even less code. But now it is working good here. I don't want to re-do it just because of little less code You are great for helping though. hehe ) ---------------------------------------------------------------------- Fantablup · 2024-03-27 16:18 Just wanted to show the final result image, so you can see what it is used for, and why this was a issue. Attatched the image...