[ANSWERED] TSqlite3Dataset Insert problem
lazarus / 16-Databases

----------------------------------------------------------------------
totya · 2015-04-26 00:10

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!

----------------------------------------------------------------------
mangakissa · 2015-04-26 08:11

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.

----------------------------------------------------------------------
totya · 2015-04-26 08:24

Quote from: mangakissa on April 26, 2015, 10:11:48 amInsert 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 · 2015-04-28 06:54

Yes, but DBGrid is only a visualisor of your dataset.