Recent

Author Topic: [ANSWERED] TSqlite3Dataset Insert problem  (Read 2059 times)

totya

  • Hero Member
  • *****
  • Posts: 640
[ANSWERED] TSqlite3Dataset Insert problem
« on: April 26, 2015, 02:10:22 am »
Hi!

I'm beginner in SQL.

See this sample:

Code: [Select]
unit Unit1;

{$mode objfpc}{$H+}

interface

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

type

  { TForm1 }

  TForm1 = class(TForm)
    Insert_Button: TButton;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Sqlite3Dataset1: TSqlite3Dataset;
    procedure Insert_ButtonClick(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
  Inherited;

  with Sqlite3Dataset1 do
    begin
      if not(TableExists) then
      begin
        FieldDefs.Clear;
        FieldDefs.Add('id', ftAutoInc);
        FieldDefs.Add('text', ftString);
        CreateTable;
      end;
    Open;
    end;
end;

procedure TForm1.Insert_ButtonClick(Sender: TObject);
begin
  with Sqlite3Dataset1 do
    if TableExists then
      begin
        Open;

        Insert;
          FieldByName('text').AsString := 'test';
        Post;

        ApplyUpdates;
      end;
end;

end.

If I use the INSERT button, then new record inserted to the correct position, I see this in the DBGrid1.
But if I reopen this database, inserted field positions are last, these field are in the last position.

For me now very need the correct order, how can I get records in the inserted order? What is the simplest way?
I don't know why "insert" this method, if the result is "append"...

Thanks!
« Last Edit: April 26, 2015, 01:03:20 pm by totya »

mangakissa

  • Hero Member
  • *****
  • Posts: 1099
Re: TSqlite3Dataset Insert problem
« Reply #1 on: April 26, 2015, 10:11:48 am »
Insert and append are doing the same. When a record is posted, it's aways placed at the and of the file or on a empty place where a record is deleted. Inserts is only creating space on the dbgrid where you're selected record is set. If you're  doing a insert/post/refresh you wil see your record is placed at the and of your dbgrid (refresh = reloading the query = close/open).
Also your order of your records is het by ORDER BY or by indexfieldnames property.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

totya

  • Hero Member
  • *****
  • Posts: 640
Re: TSqlite3Dataset Insert problem
« Reply #2 on: April 26, 2015, 10:24:15 am »
Insert and append are doing the same. When a record is posted, it's aways placed at the and of the file or on a empty place where a record is deleted. Inserts is only creating space on the dbgrid where you're selected record is set. If you're  doing a insert/post/refresh you wil see your record is placed at the and of your dbgrid (refresh = reloading the query = close/open).
Also your order of your records is het by ORDER BY or by indexfieldnames property.

Hi!

Thanks for this information!

But INSERT is good idea, because this insert new record to the appropriate position (in DBGrid).
Okay, I create new field, for example id2, then when save:

Code: [Select]
        i:=0;
        xDBGrid.BeginUpdate;
          while not eof do
          begin
            Edit;
              FieldByName('id2').AsInteger:=i;
            Post;

            inc(i);
            Next;
          end;
        xDBGrid.EndUpdate(true);


...and when open database:

Code: [Select]
SQL:='SELECT * FROM '+TableName+' ORDER BY '+'id2'+' ASC;';

Thanks!

mangakissa

  • Hero Member
  • *****
  • Posts: 1099
Re: [ANSWERED] TSqlite3Dataset Insert problem
« Reply #3 on: April 28, 2015, 08:54:37 am »
Yes, but DBGrid is only a visualisor of your dataset.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

 

TinyPortal © 2005-2018