Insert Into and Edit Database

[Weitentaaal]

Hey Guys,

just wanted to ask whats the best / correct way to do this

Code: Pascal  [Select][+][-]

1.                     dbGer.Insert;
2.                     dbGer.FieldByName('Zeichnung').AsInteger := ZeichNr;
3.                     dbGer.FieldByName('ZlAl').AsInteger := InZlAl;
4.                     dbGer.FieldByName('Komp').AsInteger := 1;
5.                     dbGer.FieldByName('Art').AsInteger := 0;
6.                     dbGer.FieldByName('X1').AsFloat := RsX;
7.                     dbGer.FieldByName('X2').AsFloat := ReX;
8.                     dbGer.FieldByName('Y1').AsFloat := RsY;
9.                     dbGer.FieldByName('Y2').AsFloat := ReY;
10.                     dbGer.FieldByName('Z1').AsFloat := RsZ;
11.                     dbGer.FieldByName('Z2').AsFloat := ReZ;
12.                     If InZlAl = 0 then Begin
13.                        dbGer.FieldByName('ZRahmenart').text := ZRahmenart;
14.                     End Else Begin
15.                        dbGer.FieldByName('ARahmenart').text := ARahmenart;
16.                     End;
17.                     dbGer.FieldByName('Para1').text := 0;
18.                     dbGer.ApplyUpdates;
19.  

DB : Sqlite

Edit: I Tested to Edit a Fiel in my db in a mini Project :

Code: Pascal  [Select][+][-]

1.   wgrExecute(dbT, 'SELECT * FROM GERAET WHERE Zeichnung = 1 AND Komp = 0 AND Art = 1');
2.   dbT.Edit;
3.   dbT.FieldByName('ARahmenart').AsInteger:= 2;
4.   dbT.Post;
5.   dbt.ApplyUpdates;
6.   showMessage('t3');
7.  

did't work what am i doing wrong ?
Getting this Error

[JanRoza]

Shouldn't there be a dbGer.Post statement before the ApplyUpdates?

[Weitentaaal]

i dont know if i need it .. just a Beginner asking how to do this in a better way

[JanRoza]

You need the post statementafter an edit or insert statement, take a look at https://wiki.freepascal.org/SqlDBHowto.

[Weitentaaal]

didn't work with the .Post :/

i set the query to edit --> then i edit the field in db --> after that i use Post and after the Post i apply the updates but i am making an mistake i don't know wich ... pls help

[Zvoni]

Transaction commit?

[Weitentaaal]

so i should just add an Commit after alll ? or how do u mean this ?

[egsuh]

Try these steps.


Transaction.Active := true;
// Then do what you have written
Transaction.Commit;

Transaction.Active := True;
// Open DBT                    ==> You should be able to see your changes here

[Weitentaaal]

Changed Code o this but didn't work:( :

Code: Pascal  [Select][+][-]

1.    initdb();
2.  
3. //initDb:
4.    sqlConnWGR := TSQLite3Connection.Create(Nil);
5.    sqlConnWGR.DatabaseName := SQLiteWGRPath;
6.    //sqlConnWSET.HostName := SQLiteDBHost;
7.    //sqlConnWSET.UserName := SQLiteDBUser;
8.    sqlConnWGR.Password := SQLiteWGRPass;
9.    sqlConnWGR.Connected := true;
10.    sqlConnWGR.KeepConnection := true;
11.  
12.    SQLTransWGR := TSQLTransaction.Create(sqlConnWGR);
13.    sqlConnWGR.Transaction := SQLTransWGR;
14. //End InitDb
15.    
16.    dbT := TSQLQuery.Create(nil);
17.   dbT.UsePrimaryKeyAsKey := False;
18.   dbT.DataBase := sqlConnWGR;
19.   dbT.PacketRecords := -1;
20.   SQLTransWGR.Active:= True;
21.   //dbT.SQL.Text := 'Update Geraet Set Zeichnung = '+Char(32)+'1'+Char(32)+' WHERE KOMP=0 AND ART=0';
22.   wgrExecute(dbT, 'SELECT * FROM GERAET WHERE Zeichnung = 1 AND Komp = 0 AND Art = 1');
23.   dbT.Edit;
24.   dbT.FieldByName('ARahmenart').AsInteger:= 2;
25.   dbT.Post;
26.   //dbt.ApplyUpdates;
27.   SQLTransWGR.Commit;
28.  
29.  

i Know how to add stuff and its working but i cant Edit a Already existing field

any Ideas ? Thanks btw for ur Help

[egsuh]

You need to run applyupdates.

"Post" changes the content of "opened dataset" ---  dbT here, but does not change the content of database in the HardDisk. ApplyUpdates does.  The procedures must be so.

But I'm not sure whether you can change the content of TSQLQuery. If you are using SQLite, then try TSQLite3DataSet. It's on Data Access Tab.

[Weitentaaal]

Pls Help i dont know what i am missing

Code: Pascal  [Select][+][-]

1. try
2.     sqlConnWGR := TSQLite3Connection.Create(Nil);
3.     sqlConnWGR.DatabaseName := SQLiteWGRPath;
4.     //sqlConnWSET.HostName := SQLiteDBHost;
5.     //sqlConnWSET.UserName := SQLiteDBUser;
6.     sqlConnWGR.Password := SQLiteWGRPass;
7.     sqlConnWGR.Connected := true;
8.     sqlConnWGR.KeepConnection := true;
9.  
10.     SQLTransWGR := TSQLTransaction.Create(sqlConnWGR);
11.     sqlConnWGR.Transaction := SQLTransWGR;
12.  
13.     dbT := TSQLQuery.Create(Nil);
14.     dbT.DataBase := SQLConnWGR;
15.     dbT.SQL.Text := 'SELECT * FROM GERAET WHERE ZEICHNUNG = 1 AND KOMP = 0 AND ART = 0;';
16.     dbT.Open;
17.     dbT.Edit;
18.  
19.     dbT.FieldByName('Zeichnung').AsInteger := 2;
20.  
21.     dbT.Post;
22.     dbT.Upda]"]>Blockedde := upWhereAll;
23.     //SQLTransWGR.Commit;
24.     dbT.ApplyUpdates;
25.  
26.     dbT.Close;
27.     dbT.Free;
28.   except
29.     on e:Exception do begin
30.       showmessage(e.Message);
31.     end;
32.   end;
33.  
34.   sqlConnWSET.Close();
35.   SQLTransWSET.CloseDataSets;
36.  

it tells my my db is locked and i cant update the table but i never used a second connection to the db:( is there any way its opened somewhere else ?

[Weitentaaal]

error Because i had the db browser open . Fixed but i get External SIGSEV when closing Connection :/

[egsuh]

The order should be as follows.

   SQLTransWGR.Active := True;
   dbT.Open;

   ......

   dbT.ApplyUpdates;
   SQLTransWGR.Commit;    // This closes all the linked datasets. 
 

[JanRoza]

Or use

Code: Pascal  [Select][+][-]

1. SQLTransWGR.CommitRetaining;    // Does not closes all the linked datasets.

If you want to keep the dataset open after the commit.