delete records from multiple tables

[afnan_88]

hi
i'm trying to delete records from multible tables

i have 5 tables

ITEM,CATEGORY,PICTURE,VIDEO,AUDIO

every item has a picture and vedio & audio
and the item has a category

in my app i take the category name and delete it with all its items & vedios &pictures & audios
(note : there is no relation between the CATEGORY & PICTURE,VIDEO,AUDIO but i have a relation between the ITEM & CATEGORY using the CATEGORYId)


itry the following:

DELETE ITEM.*,CATEGORY.*,PICTURE.*,VIDEO.*,AUDIO.* FROM ITEM,CATEGORY,PICTURE,VIDEO,AUDIO WHERE ITEM.CategoryId=CATEGORY.CategoryId AND ITEM.PictureId=PICTURE.PictureId AND ITEM.VideoId=VIDEO.VideoId AND ITEM.AudioId=AUDIO.AudioId AND CategoryName ='" + textBox1.Text.ToString() + "' ;";


the msg the i get : 0 categoryes deleted !


some one advise me to use inner join
so i try :

"DELETE CATEGORY.*,ITEM.*,PICTURE.*,VIDEO.*,AUDIO.*
FROM CATEGORY INNER JOIN (PICTURE INNER JOIN (VIDEO INNER JOIN (AUDIO INNER JOIN ITEM ON ITEM.AudioId=AUDIO.AudioId )
ON ITEM.VideoId=VIDEO.VideoId ) ON ITEM.PictureId=PICTURE.PictureId) ON CATEGORY.CategoryId=ITEM.CategoryId
WHERE CategoryName ='" + textBox1.Text.ToString() + "' ;";

but it does not work

can you advice me plz?

thanks in advance

 

[JamesMcS]

You can't use * with where clauses in a delete SQL statement. Try adding all the relevant fields in and see what happens.

 

[afnan_88]

i have the following query in the same application:

"DELETE ITEM.*,PICTURE.*,VIDEO.*,AUDIO.*
FROM ITEM,PICTURE,VIDEO,AUDIO
WHERE ITEM.PictureId=PICTURE.PictureId AND ITEM.VideoId=VIDEO.VideoId AND ITEM.AudioId=AUDIO.AudioId AND ItemName ='" + textBox2.Text.ToString() + "' ;";


and its working ,,

i'll try..''but ihave more than 10 fields ?'
can you give me more suggestions ?

 

[DCrake]

Not quite happy with this piece of code

+ textBox2.Text.ToString() +

Usually appears as

& me.Textbox2 &

 

[JamesMcS]

The tables are all joined up aren't they?

 

[JamesMcS]

I couldn't find a reference to ToString in the help file... that was going to be my next question

 

[DCrake]

The primary key from the host table must appear as the foreign key in the child tables. In your example if one table doesn't then the delete will fail.

Try using a function to do the delete

Public Function MassDelete(AnyID As Long)


DoCmd.SetWarnings False
'Table 1
DoCmd.RunSQL "Delete * From Table1 Where FK = " & AnyID
'Table 2
DoCmd.RunSQL "Delete * From Table2 Where FK = " & AnyID

Etc

DoCmd.SetWarnings True

End Function

The simply use the function in your form to perfrom the task

Call MassDelete(Me.ID)

Remember the names of tablrs and fields are for brevity only.

 

[afnan_88]

hello DCrake
im working throw a c sharp.net application this piece of code take the category name from the user i'm sure that its not causing any problems i use it many times before this

 

[DCrake]

So why are you asking questions on Access then?

 

[afnan_88]

JamesMcS

thay are all joined

I couldn't find a reference to ToString in the help file... that was going to be my next question

this part is + textBox2.Text.ToString() + "' ; working good i have a full database editor and its workig good

also i have other delete query in the same app and its work properly:

"DELETE ITEM.*,PICTURE.*,VIDEO.*,AUDIO.*
FROM ITEM,PICTURE,VIDEO,AUDIO
WHERE ITEM.PictureId=PICTURE.PictureId AND ITEM.VideoId=VIDEO.VideoId AND ITEM.AudioId=AUDIO.AudioId AND ItemName ='" + textBox2.Text.ToString() + "' ;";

 

[DCrake]

If you do

Debug.Print textBox2.Text.ToString()

in the code section what appears in the immediate window?

 

[afnan_88]

DCrake

my csharp application is linked to an access database..
alsoi think the problem in my query ..

 

[afnan_88]

DCrake
one of the category name appears

 

[DCrake]

DOn't know how csharp code works but if this was Access it would read

WHERE ITEM.PictureId=" & PICTURE.PictureId & " AND ITEM.VideoId=" & VIDEO.VideoId " & AND ITEM.AudioId=" & AUDIO.AudioId & " AND ItemName ='" + textBox2.Text.ToString() + "' ;";

I think it needs to parse out the values