Can't save changes of Access-database. Using ODBC lazarus / 16-Databases ---------------------------------------------------------------------- Nickedom · 2018-09-14 08:15 Hello Community, I programmed a program which can read a MS Access-database. But the programm can’t save changes to the database-file. I have to work with this type of database. Operating system: Windows 10. Lazarus IDE v1.6.4. I did what this tutorial is saying: https://www.youtube.com/watch?v=LW3wuJAB2hk Because I don’t want expect you to watch the long video I write what I did here: 1) Install the following driver: Microsoft Access Database Engine 2010 Redistributable https://www.microsoft.com/en-my/download/details.aspx?id=13255 2) Configure the driver. See attachment-picture. 3)  Set into the form the following objects:     DataSource1: TDataSource;     DBGrid1: TDBGrid;     ODBCConnection1: TODBCConnection;     SQLQuery1: TSQLQuery;     SQLTransaction1: TSQLTransaction; 4) Initialize this objects with the following content: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.init_all;begin   init_DBGrid;   init_DataSource1;   init_ODBCConnection1;   init_SQLTransaction1;   init_SQLQuery1;end;     procedure TForm1.init_DBGrid;begin   DBGrid1.DataSource := DataSource1;end;  procedure TForm1.init_DataSource1;begin   DataSource1.DataSet :=SQLQuery1;end;  procedure TForm1.init_ODBCConnection1;begin   ODBCConnection1.DatabaseName := 'lib_for_GUI';   ODBCConnection1.UserName := '';   ODBCConnection1.Password := '';   ODBCConnection1.Transaction := SQLTransaction1;;   ODBCConnection1.Connected := true;end;  procedure TForm1.init_SQLTransaction1;begin   SQLTransaction1.DataBase := ODBCConnection1;   SQLTransaction1.Active := true;end;  procedure TForm1.init_SQLQuery1;begin   SQLQuery1.DataBase := ODBCConnection1;   SQLQuery1.Transaction := SQLTransaction1;   SQLQuery1.SQL.Text := 'select * from Altium';   SQLQuery1.UsePrimaryKeyAsKey := false;   SQLQuery1.Active := true;end;   After the procedure “Form1.init_all” worked, the content of the database is watched in the DBGrid1 correctly. I can select the table with other orders for particular columns. It function. But I can’t save changes to the database-file. In the following link is described how to save changes into the database: http://wiki.freepascal.org/SQLdb_Tutorial2 The homepage describes to use this following two lines:   SQLQuery1.ApplyUpdates();   SQLTransaction1.Commit; In the following procedure I manipulate a field and want save the change. But it doesn’t work. In the attachment you can see the error-message. A part of the error-message is in german. I translated this part into english. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.Button_Save_to_h00001Click(Sender: TObject);begin  SQLQuery1.Close;  SQLQuery1.SQL.Clear;  SQLQuery1.SQL.Add('UPDATE Altium SET h_MGR = ''huibuh'' WHERE PartNumer = ''h00001''');  ShowMessage('SQLQuery1.SQL.Text=' + sLineBreak + SQLQuery1.SQL.Text );   ODBCConnection1.connected := True;  SQLTransaction1.Active := True;  SQLQuery1.Open;   SQLQuery1.ApplyUpdates();  SQLTransaction1.Commit;end;  Please could help me somebody? I am trying to solve the problem for 3 days. PS.: I am wondering at the following: In the driver i set a username and a password. But in the Delphi-Code i left the username and password empty. And it works. If I write the correct username and password in the delphi-code, the problem is not solved. ---------------------------------------------------------------------- balazsszekely · 2018-09-14 08:28 Hi Nickedom, QuoteIn the following procedure I manipulate a field and want save the change. But it doesn’t work. In the attachment you can see the error-message. A part of the error-message is in german. I translated this part into english.1. When the query does not return anything(insert, update) use SQLQuery1.ExecSQL, otherwise use SQLQuery1.Open(select). 2. Always use parametrized queries when you wish to pass one or more parameter, like this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i    SQLQuery1.SQL.Text := 'update tablename set fieldname = :param';  SQLQuery1.Params.BeginUpdate;    SQLQuery1.ParamByName('param').AsInteger := 12;  SQLQuery1.Params.EndUpdate;  SQLQuery1.ExecSQL;  In your particular case, it should look like this(I assume the fields are string(varchar) type, if not just change the AsString part): Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   SQLQuery1.Close;  SQLQuery1.SQL.Clear;  SQLQuery1.SQL.Add('UPDATE Altium SET h_MGR = :param1 WHERE PartNumer = :param2');  SQLQuery1.Params.BeginUpdate;  SQLQuery1.ParamByName('param1').AsString := 'huibuh';  SQLQuery1.ParamByName('param2').AsString := 'h00001';  SQLQuery1.Params.EndUpdate;  ShowMessage('SQLQuery1.SQL.Text=' + sLineBreak + SQLQuery1.SQL.Text );   ODBCConnection1.connected := True;  SQLTransaction1.Active := True;  SQLQuery1.ExecSQL;   SQLQuery1.ApplyUpdates()  SQLTransaction1.Commit; ---------------------------------------------------------------------- Nickedom · 2018-09-14 09:08 Hi GetMem, thank you for the fast answer. I changed the code like you has written. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.Button_Save_to_h00001Click(Sender: TObject);begin  SQLQuery1.Close;  SQLQuery1.SQl.Clear;   SQLQuery1.SQL.Add('UPDATE Altium SET h_MGR = :param1 WHERE PartNumer = :param2');  SQLQuery1.Params.BeginUpdate;  SQLQuery1.ParamByName('param1').AsString := 'huibuh';  SQLQuery1.ParamByName('param2').AsString := 'h00001';  SQLQuery1.Params.EndUpdate;  ShowMessage('SQLQuery1.SQL.Text=' + sLineBreak + SQLQuery1.SQL.Text );   ODBCConnection1.connected := True;  SQLTransaction1.Active := True;  //SQLQuery1.Open;  SQLQuery1.ExecSQL;   SQLQuery1.ApplyUpdates();  SQLTransaction1.Commit;end; Now it comes an other error-message: plaase see attachment: The result of the procedure ShowMessage see in the2nd attachment: ---------------------------------------------------------------------- balazsszekely · 2018-09-14 09:34 I have no idea why is asking for 3 parameters, when there is only 2. Please try a more simple update like: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  SQLQuery1.SQL.Add('UPDATE yourtable set yourfield = 1'); SQLQuery1.ExecSQL;What happens in this case? What line gives you the error(if any)? PS: Please backup the database firts, just to be sure. ---------------------------------------------------------------------- Nickedom · 2018-09-14 10:48 Hi GetMem, I found a mistake from me: The "b" was missing in the word "PartNumber": I correccted the mistake, but now an other error occurs: please see attachment for new error-message. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   SQLQuery1.Close;  SQLQuery1.SQl.Clear;  SQLQuery1.SQL.Add('UPDATE Altium SET h_MGR = :param1 WHERE PartNumber = :param2'); // I fergot the b in PartNumber  SQLQuery1.Params.BeginUpdate;  SQLQuery1.ParamByName('param1').AsString := 'huibuh';  SQLQuery1.ParamByName('param2').AsString := 'h00001';  SQLQuery1.Params.EndUpdate;   ShowMessage('SQLQuery1.SQL.Text=' + sLineBreak + SQLQuery1.SQL.Text );   ODBCConnection1.connected := True;  SQLTransaction1.Active := True;  //SQLQuery1.Open;  SQLQuery1.ExecSQL;   SQLQuery1.ApplyUpdates();   //<-----Before I have included the "b" in the word PartNumber the error occurs here  SQLTransaction1.Commit;  Also I simplified the code, like you suggested: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   SQLQuery1.Close;  SQLQuery1.SQl.Clear;  SQLQuery1.SQL.Add('UPDATE Altium SET test = 1');   ShowMessage('SQLQuery1.SQL.Text=' + sLineBreak + SQLQuery1.SQL.Text );   ODBCConnection1.connected := True;  SQLTransaction1.Active := True;  //SQLQuery1.Open;  SQLQuery1.ExecSQL;   SQLQuery1.ApplyUpdates();   //<-----new error occurs here  SQLTransaction1.Commit; Here comes the same error-message. ---------------------------------------------------------------------- valdir.marcos · 2018-09-14 11:15 Quote from: Nickedom on September 14, 2018, 10:15:43 amI programmed a program which can read a MS Access-database. But the programm can’t save changes to the database-file. I have to work with this type of database. Operating system: Windows 10. Lazarus IDE v1.6.4. QuoteCode: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i    SQLQuery1.UsePrimaryKeyAsKey := false;Does table Altium have no primary key? Why are you not using editable dbgrid? You could insert, delete or update information directly on dbgrid. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.init_SQLQuery1;begin   SQLQuery1.DataBase := ODBCConnection1;   SQLQuery1.Transaction := SQLTransaction1;      SQLQuery1.ParseSQL := False;   SQLQuery1.DeleteSQL.Text  := 'delete from Altium where PartNumer = :PartNumer';   SQLQuery1.InsertSQL.Text  := 'insert into Altium(Field1, Field2, Field3) values (:Field1, :Field2, :Field3)';   SQLQuery1.RefreshSQL.Text := 'Select Field1, Field2, Field3 From Altium where PartNumer = :PartNumer';     SQLQuery1.SQL.Text        := 'select Field1, Field2, Field3 from Altium';   SQLQuery1.UpdateSQL.Text  := 'Update Altium Set Field1 = :Field1, Field2 = :Field2, Field3 = :Field3 Where PartNumer = :PartNumer';      SQLQuery1.UsePrimaryKeyAsKey := false;   SQLQuery1.Active := true;end; procedure TForm1.Button_save_all_changes_and_close_table(Sender: TObject);begin  SQLQuery1.ApplyUpdates(0);  SQLTransaction1.Commit;end; And it would be also easier for you could to change information: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.Button_change_information_easily(Sender: TObject);begin  SQLQuery1.Edit;  SQLQuery1.FieldByName('Field1').AsString := Edit1.Text;  SQLQuery1.FieldByName('Field2').AsString := Edit2.Text;  SQLQuery1.FieldByName('Field3').AsString := Edit3.Text;  SQLQuery1.Post;  SQLQuery1.ApplyUpdates(0);  SQLTransaction1.Commit;end; ---------------------------------------------------------------------- Nickedom · 2018-09-14 12:57 Hi valdir.macros The table Altium has got a primary key. The name is “ID_primarykey” With Access I set this field to a primarykey. But if I do: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SQLQuery1.UsePrimaryKeyAsKey := true The error occurs: “The driver doesn’t support this function.” See the whole error-message in attachment. What is wrong? Is it the wrong driver? In which cases I need a primarykey, to save changes into the database? Do I need a primarykey in my current programm? Do I need a primarykey in editable DBGrid? The field ID_primarykey is uniquely. There are integers. This programm is in the moment only an experiment. But if the experiment is finished, I want write the programm which I need. I need in the destination-programm editing with TEdit I want use TEDit to give the user a pleasant possibility to enter the fields contents. Beside of the TEdit should be always a text which describes what should be written in. Also a description in which norm the content should be written. I want use DBGrid only for showing the table in tableform. Not for editing. Thank you for your previous answers. Nickedom ---------------------------------------------------------------------- valdir.marcos · 2018-09-14 15:16 Quote from: Nickedom on September 14, 2018, 02:57:32 pmThe table Altium has got a primary key. The name is “ID_primarykey” With Access I set this field to a primarykey. But if I do: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SQLQuery1.UsePrimaryKeyAsKey := trueThe error occurs: “The driver doesn’t support this function.” See the whole error-message in attachment. What is wrong? Is it the wrong driver?I can't help on this since I neither have Microsoft Access on this computer nor I have used recently. QuoteIn which cases I need a primarykey, to save changes into the database? Do I need a primarykey in my current programm? Do I need a primarykey in editable DBGrid?Primary Key makes everything easier and faster, but it's not obligatory.