删除SQL中的记录时出现问题 [英] Issue in deleting record in SQL

问题描述

我在从表中删除记录时遇到问题。我有[BusRoute]和[SchoolBusRoute]两个表，两个表中的相应字段是



[BusRoute]



1）BusRouteID

2）RouteNo

3）TflUrl

4）详情路线

）活跃

6）DateChanged

7）ChangedByID



[SchoolBusRoute]



1）SchoolBusRouteID

2）SchoolID

3）BusRouteID

4）DateChanged

5）ChangedByID



表[SchoolBusRoute]中的字段BusRouteID id依赖于表[BusRoute]中的字段BusRouteID。特定字段是表[BusRoute]中的主键，因此不会有重复记录。但是我们在表[SchoolBusRoute]中为不同的schoolID获得了[BusRouteID]字段的重复记录。因此，当我尝试删除表[BusRoute]中的记录时，它与表[SchoolBusRoute]冲突，尽管我已经删除了表[SchoolBusRoute]中特定学校的相应记录，因为它仍然具有相同的BusRouteID对抗其他学校ID。





当我尝试在visual studio中使用C＃编码执行相同的操作时仍然无法删除它显示一个异常为'无法将值NULL插入列'BusRouteID'，表'TflStars.dbo.SchoolBusRoute'; column不为null允许空值。 UPDATE失败'



我用来删除表中记录的代码是

I am facing an issue in deleting record from a table. I have got two table as [BusRoute] and [SchoolBusRoute] and the respective fields in both the tables are

[BusRoute]

1) BusRouteID
2) RouteNo
3) TflUrl
4) DetailsofRoute
5) Active
6) DateChanged
7) ChangedByID

[SchoolBusRoute]

1) SchoolBusRouteID
2) SchoolID
3) BusRouteID
4) DateChanged
5) ChangedByID

Where the field BusRouteID in the table [SchoolBusRoute] id dependent on the field BusRouteID in the table [BusRoute]. The particular field is a primary key in the table [BusRoute] and so there will be no duplicate records. But we have got duplicate records of the field [BusRouteID] for different schoolID in the table [SchoolBusRoute]. Hence because of this when I try to delete a record in the table [BusRoute] it is conflicting with the table [SchoolBusRoute] although I have deleted the corresponding record against the particular school in the table [SchoolBusRoute] as it still have the same BusRouteID against other schoolID.


When I try to perform the same operation using C# coding in visual studio and still I am unable to delete as it displays an exception as 'Cannot insert the value NULL into column 'BusRouteID', table 'TflStars.dbo.SchoolBusRoute'; column does not null allow nulls. UPDATE fails'

The code I used to delete the record from both the table is

else if (e.CommandName == "DeleteRecord")
{
    //Delete and raise event to rebibnd
    SchoolBusRoute thisSchoolBusRoute = selectedBusRoute.SchoolBusRoutes.Where(p => p.BusRouteID == selectedBusRoute.BusRouteID).FirstOrDefault();

    if (thisSchoolBusRoute != null)
    {
        schoolBusRoutesService.Delete(thisSchoolBusRoute);
        BusRoutesService.Delete(selectedBusRoute);

        if (OnBusRouteStatusChanged != null)
        {
            EventArgs newArgs = new EventArgs();
            OnBusRouteStatusChanged(this, newArgs);
        }
    }
}

public void Delete(SchoolBusRoute DataEntity)
{
    SchoolBusRoute entityInstance = currentContext.SchoolBusRoutes.Where(p => p.SchoolBusRouteID == DataEntity.SchoolBusRouteID).FirstOrDefault();

    if (entityInstance != null)
    {
        currentContext.DeleteObject(entityInstance);
        currentContext.SaveChanges();
    }
}

public void Delete(BusRoute DataEntity)
{
    BusRoute entityInstance = currentContext.BusRoutes.Where(p => p.BusRouteID == DataEntity.BusRouteID).FirstOrDefault();

	if (entityInstance != null)
    {
        currentContext.DeleteObject(entityInstance);
        currentContext.SaveChanges();
    }
}

我在声明中得到例外



currentContext 。保存更改（）;

函数'Public void Delete（BusRoute DataEntity）'



任何人都可以帮我提供删除记录的解决方案在表[BusRoute]中没有与表[SchoolBusRoute]冲突。

I am getting the exception in the statement

currentContext.SaveChanges();
that is in the function 'Public void Delete(BusRoute DataEntity)'

Can anyone help me to provide a solution to delete a record in the table [BusRoute] without conflicting with the table [SchoolBusRoute].

推荐答案

你有三个选择：

You have three options:

1. 您只允许在任何SchoolBusRoutes未使用的情况下删除BusRoute。
2. 您始终会删除使用该BusRoute的所有SchoolBusRoute。
3. 您断开连接BusRoute的SchoolBusRoute设置为另一个BusRouteId或NULL。

否则外键将允许您删除BusRoute。

Otherwise the foreign key will nto allow you to delete the BusRoute.

这篇关于删除SQL中的记录时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！