Delete from 3 tables only get it to work to delete from one table

Question

0.00/5 (No votes)

See more:

i have 3 tables

SQL

EMPLOYEES_TECHNICAL_SKILLS
technical_skill_id(PK/FK)
employee_id(PK/FK)
technical_skill_level_id

TECHNICAL_SKILL_LEVEL
technical_skill_level_id (PK)
skill_level

TECHNICAL_SKILLS
technical_skill_id (PK)
skill_name
skill_type

i am trying to delete all data from the tables
But i only get it to work to delete EMPLOYEES_TECHNICAL_SKILLS Columns
and in the other tables i still have data

I have trying to do it without any JOIN like this.

C#

using (knowitCVdbEntities db = new knowitCVdbEntities())
{
SPWeb theSite = SPControl.GetContextWeb(Context);
SPUser theUser = theSite.CurrentUser;
string strUserName = theUser.LoginName;

var theEmplDatabaseRem = (
from p
in db.EMPLOYEES
where p.username == strUserName
select p).FirstOrDefault();

_emp = theEmplDatabaseRem;

if (_emp != null)
{
LabelPleaseSelectDbListBox.Visible = false;

if (ListBoxDataBase.SelectedItem != null)
{


string vItem = ListBoxDataBase.SelectedItem.Value;
string valueListBox = vItem;
int indexOf = valueListBox.IndexOf("-", StringComparison.Ordinal);
string valueDatabase = valueListBox.Substring(0, indexOf - 1);

EMPLOYEES_TECHNICAL_SKILLS dbRemove = (from p
in db.EMPLOYEES_TECHNICAL_SKILLS
where p.employee_id == _emp.employee_id && p.TECHNICAL_SKILLS.skill_name == valueDatabase
select p).FirstOrDefault();

if (dbRemove != null)
{
_emp.EMPLOYEES_TECHNICAL_SKILLS.Remove(dbRemove);
db.SaveChanges();
ListBoxDataBase.Items.Remove(ListBoxDataBase.SelectedItem);
}

{
LabelPleaseSelectDbListBox.Visible = true;
LabelPleaseSelectDbListBox.Text = "You didn't choose anything";
}

}
// and this result in that i only delete from the first table not the others.

and i have also tried with JOIN like this.
var dbRemove = (from p in db.EMPLOYEES_TECHNICAL_SKILLS.AsEnumerable()
join m in db.TECHNICAL_SKILLS.AsEnumerable() on p.technical_skill_id equals
m.technical_skill_id
join l in db.TECHNICAL_SKILL_LEVEL.AsEnumerable() on p.technical_skill_level_id
equals l.technical_skill_level_id
where p.employee_id == _emp.employee_id && m.skill_name == valueDatabase
select p).FirstOrDefault();

and have still not got it to work , what could i have missed?

Posted 26-Mar-13 9:31am

Kurac1

Updated 26-Mar-13 18:15pm

Shanalal Kasim

v3

Add a Solution

Comments

Maciej Los 26-Mar-13 15:43pm

We need to know complete structure of your tables, not only data types.
Have you seen this: http://msdn.microsoft.com/en-us/library/ms188066.aspx[^], section: ON DELETE -> CASCADE?

Kurac1 26-Mar-13 15:56pm

-- Creating table 'EMPLOYEES_TECHNICAL_SKILLS'
CREATE TABLE [dbo].[EMPLOYEES_TECHNICAL_SKILLS] (
[technical_skill_id] int NOT NULL,
[employee_id] int NOT NULL,
[technical_skill_level_id] int NOT NULL
);
GO

-- Creating table 'TECHNICAL_SKILL_LEVEL'
CREATE TABLE [dbo].[TECHNICAL_SKILL_LEVEL] (
[technical_skill_level_id] int IDENTITY(1,1) NOT NULL,
[skill_level] varchar(50) NOT NULL
);
GO

-- Creating table 'TECHNICAL_SKILLS'
CREATE TABLE [dbo].[TECHNICAL_SKILLS] (
[technical_skill_id] int IDENTITY(1,1) NOT NULL,
[skill_name] varchar(50) NOT NULL,
[skill_type] varchar(50) NOT NULL
);
GO

Creating foreign key on [technical_skill_level_id] in table 'EMPLOYEES_TECHNICAL_SKILLS'
ALTER TABLE [dbo].[EMPLOYEES_TECHNICAL_SKILLS]
ADD CONSTRAINT [FK_EMPLOYEES_TECHNICAL_SKILLS_TECHNICAL_SKILL_LEVEL1]
FOREIGN KEY ([technical_skill_level_id])
REFERENCES [dbo].[TECHNICAL_SKILL_LEVEL]
([technical_skill_level_id])
ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EMPLOYEES_TECHNICAL_SKILLS_TECHNICAL_SKILL_LEVEL1'
CREATE INDEX [IX_FK_EMPLOYEES_TECHNICAL_SKILLS_TECHNICAL_SKILL_LEVEL1]
ON [dbo].[EMPLOYEES_TECHNICAL_SKILLS]
([technical_skill_level_id]);
GO

-- Creating foreign key on [technical_skill_id] in table 'EMPLOYEES_TECHNICAL_SKILLS'
ALTER TABLE [dbo].[EMPLOYEES_TECHNICAL_SKILLS]
ADD CONSTRAINT [FK_EMPLOYEES_TECHNICAL_SKILLS_TECHNICAL_SKILLS1]
FOREIGN KEY ([technical_skill_id])
REFERENCES [dbo].[TECHNICAL_SKILLS]
([technical_skill_id])
ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

do u mean this ?

Maciej Los 26-Mar-13 16:16pm

Exactly!

YAIR-I 26-Mar-13 16:21pm

First, you have foreign keys and it means that you can not delete from the 2 tables if you use there value in any other rows in the main table.
Second, from your code it looks like you are deleting only from one table. where is the code to delete from the other 2.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-03-26T10:27:00

As i wrote in my comment, to enable cascade deleting, you need to change a definition of table.

SQL

ALTER TABLE [dbo].USER_PHONE  WITH CHECK ADD
CONSTRAINT [FK_Temp_UsersPhone_Users] FOREIGN KEY([USR_ID])
REFERENCES [dbo].[Users] ([USR_ID])
ON DELETE CASCADE
GO

Quote:
ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

Specifies what action happens to rows in the table that is altered, if those rows have a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.

NO ACTION

The SQL Server Database Engine raises an error and the delete action on the row in the parent table is rolled back.
CASCADE

Corresponding rows are deleted from the referencing table if that row is deleted from the parent table.

See this: http://msdn.microsoft.com/en-us/library/ms188066.aspx[^].

CASCADE DELETE OPTION[^]
Cascading deletes in SQL Server[^]