HOW TO DELETE TWO LINKED ROW FROM MYSQL DATABASE USING DELPHI ???

[havsys]

Hello,

Can somebody show my how to delete two linked row using delphi.

I Use delphi 2007 and MySql.

In MySql i have database with two Table. Table Contacts(ID, FirstName, LastName, Phone, PICID) and table Picture(ID, IMG)
MySql Tables:

CREATE TABLE `Picture`.`Picture` (
  `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `IMG` LONGBLOB,
  PRIMARY KEY (`ID`)
)
ENGINE = InnoDB;

CREATE TABLE `contacts` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `FirstName` varchar(45) NOT NULL,
  `LastName` varchar(45) NOT NULL,
  `Phone` varchar(45) default NULL,
  `PICID` int(10) unsigned default NULL,
  PRIMARY KEY  (`ID`),
  KEY `FK_contacts_1` (`PICID`),
  CONSTRAINT `FK_contacts_1` FOREIGN KEY (`PICID`) REFERENCES `picture` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

In my Delphi Application I have Delete Button. When I find some Contact on my DBGrid and press DELETE Button I can delete only contact from table Contact, I aslo want to delete contact picture.

I want to delete row from table CONTACTS and row from Table PICTURE. Table Picture Is linked to table Contact With foreign key.

 

[imex]

Hi,

I have no experience with MySQL, but I think you could activate the action Cascade in the foreign key to automatically delete the Picture when delete a Contact.

If you really want to delete by the application, then you can use code in BeforeDelete event of Contacts DataSet to delete the picture.

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[havsys]

Thank you imex.

It is working!

 

[havsys]

Hello imex,

I have small problem with deleting row from two table. Can you help me with this.

I put code in BeforeDelete event of Contacts DataSet to delete the picture how you tell me.
I can delete Row but I always delete the last insert row.

if I want to delete contact with ID=7(from table Contacts) I will delete Contact Picture with ID=15 (from table Picture).

ADODataSet for table Contact is my "TContacts"
ADODataSet for Table Picture is my "TPicture"
Data modul is my "dm"

For delete contacts I use this code: dm.TContacts.Delete; - works fine
For BeforeDelete event of Contacts DataSet(TContacts) to delete the picture I use this code: dm.TPicture.Delete - But I delete only last inserted row.
Table Contacts and table Picture are conected with FOREIGN KEY. table CONTACTS(PICID)-->(ID) table PICTURE.

 

[imex]

Hi,

Try using the Locate method to position the DataSet in the correct record before deleting:

if TPicture.Locate('ID', TContacts.FieldByName('PICID').AsInteger, []) then
    TPicture.Delete
else
    ShowMessage('Picture not found.');

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]