Issue in deleting record in SQL

See more:

ASP.NET

SQL

C#3.5

, +

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();
    }
}

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