DBLookUpCombo in a DBGrid - Help, Almost there... lazarus / 16-Databases ---------------------------------------------------------------------- DeBritto · 2016-02-25 18:12 Hi folks, I'm struggling with an issue for some time now and I give up... I'm trying to build a DBGrid with a DBLookupCombobox attached. The DBLookup is working fine, but when I post the updates I receive this error: 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 Am error occured while applying the updates in a record:IBConnection_ConnSmCube: PrepareStatement :- Dynamic SQL Error- SQL error code = -206- Column unknown- Modality- At line 1, column 21  I have tried to update the record using 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 DataModule_ SmCube.SQLQuery_Institutions.SQL.Clear;DataModule_SmCube.SQLQuery_Inst itutions.SQL.Add('UPDATE institutionsSET institution = :institution, institution_modalityID =:institution_modalityID WHERE institutionID = :institutionID');  DataModule_SmCube.SQLQuery_Institutions.Params.Para mByName('institution').AsString:= Institutions.DBGrid_Institutions.Dat aSource.DataSet.Fields[1].AsString;DataModule_SmCube.SQLQuery_Institut ions.Params.ParamByName('institution_modalityID').AsInteger:= Institut ions.DBGrid_Institutions.DataSource.DataSet.Fields[2].AsInteger;DataMo dule_SmCube.SQLQuery_Institutions.Params.ParamByName('institutionID'). AsInteger:= FInstitutions.DBGrid_Institutions.DataSource.DataSet.Field s[0].AsInteger;DataModule_SmCube.SQLQuery_Institutions.ExecSQL;  But doesn't work. I really not figure out what to do, can you help me please... This is the routine: 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   DataModule_SmCube.SQLQuery_Institutions.SQL.Clear;  DataModule_SmCube.SQLQuery_Institutions.SQL.Add('SELECT institutions.institutionid, institutions.institution, institutions.institution_modalityid, institutions.description, ');  DataModule_SmCube.SQLQuery_Institutions.SQL.Add('(SELECT modalities.modality FROM modalities WHERE modalities.modalityid = institutions.institution_modalityid) ');  DataModule_SmCube.SQLQuery_Institutions.SQL.Add(' FROM institutions ');  DataModule_SmCube.SQLQuery_Institutions.Active:= True;  DataModule_SmCube.SQLQuery_Institutions.ExecSQL;   //---------- Prepare Lookup Combo  DataModule_SmCube.SQLQuery_Modalities.Close;  DataModule_SmCube.SQLQuery_Modalities.SQL.Clear;  DataModule_SmCube.SQLQuery_Modalities.SQL.Add('select * from modalities order by modalityNumber');  DataModule_SmCube.SQLQuery_Modalities.Active:= True;  DataModule_SmCube.SQLQuery_Modalities.ExecSQL;   DbLookupComboBox1.DataSource:= DataModule_SmCube.DataSource_Institutions;    DbLookupComboBox1.DataField:='INSTITUTION_MODALITYID';   DbLookupComboBox1.ListSource:= DataModule_SmCube.DataSource_Modalities;   DbLookupComboBox1.KeyField:='MODALITYID';   DbLookupComboBox1.ListField:='MODALITY';    DbLookupComboBox1.LookupCache:=True;   DataModule_SmCube.SQLQuery_Institutions.First;  while not DataModule_SmCube.SQLQuery_Institutions.EOF do  begin    // Setup DbGrid Columns    DBGrid_Institutions.Columns.Items[0].Title.Caption:= 'Social Institution';    DBGrid_Institutions.Columns.Items[0].FieldName:= 'INSTITUTION';     DBGrid_Institutions.Columns.Items[1].Title.Caption:= 'Modality';    DBGrid_Institutions.Columns.Items[1].FieldName:= 'MODALITY';     DBGrid_Institutions.Columns.Items[2].FieldName:= 'INSTITUTION_MODALITYID';    DBGrid_Institutions.Columns.Items[2].Visible:= False;    DBGrid_Institutions.Columns.Items[3].Visible:= False;    DBGrid_Institutions.Columns.Items[4].Visible:= False;     DataModule_SmCube.SQLQuery_Institutions.Next;  end;     Here is the Datamodule AfterPost code:   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 procedure TDataModule_SmCube.SQLQuery_InstitutionsAfterPost(DataSet: TDataSet);begin    DataModule_SmCube.SQLQuery_Institutions.ApplyUpdates;  DataModule_SmCube.SQLTransaction_ConnSmCube.CommitRetaining;  DataModule_SmCube.SQLQuery_Institutions.Refresh;end;   Best regards, Christian ---------------------------------------------------------------------- Jurassic Pork · 2016-02-25 22:28 hello, 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 Am error occured while applying the updates in a record:IBConnection_ConnSmCube: PrepareStatement :- Dynamic SQL Error- SQL error code = -206- Column unknown- Modality- At line 1, column 21  You have a problem with the Field Modality, check the case of this field in your sql queries. Show us the definition of your tables (create table). May be a problem in this line : 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 DbLookupComboBox1.ListField:='MODALITY'; Friendly, J.P ---------------------------------------------------------------------- mangakissa · 2016-02-26 08:47 Order your queries . Just use post, appyupdate and commit(retaining) to update a record.Use DataModule_SmCube.SQLQuery_Institutions.UpdateSQL if you want to update specific fieldsCode: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i DataModule_SmCube.SQLQuery_Institutions.UpdateSQL.Add('UPDATE institutionsSET institution = :institution, institution_modalityID =:institution_modalityID WHERE institutionID = :institutionID'); active = true, so  ExecSQL is useless. Your select statement is not correct. It can't find field 'modality', because it does not exists. this is the right SELECT: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 with DataModule_SmCube.SQLQuery_Institutions.SQL dobegin  Add('SELECT institutions.institutionid, institutions.institution, ');  Add('       institutions.institution_modalityid, institutions.description,');  Add('  (SELECT modalities.modality FROM modalities ');  Add('   WHERE modalities.modalityid = institutions.institution_modalityid) as modality');  Add('FROM institutions'); An updateSQL is in this case very important, otherwise your query is readonly.field modality can't be save, because it's not in the primary table or in the updateSQL query. ---------------------------------------------------------------------- DeBritto · 2016-02-27 02:06 Thank you so much Mangakissa, I followed all your instructions and everything is working like a charm now! The only thing is that the select that you proposed doesn't work, I'm using the old one and its working. The key to solve the problem was to use Update SQL instruction in the correct way. Thank you so much, I was struggling with this for many many days... ---------------------------------------------------------------------- DeBritto · 2016-02-27 02:14 Thanks too Jurassik Pork! Best regards