Recent

Author Topic: Cannot edit data using DBGrid  (Read 9573 times)

hijack

  • Newbie
  • Posts: 2
Cannot edit data using DBGrid
« on: December 04, 2013, 09:04:10 am »
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.UpdateMode:=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 = <>
    UpdateMode = 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

« Last Edit: December 04, 2013, 09:50:04 am by hijack »

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: Cannot edit data using DBGrid
« Reply #1 on: December 04, 2013, 11:05:53 am »
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.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

hijack

  • Newbie
  • Posts: 2
Re: Cannot edit data using DBGrid
« Reply #2 on: December 04, 2013, 12:18:29 pm »
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

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Cannot edit data using DBGrid
« Reply #3 on: December 05, 2013, 08:32:12 am »
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?
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: Cannot edit data using DBGrid
« Reply #4 on: December 05, 2013, 09:10:27 am »
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.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Cannot edit data using DBGrid
« Reply #5 on: December 05, 2013, 09:24:35 am »
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)
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

 

TinyPortal © 2005-2018