Cannot update a record using SQLQuery, Update etc.
lazarus / 16-Databases

----------------------------------------------------------------------
ranny · 2025-03-19 07:28

Hi,

Many years ago I wrote a program that reads in an Access database and
presented the user with some simple search options, it was written in
Visual basic and the code is lost.  More recently I have tried to use
Lazarus and I have struggled but in the last week or two I have made
some progress.

The principle is that it will read the database structure, present the
records in a stringgrid, allow searches depending on the field type
etc.  Nothing too difficult.

However, whilst I can get that all working fairly well, I cannot seem
to be able to update a record.  The way I wanted to do it was present
eh record in a stringgrid, allow the fields to be edited, and then
replace the whole record with the contents of the stringgrid.

Here's what I tried:-

//make ODBC connection
    ODBCConnection1.Driver:= 'Microsoft Access Driver (*.mdb,
*.accdb)';
    ODBCConnection1.Params.Add('DBQ=.\id931.mdb');      // or specify
full path to mdb file
    ODBCConnection1.Params.Add('Locale Identifier=1031');
    ODBCConnection1.Params.Add('ExtendedAnsiSQL=1');
    ODBCConnection1.Params.Add('CHARSET=ansi');
    ODBCConnection1.Connected:= True;
    ODBCConnection1.KeepConnection:= True;

    //transaction
    SQLTransaction1.DataBase:= ODBCConnection1;
    SQLTransaction1.Action:= caCommit;
    SQLTransaction1.Active:= True; //not applied to ms access(false)

    SQLQuery0.DataBase:= ODBCConnection1;
    SQLQuery0.UsePrimaryKeyAsKey:= False;
    DataSource0.DataSet:= SQLQuery0;   

    SQLQuery0.Update.Text:=<a large string>

    SQLQuery0.Open;

   SQLQuery0.ApplyUpdates

And then I get errors relating to databse not open or Operation cannot
be performed on an inactive dataset.

Ive tried a few things like SQLQuery0.ExecSQL an others and no joy.

I would welcome any help on this as the project is nearly finished and
this is the last thing to fix.

Thanks in advance.

----------------------------------------------------------------------
Zvoni · 2025-03-19 07:44

SQLQuery-Object needs an assigned Transaction.

Besides your Problem: If you have to rewrite everything (since your
vb-code is lost), throw the Access-DB where it belongs: The rubbish-
bin.
Use SQLite

----------------------------------------------------------------------
ranny · 2025-03-19 08:04

Hi,

Thanks for the comments.   I have:-

    //transaction
    SQLTransaction1.DataBase:= ODBCConnection1;
    SQLTransaction1.Action:= caCommit;
    SQLTransaction1.Active:= True; //not applied to ms access(false)

Is this not what you suggest?  It doesnt appear to be associated  with
my SQLQuery right enough.

Sorry but I am not so good with this Databse stuff.

I understand the comment on SQLite but all the databases exist and not
necessarily under my control....

Thanks.

----------------------------------------------------------------------
CharlyTango · 2025-03-19 08:17

Without even having any possibility to test with Access, I can only
make assumptions

Check if the ODBC connection to Access ist stable and working. e.g. by
showing some data in a TDBGrid and using a simple SQL Query like
"SELECT x,y, from z WHERE anyclause ".

Use an SQLQuery.Open for Queries that deliver results, otherwise use
SQLQuery.ExexSQL

Try an SQL Statement directly in Access first to check whether it is
valid and works to avoid Syntax problems.

Quote from: ranny on March 19, 2025, 08:28:19 am   
SQLQuery0.Update.Text:=<a large string>
    SQLQuery0.Open;
   SQLQuery0.ApplyUpdates

This syntax is intended for use with data-sensitive input fields. For
submitting a single UPDATE statement use

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
SQLQuery0.SQL.Text:=<a large string>;SQLQuery0.ExecSQL;
Besides... avoid Access at all -- use SQLite instead

----------------------------------------------------------------------
TRon · 2025-03-19 08:20

Quote from: ranny on March 19, 2025, 09:04:14 amSorry but I am not so
good with this Databse stuff.
Example in wiki, basic use of SQLDB in documentation though in your
example code the components are already created because done at design
time (I assume) so you only have to follow how things are linked
together.

----------------------------------------------------------------------
Zvoni · 2025-03-19 08:22

Quote from: ranny on March 19, 2025, 09:04:14 amHi,

Thanks for the comments.   I have:-

    //transaction
    SQLTransaction1.DataBase:= ODBCConnection1;
    SQLTransaction1.Action:= caCommit;
    SQLTransaction1.Active:= True; //not applied to ms access(false)

Is this not what you suggest?  It doesnt appear to be associated  with
my SQLQuery right enough.

Sorry but I am not so good with this Databse stuff.

I understand the comment on SQLite but all the databases exist and not
necessarily under my control....

Thanks.

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
SQLQuery0.DataBase:=
ODBCConnection1;SQLQuery0.Transaction:=SQLTransaction1;  //<--
HERESQLQuery0.UsePrimaryKeyAsKey:= False;DataSource0.DataSet:=
SQLQuery0;

----------------------------------------------------------------------
CharlyTango · 2025-03-19 08:32

Regarding transactions i would try it the other way round, but don't
know whether it makes a difference:

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
ODBCConnection1.Transaction:= SQLTransaction1; 
In my opinion this statement assigns a distinct transaction to all
queries using ODBCConnection1.
My applications use this syntax and they work.
Maybe there is a possibility to use other transactions in certain
query cascades as suggested by @zvoni

I' dont know exactly which way ist the better one. Try both of them
;-)

----------------------------------------------------------------------
ranny · 2025-03-19 08:57

Thanks everyone for your advice and suggestions.  I carry out some
edits and see what happens and report.

Cheers.

----------------------------------------------------------------------
ranny · 2025-03-19 09:19

Ach, driving me nuts!

Code is this:-
    SQLQuery0.DataBase:= ODBCConnection1;
    SQLQuery0.Transaction:=SQLTransaction1;;
    SQLQuery0.UsePrimaryKeyAsKey:= False;
    DataSource0.DataSet:= SQLQuery0;


    SQLQuery0.ExecSQL;
    SQLQuery0.UpdateSQL.Text:=fs;
    SQLQuery0.Edit;

I get this:-
"Operation cannot be performed on an inactive dataset"

at SQLQuery0.Edit


If I use SQLQuery0.Post instead of SQLQuery0.Edit, I get this error
"SQLQuery0: Operation not allowed, dataset "SQLQuery0" is not in an
edit or insert state"

Time for a coffee break....

----------------------------------------------------------------------
Zvoni · 2025-03-19 09:35

Quote from: ranny on March 19, 2025, 10:19:06 amAch, driving me nuts!

Code is this:-
    SQLQuery0.DataBase:= ODBCConnection1;
    SQLQuery0.Transaction:=SQLTransaction1;;
    SQLQuery0.UsePrimaryKeyAsKey:= False;
    DataSource0.DataSet:= SQLQuery0;


    SQLQuery0.ExecSQL;
    SQLQuery0.UpdateSQL.Text:=fs;
    SQLQuery0.Edit;

I get this:-
"Operation cannot be performed on an inactive dataset"

at SQLQuery0.Edit


If I use SQLQuery0.Post instead of SQLQuery0.Edit, I get this error
"SQLQuery0: Operation not allowed, dataset "SQLQuery0" is not in an
edit or insert state"

Time for a coffee break....

Spot the mistake......
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    
SQLQuery0.ExecSQL;    SQLQuery0.UpdateSQL.Text:=fs;    SQLQuery0.Edit;
 ExecSQL, then changing the SQL-text, THEN goint to Edit-Mode....
*sigh*

----------------------------------------------------------------------
ranny · 2025-03-19 11:38

Apologies, I am trying to read up on this online and I find it a
minefield.

Zvoni, you say "ExecSQL, then changing the SQL-text, THEN goint to
Edit-Mode.... *sigh*"

when referencing:-

SQLQuery0.ExecSQL;
SQLQuery0.UpdateSQL.Text:=fs;
SQLQuery0.Edit;

With three lines as above there are six scenarios of which comes
first, second and third, I tried them all and they all fail on
SQLQuery0.edit no matter where it is placed.

Something adrift elsewhere.

I think I will park this project for a while and do some reading up.

Thanks for your input.

----------------------------------------------------------------------
cdbc · 2025-03-19 11:49

Hi
Try this instead: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 SQLQuery0.Close;SQLQuery0.SQL.Text:=fs;SQLQuery0.ExecSQL;That
just might work...
Regards Benny

----------------------------------------------------------------------
1HuntnMan · 2025-03-19 13:42

Zvoni recommended what I am in the process of doing.  I am currently
finishing converting an app that was written for a TDbf database.
Chunk the MS-Access and use SQLite3. I have all my source so once you
get the hang of properly declaring your database, creating a
transaction, connecting to the database and then opening, your in
business. I'm in a learning process but it's working. Here's an
example of what I learned, mostly from TRon but others.
If you do decide to move to SQLite3 as Zvoni recommended and what I
learned from TRon, et. al., create a datamodule or just a Form and
opening it up in your main Program file before anything
else(Application.CreateForm(DB or Form)). Then put a
TSQLite3Connection, TSQLQuery and TSQLTransaction on the DM or Form.
Then in the DM/Form Connect to your database, then create a
transaction pointing to your connect, point to the database by
assigning your connect to your transaction and then open it and point
to the database and the transaction. Here's the way I did from
learning from TRon in my datamodule.

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
TDmPMSDataBase.DataModuleCreate(Sender: TObject);begin 
ConnectPMSDB.DatabaseName:= SysUtils.ExtractFilePath(ParamStr(0)) +
'PMSDB.sqlite';  //-> Create a transactions...  TransPMSDB:=
TSQLTransaction.Create(ConnectPMSDB);  //-> Point to the database
instance...  TransPMSDB.Database:= ConnectPMSDB;  //-> Open the PMS
database...  ConnectPMSDB.Open;  //-> Point to the database and
transaction...  QueryPMSDB.Database:= ConnectPMSDB; 
QueryPMSDB.Transaction:= TransPMSDB;end; 
Then as your writing code you just use SQL, this is snippet from just
my Clients Management part of my application:
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
TFrmClientsMgt.FormShow(Sender: TObject);begin  //-> Set the SQL
select statement...  DmPMSDataBase.QueryPMSDB.SQL.Text:= 'SELECT *
FROM CLIENTS';  //-> Now use the standard TDataset methods... 
DmPMSDataBase.QueryPMSDB.Open;  BitBtnSave.Enabled:= False; 
BitBtnCancel.Enabled:= False; Hope this helps but this is SQLite3, not
MSAccess.

----------------------------------------------------------------------
1HuntnMan · 2025-03-19 19:35

SQLite is the most used database engine in the world...
https://www.sqlite.org/mostdeployed.html

----------------------------------------------------------------------
egsuh · 2025-03-20 01:57

Simply from curiosity...

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
TDmPMSDataBase.DataModuleCreate(Sender: TObject);begin 
ConnectPMSDB.DatabaseName:= SysUtils.ExtractFilePath(ParamStr(0)) +
'PMSDB.sqlite';   TransPMSDB:= TSQLTransaction.Create(ConnectPMSDB);
// Question here    TransPMSDB.Database:= ConnectPMSDB; 
ConnectPMSDB.Open;   QueryPMSDB.Database:= ConnectPMSDB; 
QueryPMSDB.Transaction:= TransPMSDB;  // comment: this is not
necessary. end;
Question:
It seems that you have dropped a TSQLite3connection component and
TSQLQuery component from component pallette, but why not
TSQLTransaction? Why do you create it here?

Comment:

When you assign database to a query, the default transaction is used.
You don't to have to assign it again, unless you are going to use
different transaction.

Your way has no problem. I'm asking simply from curiosity.

----------------------------------------------------------------------
Zvoni · 2025-03-20 13:39

Quote from: egsuh on March 20, 2025, 02:57:57 amComment:

When you assign database to a query, the default transaction is used.
You don't to have to assign it again, unless you are going to use
different transaction.

Your way has no problem. I'm asking simply from curiosity.
Not that i know of.
I always had to connect the 3 (or more) Objects as described in
Wiki/Documentation:
Create Connection-Object
Create Transaction-Object (or as many as you need)
Assign Transaction-Object to Transaction-Property of Connection-Object
(only one Transaction per Connection?)
Assign Connection-Object to Database-Property of Transaction(s)
Create Query-Object(s)
Assign Transaction-Object to Transaction-Property of Query
Assign Connection-Object to Database-Property of Query
Open Connection
Activate Transaction(s)
Open Query(s)

EDIT: I just looked into the Source-Code of sqlite3conn:
there is no "Default"-Transaction-Object for sqlite3connection, only
the Inherited Transaction-Object of its Ancestor TSQLconnection.
and in sqldb (TSQLConnection) i found this snippet
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
TSQLConnection.GetDBInfo(const ASchemaType : TSchemaType; const
ASchemaObjectName, AReturnField : string; AList: TStrings); var qry :
TCustomSQLQuery; begin  if not assigned(Transaction) then   
DatabaseError(SErrConnTransactionnSet); If transaction is not Assigned
return what??

----------------------------------------------------------------------
egsuh · 2025-03-22 00:10

QuoteEDIT: I just looked into the Source-Code of sqlite3conn:
there is no "Default"-Transaction-Object for sqlite3connection, only
the Inherited Transaction-Object of its Ancestor TSQLconnection.
and in sqldb (TSQLConnection) i found this snippet
Hmmm..  I'm more accustomed to using components from component
palletes.
Generally I drop one database, one transaction, and sqlquery
components on Datamodule, and set Transaction's database to Database,
and then SQLQuery's database to database.  Then at the object
inspector of sqlquery, transaction is set automatically. Not sure when
programmed.

I love easy life ^^