DBGrid DBLookupComboBox editor lazarus / 16-Databases ---------------------------------------------------------------------- goodname · 2012-10-05 21:57 Hi Trying to use a DBLookupComboBox in a DBGrid. The custom editor works using the OnSelectEditor event below except that that the DBGrid displays the key data instead of the lookup data. Code: [Select]DBGrid1: TDBGrid; DBLCBox: TDBLookupComboBox; ... procedure TForm1.DBGrid1SelectEditor(Sender: TObject; Column: TColumn;  var Editor: TWinControl); begin   if Column.Field = DBLCBox.Field then begin    DBLCBox.BoundsRect:=DBGrid1.SelectedFieldRect;    Editor:=DBLCBox;   end; end; Setting the DBGrid to display lookup data means the "if" in the OnSelectEditor is always false as the TFields no longer match. Sure that there is something simple that I'm missing. Is there a way to either change the DBGrid or OnSelectEditor event to display only lookup data to the user yet use key data for posting data? Thanks in advance for any responses. Edit: The following thread mention this issue. Could not follow the solution mentioned there. http://www.lazarus.freepascal.org/index.php/topic,18163.msg102363.html #msg102363 ---------------------------------------------------------------------- taazz · 2012-10-05 22:56 First please provide your version of lazarus and fpc. As for your problem there 2 solutions that I have used heavily in the past both work great. 1) This one requires that you use persistent fields on your dataset. If you add all the fields at design time forcing your dataset to only handle the fields you have added you can add extra fields that do not take data from the DB back end what ever that might be. Those fields are usually calculated and lookup fields. Adding a lookup field will ask you to select the keyfield which is the field that holds the key value in the dataset you are editing, the lookup dataset the lookupkeyfield and the Result Fields. the main you need to remember here that the result field is what you want the end user to see in the grid the key fields and lookup keys are fields that create the link between the two datasets. There is a bug in lazarus 1.0 release which it comes from the fpc 2.6.0 distribution that you need to keep an eye on, if the lookup key field and the keyfield do not have the same name you will get an exception that says that the lookupfieldkey was not found add a new calculated field on the main dataset and give the same name as the lookupkeyfield in order to bypass the problem. 2) this solution is a based on the server you are using instead of using select * from mytable you issue an sql in the form of 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 MyTable.*, MyLookupTable.ResultFieldName FROM MyTableINNER JOIN MyLookupTable ON MyTable.KeyFieldName = MyLookupTable.LookupKeyFieldName  this will retrieve the description field inside you main dataset and you can show it on screen instead of the keyfield. This creates a number of problems with thegrid though you see after you have edited the keyfield that field will still have the old value you need to applyupdates and reQuery the server for the new value to show correctly. You can work around this on the client by updating both the key field and the description field on the validate event of the key field making sure that the decsription field is not part of the insert and update sql that will be issued to the server when you call applyupdates. Regards Jo ---------------------------------------------------------------------- goodname · 2012-10-06 01:18 Thank you Taazz I'll try your solutions tomorrow. Didn't mention the version earlier as I didn't think it would make a difference in this case. Could be wrong on that though. Using fpc 2.6.0, lazarus 1.0.1, and win32. ---------------------------------------------------------------------- goodname · 2012-10-09 14:11 Have found a third solution and want to hear opinions. I have tested the proposed solution and it works. It requires a change to the TColumn class in DBGrids. The query sent to the database contains both the lookup and the key data as in taazz's solution 2. Two properties are added to TColumn called keyFieldName and keyField. The properties are used as shown below. Code: [Select]procedure TForm1.FormActivate(Sender: TObject); begin   DBGrid1.Columns[0].keyFieldName:='key';   DBGrid1.Columns[0].FieldName:='lookup'; end; procedure Tform1.DBGrid1SelectEditor(Sender: TObject; Column: TColumn;   var Editor: TWinControl); begin   if Column.keyField=DBLCBox.Field   then begin    DBLCBox.BoundsRect:=DBGrid1.SelectedFieldRect;    Editor:=DBLCBox;   end; end; Was not able to get the keyFieldName to show up in the ObjectInspector. Basically a single column can be linked  with a lookup field for display and a id field. If others like the approach I can post the patch to TColumn which I'm sure can be improved. Edit: Alternative is to create properties called LookupField and LookupFieldName which would mean the Field and FieldName would hold the key. ---------------------------------------------------------------------- mizar · 2012-11-23 09:28 I'm using codetyphon 3.0 (lazarus 1.1 svn 39290 fpc 2.7.1) and the bug related to the names of the fields is still there  And the workaround suggested by taaz doesn't work either. At design time in DBGrid I can see the lookup field and I can read the right value inside, but it is not shaped as a combo but as a normal cell. At runtime I get the exception "field not found". Db middleware: zeos 7.0.0dev Any help appreciated ---------------------------------------------------------------------- goodname · 2012-11-23 14:56 Was not describing a bug when I asked this question. Make sure that your DBLookupComboBox is behaving properly when separate from the DBGrid. As for the "field not found" error make sure that field names used match those in your SQL select query. The SelectEditor event only works when editing a cell. When not in edit mode the cell will look like any other cell in the grid. ---------------------------------------------------------------------- mizar · 2012-11-23 16:03 Quote from: goodname on November 23, 2012, 03:56:35 pmWas not describing a bug when I asked this question. I was referring to taaz's post Quote from: goodname on November 23, 2012, 03:56:35 pmMake sure that your DBLookupComboBox is behaving properly when separate from the DBGrid. It seems Quote from: goodname on November 23, 2012, 03:56:35 pmAs for the "field not found" error make sure that field names used match those in your SQL select query. If it were wrong it shouldn't show the correct data at design time. Quote from: goodname on November 23, 2012, 03:56:35 pmThe SelectEditor event only works when editing a cell. When not in edit mode the cell will look like any other cell in the grid. ok! ---------------------------------------------------------------------- taazz · 2012-11-29 10:58 @mizar As I do not use fpc 2.7 at all, I have no idea what is going on and what is the problem you are facing. I have tested fpc2.6.1 and if I remember correctly the bug was corrected so I expect it to be corrected in 2.7.X as well but I try to use only released versions and fpc2.7.X is not going to be released any time soon. @goodname Extending the dbcolumn to handle data specific tasks circumvents a number of OOP rules I wouldn't expect it to get accept in lcl/fpc, especially when you have the lookup field class already that was designed for that functionality in the first place. It is far better to extend that class instead with what ever you need it to support. ---------------------------------------------------------------------- mizar · 2012-11-30 21:51 I'm using now lazarus svn 39418 + fpc 2.6.1 + zeos 7.0.1-beta but till now no luck  I can't figure out what I'm doing wrong, but at design time I can see the lookup field in the grid while at runtime it raise an exception "field not found"  al line 1771 ( BindField(true) ) of ZAbstractRODataset TZAbstractRODataset.GetRecord