i want to delete from 5 tables with inner join like that:
the user will Enter category name in text box iwant to delete Category+Item+picture+audio+video
help me please :)
thi sis tables and it's colume
each category contain item
each item has picture ,video ,audio
user enter category name
by writte delete query
category and item delete
but picture ,video ,audio still in data base
iwrite that code
string query10 = "Select ItemId.ITEM inner join category on ITEM.CategoryId=CATEGORY.CategoryId && CATEGORY.CategoryName='" + textBox1.Text + "'";
OleDbCommand aCommand10 = new OleDbCommand(query10, conn);
OleDbDataReader drr = aCommand10.ExecuteReader();
int y = drr.GetInt32(0); string query1 = "DELETE * FROM PICTURE WHERE PicturId='" + y + "' ";
OleDbCommand aCommand50 = new OleDbCommand(query1, conn);
string query2 = "DELETE * FROM VIDEO WHERE VideoId='" + y + "' ";
OleDbCommand aCommand52 = new OleDbCommand(query2, conn);
string query3 = "DELETE * FROM PICTURE WHERE AudioId='" + y+ "' ";
OleDbCommand aCommand52 = new OleDbCommand(query3, conn);
string DeleteCategoryCommand = "DELETE FROM CATEGORY WHERE CategoryName ='" + textBox1.Text.ToString() + "' ;";
Ok, firstly, please stop writing help help help all the time. It makes me feel pressured. I'm already here, helping you when I can, I also have my own things to do you know ;)
Ok, your code itself looks fine, so I imagine you're getting errors from your foreign key constraints.
So let's go back to what I said earlier. You need to make your foreign keys propagate the delete throughout the database. Unfortunately, I don't use MS Access as a database, but you should have the option to set what happens "OnDelete". At which point you need to make it "Cascade".
Other than that you need to goto the top of the chain (the table that references other tables, but has no tables referencing to it) and start deleting from there first.
What is happening is, you're trying to delete a row from a table, but other tables are referencing that row. Therefore, you aren't allowed to delete them. You must delete whatever is referencing that row first BEFORE you can delete that row specifically. This is the Foreign Key Constraint
there are no large different between sql and access.
The SQL isn't, no. But the UI is and I don't know how to use the Access UI ;)
In the string in your comment you have the table and the property backwards. It should be "SELECT ITEM.ItemId INNER JOIN category ON ITEM.CategoryId = CATEGORY.CategoryId WHERE CATEGORY.CategoryName='myCategory'" =)
I know you have foreign keys, I'm saying that's what is stopping you from deleting.
Hm, no. When you create your foreign key you can specify an action to perform when a row gets deleted. Commonly it's either "NO ACTION" or "CASCADE"
I think that yours must be set to "RESTRICT"/"NO ACTION"
Ok i got your problem and the solution is as follow.
create your table that is Item table
Itemid is Primary key and pictureId,VideoId,AudioId,CategoryId make all this column as
and after that create your other table with the name Category,Picture,Video, and audio.
and alll this table make it's respected id as forigen key constriant with cascade/Delete
of Item table.
then fire a query on item table
Delete from Item Where ItemId='xyz'
when query fire it will also delete all the item from the respected table so no need to passing
query with innser join