Cannot update a record using SQLQuery, Update etc.

[ranny]

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]

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]

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]

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.
 

    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][+][-]

1. SQLQuery0.SQL.Text:=<a large string>;
2. SQLQuery0.ExecSQL;

Besides... avoid Access at all -- use SQLite instead

[TRon]

Sorry 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]

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.

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

1. SQLQuery0.DataBase:= ODBCConnection1;
2. SQLQuery0.Transaction:=SQLTransaction1;  //<-- HERE
3. SQLQuery0.UsePrimaryKeyAsKey:= False;
4. DataSource0.DataSet:= SQLQuery0;    

[CharlyTango]

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

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

1. ODBCConnection1.Transaction:= SQLTransaction1;
2.  

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]

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

Cheers.

[ranny]

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]

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....

Spot the mistake......

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

1.     SQLQuery0.ExecSQL;
2.     SQLQuery0.UpdateSQL.Text:=fs;
3.     SQLQuery0.Edit;
4.  

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

[ranny]

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]

Hi
Try this instead:

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

1. SQLQuery0.Close;
2. SQLQuery0.SQL.Text:=fs;
3. SQLQuery0.ExecSQL;

That just might work...
Regards Benny

[1HuntnMan]

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][+][-]

1. procedure TDmPMSDataBase.DataModuleCreate(Sender: TObject);
2. begin
3.   ConnectPMSDB.DatabaseName:= SysUtils.ExtractFilePath(ParamStr(0)) + 'PMSDB.sqlite';
4.   //-> Create a transactions...
5.   TransPMSDB:= TSQLTransaction.Create(ConnectPMSDB);
6.   //-> Point to the database instance...
7.   TransPMSDB.Database:= ConnectPMSDB;
8.   //-> Open the PMS database...
9.   ConnectPMSDB.Open;
10.   //-> Point to the database and transaction...
11.   QueryPMSDB.Database:= ConnectPMSDB;
12.   QueryPMSDB.Transaction:= TransPMSDB;
13. end;
14.  

Then as your writing code you just use SQL, this is snippet from just my Clients Management part of my application:

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

1. procedure TFrmClientsMgt.FormShow(Sender: TObject);
2. begin
3.   //-> Set the SQL select statement...
4.   DmPMSDataBase.QueryPMSDB.SQL.Text:= 'SELECT * FROM CLIENTS';
5.   //-> Now use the standard TDataset methods...
6.   DmPMSDataBase.QueryPMSDB.Open;
7.   BitBtnSave.Enabled:= False;
8.   BitBtnCancel.Enabled:= False;
9.  

Hope this helps but this is SQLite3, not MSAccess.

[1HuntnMan]

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

[egsuh]

Simply from curiosity...

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

1. procedure TDmPMSDataBase.DataModuleCreate(Sender: TObject);
2. begin
3.   ConnectPMSDB.DatabaseName:= SysUtils.ExtractFilePath(ParamStr(0)) + 'PMSDB.sqlite';
4.  
5.   TransPMSDB:= TSQLTransaction.Create(ConnectPMSDB); // Question here
6.  
7.   TransPMSDB.Database:= ConnectPMSDB;
8.   ConnectPMSDB.Open;
9.  
10.   QueryPMSDB.Database:= ConnectPMSDB;
11.   QueryPMSDB.Transaction:= TransPMSDB;  // comment: this is not necessary.
12. 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.