Lazarus
Programming => Databases => Topic started by: Goodman H__ on January 15, 2012, 02:09:26 pm
-
Greetings,
The lastest snapshot under windows xp.
I just want to simply delete a record by clicking the nbDelete button in the dbNavigator controls.The confirmation windows pops up and asking whether delete it or not.I pressed Yes.But when I check in the database file,the record is still there.I am using TODBCconnection,TSqlTransaction and TSqlQuery to connect to MS Access file (*.mdb).
Could anybody here to figure me out whether there is anything I missed?
Thanks for you help in advance.
-
One very obvious one: have you set the DBNavigator's datasource
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1#Deleting_data (http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1#Deleting_data)
-
Define a TSQLQuery AfterDelete event linked with the dbNavigator through the TDatasource. Find the Transaction linked with the TSQLQuery and place ApplyUpdates followed by TSQLTransaction.Commit or TSQLTransaction.CommitRetaining.
The same kind of thing should be done for TSQLQuery afterPost events to handle Inserts and Updates.
-
Hi all,
Thanks for the hints and tips.
I managed to have the sample program work by below code.I know there may be problems on the code.Grateful for any comments.
1.In sqlQuery1,add params according to the primary key field and the field to be updated;
2.write deleteSql,updateSql statement;
3.For delete,in dbnavigator1:
if button=nbDelete then
begin
sqlQuery1.ApplyUpdates;
sqlTransaction1.Commit;
end;
{
After delete,the dataSet closed so I need to re-open it:
}
if sqlTransaction1.active=false then
begin
sqlTransaction1.active:=true;
sqlQuery1.active:=true;
sqlQuery1.open;
end;
{
as the table contains Chinese character fields,I use a routine to have lazarus print Chinese font correctly.
function utf8DataSet:TSqlQuery;
begin
....
result:=someVar;
end;
In the form's onCreate event:
sqlQuery1:=utf8DataSet;
But after deletion,the Chinese font issue ocurred again,they are not recognizable!
So I fixed the issue by adding below code in sqlQuery1's afterScroll event:
sqlQuery1:=utf8DataSet;
}
4.For update,insert,in dbnavigator1:
if button=nbPost then
begin
sqlQuery1.edit;
sqlQuery1.ApplyUpdates;
sqlTransaction1.CommitRetaining;
end;
-
Usually put the apply and commit code into TSQLQuery.afterDelete and afterPost events as it captures all possible delete, update, and insert event sources.
Using CommitRetaining should stop the query from closing after delete.
Thanks for mentioning the sqlQuery1:=utf8DataSet; Have not needed unicode yet so had not figured this part out yet.
-
Revisiting the update and deletion topic.
There are issues when ApplyUpdates and commits statement placed in AfterPost and AfterDelete events:After an Insertion or Deletion,it seems that the sqlquery/transaction/database's shut down,the dbGrid's cleared with the contents ,and both dbnavigator get to grey/disabled.(But the transaction's been successed after checking MS Access Database file).
I managed to make it after adding below codes to both places(AfterPost and AfterDelete).I don't test whether it has to add to AfterInsert and AfterEdit as well for all cases.If this is what we have to do,a bit worry the code bloated everywhere.
Any tips would be much appreciated.
if tr.Active=false then
begin
tr.Active:=true;
sq.Active:=true;
sq.Open;
end;