* * *

Author Topic: Delete record failed through dbnavigator.nbDelete  (Read 3487 times)

Goodman H__

  • Full Member
  • ***
  • Posts: 130
Delete record failed through dbnavigator.nbDelete
« 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.



fpc:2.6.1 Lazarus:1.1 SVN39277
OS:win 7

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Delete record failed through dbnavigator.nbDelete
« Reply #1 on: January 15, 2012, 03:09:04 pm »
One very obvious one: have you set the DBNavigator's datasource
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1#Deleting_data
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

goodname

  • Full Member
  • ***
  • Posts: 208
Re: Delete record failed through dbnavigator.nbDelete
« Reply #2 on: January 15, 2012, 03:15:18 pm »
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.
« Last Edit: January 15, 2012, 03:44:11 pm by goodname »

Goodman H__

  • Full Member
  • ***
  • Posts: 130
Re: Delete record failed through dbnavigator.nbDelete
« Reply #3 on: January 18, 2012, 01:57:22 am »
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:
Code: [Select]
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:
Code: [Select]
if button=nbPost then
begin
  sqlQuery1.edit;
  sqlQuery1.ApplyUpdates;
  sqlTransaction1.CommitRetaining;
end;
   
   
« Last Edit: January 18, 2012, 02:03:06 am by Goodman H__ »
fpc:2.6.1 Lazarus:1.1 SVN39277
OS:win 7

goodname

  • Full Member
  • ***
  • Posts: 208
Re: Delete record failed through dbnavigator.nbDelete
« Reply #4 on: January 18, 2012, 11:56:26 pm »
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.

Goodman H__

  • Full Member
  • ***
  • Posts: 130
Re: Delete record failed through dbnavigator.nbDelete
« Reply #5 on: September 04, 2013, 08:59:27 am »
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.
Code: [Select]
if tr.Active=false then
   begin

     tr.Active:=true;
     sq.Active:=true;
     sq.Open;
   end;                   
« Last Edit: September 04, 2013, 09:04:53 am by Goodman H__ »
fpc:2.6.1 Lazarus:1.1 SVN39277
OS:win 7

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus