<SOLVED> DBLookUpCombo in a DBGrid - Help, Almost there...

[DeBritto]

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][+][-]

1. Am error occured while applying the updates in a record:
2. IBConnection_ConnSmCube: PrepareStatement :
3. - Dynamic SQL Error
4. - SQL error code = -206
5. - Column unknown
6. - Modality
7. - At line 1, column 21
8.  
9.  

I have tried to update the record using this:

Code: Pascal  [Select][+][-]

1. DataModule_SmCube.SQLQuery_Institutions.SQL.Clear;
2. DataModule_SmCube.SQLQuery_Institutions.SQL.Add('UPDATE institutions
3. SET institution = :institution, institution_modalityID =
4. :institution_modalityID WHERE institutionID = :institutionID');
5.   DataModule_SmCube.SQLQuery_Institutions.Params.ParamByName('institution').AsString:= Institutions.DBGrid_Institutions.DataSource.DataSet.Fields[1].AsString;
6. DataModule_SmCube.SQLQuery_Institutions.Params.ParamByName('institution_modalityID').AsInteger:= Institutions.DBGrid_Institutions.DataSource.DataSet.Fields[2].AsInteger;
7. DataModule_SmCube.SQLQuery_Institutions.Params.ParamByName('institutionID').AsInteger:= FInstitutions.DBGrid_Institutions.DataSource.DataSet.Fields[0].AsInteger;
8. DataModule_SmCube.SQLQuery_Institutions.ExecSQL;
9.  

But doesn't work.
I really not figure out what to do, can you help me please...

This is the routine:

Code: Pascal  [Select][+][-]

1.   DataModule_SmCube.SQLQuery_Institutions.SQL.Clear;
2.   DataModule_SmCube.SQLQuery_Institutions.SQL.Add('SELECT institutions.institutionid, institutions.institution, institutions.institution_modalityid, institutions.description, ');
3.   DataModule_SmCube.SQLQuery_Institutions.SQL.Add('(SELECT modalities.modality FROM modalities WHERE modalities.modalityid = institutions.institution_modalityid) ');
4.   DataModule_SmCube.SQLQuery_Institutions.SQL.Add(' FROM institutions ');
5.   DataModule_SmCube.SQLQuery_Institutions.Active:= True;
6.   DataModule_SmCube.SQLQuery_Institutions.ExecSQL;
7.  
8.   //---------- Prepare Lookup Combo
9.   DataModule_SmCube.SQLQuery_Modalities.Close;
10.   DataModule_SmCube.SQLQuery_Modalities.SQL.Clear;
11.   DataModule_SmCube.SQLQuery_Modalities.SQL.Add('select * from modalities order by modalityNumber');
12.   DataModule_SmCube.SQLQuery_Modalities.Active:= True;
13.   DataModule_SmCube.SQLQuery_Modalities.ExecSQL;
14.  
15.   DbLookupComboBox1.DataSource:= DataModule_SmCube.DataSource_Institutions;  
16.   DbLookupComboBox1.DataField:='INSTITUTION_MODALITYID';
17.   DbLookupComboBox1.ListSource:= DataModule_SmCube.DataSource_Modalities;
18.   DbLookupComboBox1.KeyField:='MODALITYID';
19.   DbLookupComboBox1.ListField:='MODALITY';  
20.   DbLookupComboBox1.LookupCache:=True;
21.  
22.   DataModule_SmCube.SQLQuery_Institutions.First;
23.   while not DataModule_SmCube.SQLQuery_Institutions.EOF do
24.   begin
25.     // Setup DbGrid Columns
26.     DBGrid_Institutions.Columns.Items[0].Title.Caption:= 'Social Institution';
27.     DBGrid_Institutions.Columns.Items[0].FieldName:= 'INSTITUTION';
28.  
29.     DBGrid_Institutions.Columns.Items[1].Title.Caption:= 'Modality';
30.     DBGrid_Institutions.Columns.Items[1].FieldName:= 'MODALITY';
31.  
32.     DBGrid_Institutions.Columns.Items[2].FieldName:= 'INSTITUTION_MODALITYID';
33.     DBGrid_Institutions.Columns.Items[2].Visible:= False;
34.     DBGrid_Institutions.Columns.Items[3].Visible:= False;
35.     DBGrid_Institutions.Columns.Items[4].Visible:= False;
36.  
37.     DataModule_SmCube.SQLQuery_Institutions.Next;
38.   end;
39.  
40.  

 

Here is the Datamodule AfterPost code:
 

Code: Pascal  [Select][+][-]

1. procedure TDataModule_SmCube.SQLQuery_InstitutionsAfterPost(DataSet: TDataSet);
2. begin  
3.   DataModule_SmCube.SQLQuery_Institutions.ApplyUpdates;
4.   DataModule_SmCube.SQLTransaction_ConnSmCube.CommitRetaining;
5.   DataModule_SmCube.SQLQuery_Institutions.Refresh;
6. end;
7.  
8.  

Best regards,
Christian

[Jurassic Pork]

hello,

Code: Pascal  [Select][+][-]

1. Am error occured while applying the updates in a record:
2. IBConnection_ConnSmCube: PrepareStatement :
3. - Dynamic SQL Error
4. - SQL error code = -206
5. - Column unknown
6. - Modality
7. - At line 1, column 21
8.  

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][+][-]

1. DbLookupComboBox1.ListField:='MODALITY';  

Friendly, J.P

[mangakissa]

• 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 fields

Code: Pascal  [Select][+][-]

1. DataModule_SmCube.SQLQuery_Institutions.UpdateSQL.Add('UPDATE institutions
2. SET institution = :institution, institution_modalityID =
3. :institution_modalityID WHERE institutionID = :institutionID');
4.  

• 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][+][-]

1. with DataModule_SmCube.SQLQuery_Institutions.SQL do
2. begin
3.   Add('SELECT institutions.institutionid, institutions.institution, ');
4.   Add('       institutions.institution_modalityid, institutions.description,');
5.   Add('  (SELECT modalities.modality FROM modalities ');
6.   Add('   WHERE modalities.modalityid = institutions.institution_modalityid) as modality');
7.   Add('FROM institutions');
8.  

• 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]

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]

Thanks too Jurassik Pork!
Best regards