Lazarus

Programming => Databases => Topic started by: Nickedom on September 14, 2018, 10:15:43 am

Title: Can't save changes of Access-database. Using ODBC
Post by: Nickedom on September 14, 2018, 10:15:43 am
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 (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 (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][+][-]
  1. procedure TForm1.init_all;
  2. begin
  3.    init_DBGrid;
  4.    init_DataSource1;
  5.    init_ODBCConnection1;
  6.    init_SQLTransaction1;
  7.    init_SQLQuery1;
  8. end;    
  9.  
  10.  
  11.  
  12. procedure TForm1.init_DBGrid;
  13. begin
  14.    DBGrid1.DataSource := DataSource1;
  15. end;
  16.  
  17.  
  18. procedure TForm1.init_DataSource1;
  19. begin
  20.    DataSource1.DataSet :=SQLQuery1;
  21. end;
  22.  
  23.  
  24. procedure TForm1.init_ODBCConnection1;
  25. begin
  26.    ODBCConnection1.DatabaseName := 'lib_for_GUI';
  27.    ODBCConnection1.UserName := '';
  28.    ODBCConnection1.Password := '';
  29.    ODBCConnection1.Transaction := SQLTransaction1;;
  30.    ODBCConnection1.Connected := true;
  31. end;
  32.  
  33.  
  34. procedure TForm1.init_SQLTransaction1;
  35. begin
  36.    SQLTransaction1.DataBase := ODBCConnection1;
  37.    SQLTransaction1.Active := true;
  38. end;
  39.  
  40.  
  41. procedure TForm1.init_SQLQuery1;
  42. begin
  43.    SQLQuery1.DataBase := ODBCConnection1;
  44.    SQLQuery1.Transaction := SQLTransaction1;
  45.    SQLQuery1.SQL.Text := 'select * from Altium';
  46.    SQLQuery1.UsePrimaryKeyAsKey := false;
  47.    SQLQuery1.Active := true;
  48. end;
  49.  
  50.  

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 (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][+][-]
  1. procedure TForm1.Button_Save_to_h00001Click(Sender: TObject);
  2. begin
  3.   SQLQuery1.Close;
  4.   SQLQuery1.SQL.Clear;
  5.   SQLQuery1.SQL.Add('UPDATE Altium SET h_MGR = ''huibuh'' WHERE PartNumer = ''h00001''');
  6.   ShowMessage('SQLQuery1.SQL.Text=' + sLineBreak + SQLQuery1.SQL.Text );
  7.  
  8.   ODBCConnection1.connected := True;
  9.   SQLTransaction1.Active := True;
  10.   SQLQuery1.Open;
  11.  
  12.   SQLQuery1.ApplyUpdates();
  13.   SQLTransaction1.Commit;
  14. end;
  15.  

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.


Title: Re: Can't save changes of Access-database. Using ODBC
Post by: balazsszekely on September 14, 2018, 10:28:11 am
Hi Nickedom,

Quote
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.
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][+][-]
  1.  
  2.   SQLQuery1.SQL.Text := 'update tablename set fieldname = :param';
  3.   SQLQuery1.Params.BeginUpdate;  
  4.   SQLQuery1.ParamByName('param').AsInteger := 12;
  5.   SQLQuery1.Params.EndUpdate;
  6.   SQLQuery1.ExecSQL;
  7.  

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][+][-]
  1.   SQLQuery1.Close;
  2.   SQLQuery1.SQL.Clear;
  3.   SQLQuery1.SQL.Add('UPDATE Altium SET h_MGR = :param1 WHERE PartNumer = :param2');
  4.   SQLQuery1.Params.BeginUpdate;
  5.   SQLQuery1.ParamByName('param1').AsString := 'huibuh';
  6.   SQLQuery1.ParamByName('param2').AsString := 'h00001';
  7.   SQLQuery1.Params.EndUpdate;
  8.   ShowMessage('SQLQuery1.SQL.Text=' + sLineBreak + SQLQuery1.SQL.Text );
  9.  
  10.   ODBCConnection1.connected := True;
  11.   SQLTransaction1.Active := True;
  12.   SQLQuery1.ExecSQL;
  13.  
  14.   SQLQuery1.ApplyUpdates()
  15.   SQLTransaction1.Commit;
Title: Re: Can't save changes of Access-database. Using ODBC
Post by: Nickedom on September 14, 2018, 11:08:14 am
Hi GetMem,

thank you for the fast answer.

I changed the code like you has written.

Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button_Save_to_h00001Click(Sender: TObject);
  2. begin
  3.   SQLQuery1.Close;
  4.   SQLQuery1.SQl.Clear;
  5.  
  6.   SQLQuery1.SQL.Add('UPDATE Altium SET h_MGR = :param1 WHERE PartNumer = :param2');
  7.   SQLQuery1.Params.BeginUpdate;
  8.   SQLQuery1.ParamByName('param1').AsString := 'huibuh';
  9.   SQLQuery1.ParamByName('param2').AsString := 'h00001';
  10.   SQLQuery1.Params.EndUpdate;
  11.   ShowMessage('SQLQuery1.SQL.Text=' + sLineBreak + SQLQuery1.SQL.Text );
  12.  
  13.   ODBCConnection1.connected := True;
  14.   SQLTransaction1.Active := True;
  15.   //SQLQuery1.Open;
  16.   SQLQuery1.ExecSQL;
  17.  
  18.   SQLQuery1.ApplyUpdates();
  19.   SQLTransaction1.Commit;
  20. end;
  21.  

Now it comes an other error-message: plaase see attachment:

The result of the procedure ShowMessage see in the2nd attachment:

Title: Re: Can't save changes of Access-database. Using ODBC
Post by: balazsszekely on September 14, 2018, 11:34:04 am
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][+][-]
  1.  SQLQuery1.SQL.Add('UPDATE yourtable set yourfield = 1');
  2.  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.
Title: Re: Can't save changes of Access-database. Using ODBC
Post by: Nickedom on September 14, 2018, 12:48:07 pm
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][+][-]
  1.   SQLQuery1.Close;
  2.   SQLQuery1.SQl.Clear;
  3.   SQLQuery1.SQL.Add('UPDATE Altium SET h_MGR = :param1 WHERE PartNumber = :param2'); // I fergot the b in PartNumber
  4.   SQLQuery1.Params.BeginUpdate;
  5.   SQLQuery1.ParamByName('param1').AsString := 'huibuh';
  6.   SQLQuery1.ParamByName('param2').AsString := 'h00001';
  7.   SQLQuery1.Params.EndUpdate;
  8.  
  9.   ShowMessage('SQLQuery1.SQL.Text=' + sLineBreak + SQLQuery1.SQL.Text );
  10.  
  11.   ODBCConnection1.connected := True;
  12.   SQLTransaction1.Active := True;
  13.   //SQLQuery1.Open;
  14.   SQLQuery1.ExecSQL;
  15.  
  16.   SQLQuery1.ApplyUpdates();   //<-----Before I have included the "b" in the word PartNumber the error occurs here
  17.   SQLTransaction1.Commit;
  18.  

Also I simplified the code, like you suggested:

Code: Pascal  [Select][+][-]
  1.   SQLQuery1.Close;
  2.   SQLQuery1.SQl.Clear;
  3.   SQLQuery1.SQL.Add('UPDATE Altium SET test = 1');
  4.  
  5.   ShowMessage('SQLQuery1.SQL.Text=' + sLineBreak + SQLQuery1.SQL.Text );
  6.  
  7.   ODBCConnection1.connected := True;
  8.   SQLTransaction1.Active := True;
  9.   //SQLQuery1.Open;
  10.   SQLQuery1.ExecSQL;
  11.  
  12.   SQLQuery1.ApplyUpdates();   //<-----new error occurs here
  13.   SQLTransaction1.Commit;
  14.  

Here comes the same error-message.

Title: Re: Can't save changes of Access-database. Using ODBC
Post by: valdir.marcos on September 14, 2018, 01:15:43 pm
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.

Quote
Code: Pascal  [Select][+][-]
  1.    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][+][-]
  1. procedure TForm1.init_SQLQuery1;
  2. begin
  3.    SQLQuery1.DataBase := ODBCConnection1;
  4.    SQLQuery1.Transaction := SQLTransaction1;
  5.    
  6.    SQLQuery1.ParseSQL := False;
  7.    SQLQuery1.DeleteSQL.Text  := 'delete from Altium where PartNumer = :PartNumer';
  8.    SQLQuery1.InsertSQL.Text  := 'insert into Altium(Field1, Field2, Field3) values (:Field1, :Field2, :Field3)';
  9.    SQLQuery1.RefreshSQL.Text := 'Select Field1, Field2, Field3 From Altium where PartNumer = :PartNumer';  
  10.    SQLQuery1.SQL.Text        := 'select Field1, Field2, Field3 from Altium';
  11.    SQLQuery1.UpdateSQL.Text  := 'Update Altium Set Field1 = :Field1, Field2 = :Field2, Field3 = :Field3 Where PartNumer = :PartNumer';
  12.    
  13.    SQLQuery1.UsePrimaryKeyAsKey := false;
  14.    SQLQuery1.Active := true;
  15. end;
  16.  
  17. procedure TForm1.Button_save_all_changes_and_close_table(Sender: TObject);
  18. begin
  19.   SQLQuery1.ApplyUpdates(0);
  20.   SQLTransaction1.Commit;
  21. end;

And it would be also easier for you could to change information:
Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button_change_information_easily(Sender: TObject);
  2. begin
  3.   SQLQuery1.Edit;
  4.   SQLQuery1.FieldByName('Field1').AsString := Edit1.Text;
  5.   SQLQuery1.FieldByName('Field2').AsString := Edit2.Text;
  6.   SQLQuery1.FieldByName('Field3').AsString := Edit3.Text;
  7.   SQLQuery1.Post;
  8.   SQLQuery1.ApplyUpdates(0);
  9.   SQLTransaction1.Commit;
  10. end;
Title: Re: Can't save changes of Access-database. Using ODBC
Post by: Nickedom on September 14, 2018, 02:57:32 pm
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][+][-]
  1. 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
Title: Re: Can't save changes of Access-database. Using ODBC
Post by: valdir.marcos on September 14, 2018, 05:16:54 pm
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][+][-]
  1. 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?
I can't help on this since I neither have Microsoft Access on this computer nor I have used recently.

Quote
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?
Primary Key makes everything easier and faster, but it's not obligatory.
TinyPortal © 2005-2018