Search code examples
lazarus

Lazarus DBLookupCombobox can not convert null to int64 for data submission

I have been using Lazarus 2.x with Firebird 3 (via flamerobin) and i try to commit records via TSQLConnection, TSQLQuery, TDataSource in a data module.

I run the following script successfully in order to configure initially DBLookupCombobox, where the records are displayed without any problem.

procedure TForm3.FormCreate(Sender: TObject);
  begin
  appeals.SQLQuery4.Close;
  appeals.SQLQuery4.SQL.Text:='select id,fullname from promoter';

  appeals.SQLQuery4.Open;

  appeals.DataSource2.DataSet:=appeals.SQLQuery4;
  DBLookupComboBox1.ListSource:=appeals.DataSource2;
  DBLookupComboBox1.ScrollListDataset:=True;
  DBLookupComboBox1.Style:=csDropDownList;
  DBLookupComboBox1.KeyField:='id';
  DBLookupComboBox1.DataField:='id';
  DBLookupComboBox1.ListField:='fullname';    

  end;

Afterwards i use the following snippet for record submission:

procedure TForm3.Button1Click(Sender: TObject);
begin

  appeals.SQLTransaction1.Active:=false;

  appeals.SQLQuery1.SQL.Text:='UPDATE appeals set name=:name,date_entry=:entry,date_suspended=:suspended,'+
  'date_court=:court,date_judgement=:judgement,promoter_id=:code where id='+IntToStr(row_num);

  appeals.SQLQuery1.Params.ParamByName('name').AsString:=Trim(Edit1.Text);
  appeals.SQLQuery1.Params.ParamByName('entry').AsDate:=DateTimePicker1.Date;
  appeals.SQLQuery1.Params.ParamByName('suspended').AsDate:=IncDay(DateTimePicker1.Date,10);
  appeals.SQLQuery1.Params.ParamByName('court').AsDate:=DateTimePicker2.Date;
  appeals.SQLQuery1.Params.ParamByName('judgement').AsDate:=IncDay(DateTimePicker2.Date,20);
  appeals.SQLQuery1.Params.ParamByName('code').AsInteger:=DBLookupComboBox1.KeyValue;

  appeals.SQLTransaction1.StartTransaction;
  appeals.SQLQuery1.ExecSQL;
  appeals.SQLTransaction1.Commit;
  Application.MessageBox('Record submission with success !', 'Information', MB_ICONINFORMATION);                               

 end;

I have also attached the following script in Form Create event without any luck from wiki article. 

If (DBLookupComboBox1.KeyValue = Null) And (appeals.SQLQuery4.RecordCount > 0) Then
    DBLookupComboBox1.KeyValue := appeals.SQLQuery4.FieldByName('id').AsVariant;

Any idea would help me a lot regarding DBLookupComboBox1.KeyValue where the following error appears!

enter image description here

I would like to update the fields of a table via DBLookupCombobox (foreign key) that loads data from another table, two datetimepickers and edit control.

enter image description here

Regards

Solution

  • I just found that before appeals.SQLTransaction1.Active:=false, i have to store DBLookupComboBox1.KeyValue into a local variable as database connection afterwards is lost and thus there is not any value to DBLookupComboBox1.KeyValue but NULL.

    Therefore i have to use that local variable into my SQL query to update the appropriate record.

    I should be more careful next time!