Cannot edit data using DBGrid
lazarus / 16-Databases

----------------------------------------------------------------------
hijack · 2013-12-04 08:04

Hello,
I cannot update SQLite database using DBGrid.
Data in DBGrid are displayed, but when I change some cells and then
click the Button1, no changes are saved.
I think I have all components linked properly.
I also followed this tutorial, but still don't work :
http://wiki.freepascal.org/SQLdb_Tutorial2

Here is the source code:

Code: [Select]unit unit2;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, sqlite3conn, sqldb, db, FileUtil, Forms,
Controls,
  Graphics, Dialogs, DBGrids, StdCtrls;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    Datasource1: TDatasource;
    DBGrid1: TDBGrid;
    SQLite3Connection1: TSQLite3Connection;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);

  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }


procedure TForm1.FormCreate(Sender: TObject);
begin
      SQLQuery1.SQL.text:='Select * FROM zbozi WHERE id<2100 ORDER BY
nazev';
  SQLQuery1.Close;
  SQLQuery1.Open;

end;

procedure TForm1.Button1Click(Sender: TObject);
begin
   SQLQuery1.Edit;

  SQLQuery1.Upda]"]>Blockedde:=UpWhereChanged;
  SQLQuery1.ApplyUpdates;
  SQLTransaction1.Commit;
  SQLQuery1.Close;
  SQLQuery1.Open;




end;

end.

Here is also lfm file:
Code: [Select]object Form1: TForm1
  Left = 452
  Height = 698
  Top = 169
  Width = 1274
  Caption = 'Form1'
  ClientHeight = 698
  ClientWidth = 1274
  OnCreate = FormCreate
  LCLVersion = '1.0.14.0'
  object DBGrid1: TDBGrid
    Left = 30
    Height = 307
    Top = 316
    Width = 1217
    AutoFillColumns = True
    Color = clWindow
    Columns = <>
    DataSource = Datasource1
    TabOrder = 0
  end
  object Button1: TButton
    Left = 1027
    Height = 34
    Top = 268
    Width = 151
    Caption = 'Button1'
    OnClick = Button1Click
    TabOrder = 1
  end
  object SQLQuery1: TSQLQuery
    FieldDefs = <>
    Database = SQLite3Connection1
    Transaction = SQLTransaction1
    SQL.Strings = (
      ''
    )
    Params = <>
    Upda]"]>Blockedde = upWhereChanged
    left = 312
    top = 238
  end
  object SQLite3Connection1: TSQLite3Connection
    Connected = True
    LoginPrompt = False
    DatabaseName = 'database.s3db'
    KeepConnection = False
    Transaction = SQLTransaction1
    CharSet = 'UTF-8'
    LogEvents = [detCommit]
    Options = []
    left = 392
    top = 240
  end
  object SQLTransaction1: TSQLTransaction
    Active = True
    Action = caNone
    Database = SQLite3Connection1
    left = 248
    top = 238
  end
  object Datasource1: TDatasource
    DataSet = SQLQuery1
    left = 176
    top = 240
  end
end

----------------------------------------------------------------------
mangakissa · 2013-12-04 10:05

First You edit the dataset and then apply your dataset. But first you
have to post your record.
These are the steps:
- edit or dataset or change your record in DBGrid (if autoedit is on).
- Call dataset.post
- Call applyupdate
- Call Commit or commitretaining
- Open dataset if commit is called

Using commit closes your table already.

----------------------------------------------------------------------
hijack · 2013-12-04 11:18

Thanks a lot for your help.

The line that was missing in my code - in procedure Button1Click -
was:
Code: [Select]SQLQuery1.Post;
Now the updates are saved.

----------------------------------------------------------------------
BigChimp · 2013-12-05 07:32

Very strange. Just tested the code here again
http://wiki.freepascal.org/SQLdb_Tutorial2#Summary
with Firebird embedded, Laz 1.0.12, FPC 2.6.2 X86, Windows.

Editing data in the grid, pressing the filter button/button1 shows the
edits are there. Reopening the application: the edits are still there.

I would agree that if you edit the dataset directly, you'd need to
.post, but won't the grid/datasource deal with .edit/.post etc itself?

----------------------------------------------------------------------
mangakissa · 2013-12-05 08:10

Code: [Select]I would agree that if you edit the dataset directly,
you'd need to .post, but won't the grid/datasource deal with
.edit/.post etc itself?Depends whitch component you're using. The
TNavigator and a scroll in the DBGrid might be, but using a button
doesn't know if the dataset is in editmode and should post the data
into the dataset.

----------------------------------------------------------------------
BigChimp · 2013-12-05 08:24

Yes, but the button in the tutorial shows filters/does a requery. It
doesn't put the dataset in editmode.
Once again, AFAIU, the grid should handle this itself.

Anyway, was just setting up the tutorial with SQLite3 and the attached
worked for me without modification (other than changing the
TIBConnection for the SQLite equivalent)...
(Note: you'll need an sqlite3.dll there or in your
PATH/Windows/system32 directory)