SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Delete from database using JOIN or not how to
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mamor90
Starting Member

Sweden
5 Posts

Posted - 03/26/2013 :  10:37:40  Show Profile  Reply with Quote
i have 3 tables

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.
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?

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 03/26/2013 :  12:07:56  Show Profile  Reply with Quote
you can always write 3 different commands and call them sequentially; according to the referential integrity you have.

--------------------------
Joins are what RDBMS's do for a living
Go to Top of Page

mamor90
Starting Member

Sweden
5 Posts

Posted - 03/26/2013 :  15:35:25  Show Profile  Reply with Quote
quote:
Originally posted by xhostx

you can always write 3 different commands and call them sequentially; according to the referential integrity you have.

--------------------------
Joins are what RDBMS's do for a living



I think know what u mean but i dont know how to do it , because i am new in sql development
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000