I am trying to assign a value to a foreign key in an application that I am working on. However, whenever I select a value in the dropdown and click submit, the following SQL error appears:
SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Printer_Printer_Model_ModelID". The conflict occurred in database
"_360_Office_ManagementContext-577009b7-1da0-4f2f-8dbc-3196d6e7a3d9",
table "dbo.Printer_Model", column 'ID'.
The statement has been terminated.
The database table looks like this. The foreign keys are supposed to reference the ID field of their respective tables:
CREATE TABLE [dbo].[Printer] (
[ID] INT IDENTITY (1, 1) NOT NULL,
[MachineID] NVARCHAR (50) NULL,
[Log_ID] INT NOT NULL,
[Lease_ID] INT NOT NULL,
[Serial_Number] NVARCHAR (50) NULL,
[Solution] NVARCHAR (10) NULL,
[Additional_Info] NVARCHAR (50) NULL,
[InstallationDate] DATETIME2 (7) NOT NULL,
[Mono_Click] REAL NOT NULL,
[Colour_Click] REAL NOT NULL,
[Minimum] INT NOT NULL,
[Parts_Warranty] INT NOT NULL,
[IT_Support] INT NOT NULL,
[Contract_Type] NVARCHAR (50) NULL,
[SiteID] INT NULL,
[ModelID] INT NULL,
[Previous_AddressID] INT NULL,
CONSTRAINT [PK_Printer] PRIMARY KEY CLUSTERED ([ID] ASC),
CONSTRAINT [FK_Printer_Printer_Model_ModelID] FOREIGN KEY ([ModelID]) REFERENCES [dbo].[Printer_Model] ([ID]),
CONSTRAINT [FK_Printer_Address_Previous_AddressID] FOREIGN KEY ([Previous_AddressID]) REFERENCES [dbo].[Address] ([ID]),
CONSTRAINT [FK_Printer_Company_Site_SiteID] FOREIGN KEY ([SiteID]) REFERENCES [dbo].[Company_Site] ([ID])
);
GO
CREATE NONCLUSTERED INDEX [IX_Printer_ModelID]
ON [dbo].[Printer]([ModelID] ASC);
GO
CREATE NONCLUSTERED INDEX [IX_Printer_SiteID]
ON [dbo].[Printer]([SiteID] ASC);
GO
CREATE NONCLUSTERED INDEX [IX_Printer_Previous_AddressID]
ON [dbo].[Printer]([Previous_AddressID] ASC);
The method that assigns the dropdown values is as follows:
public SelectList AddressSelectList { get; set; }
public SelectList ModelSelectList { get; set; }
public SelectList SiteSelectList { get; set; }
public void PopulateAddressDropDownList(_360_Office_ManagementContext _context,
object selectedAddress = null)
{
var addressQuery = from d in _context.Address
orderby d.Address_1
select d;
AddressSelectList = new SelectList(addressQuery.AsNoTracking(),
"ID", "Address_1", selectedAddress);
}
public void PopulateModelDropDownList(_360_Office_ManagementContext _context,
object selectedModel = null)
{
var siteQuery = from d in _context.Printer_Model
orderby d.Model
select d;
ModelSelectList = new SelectList(siteQuery.AsNoTracking(),
"ID", "Model", selectedModel);
}
public void PopulateSiteDropDownList(_360_Office_ManagementContext _context,
object selectedSite = null)
{
var siteQuery = from d in _context.Company_Site
orderby d.Department
select d;
ModelSelectList = new SelectList(siteQuery.AsNoTracking(),
"ID", "Department", selectedSite);
}
public IActionResult OnGet()
{
PopulateAddressDropDownList(_context);
PopulateSiteDropDownList(_context);
PopulateModelDropDownList(_context);
return Page();
}
And the HTML that displays the dropdown is as follows:
<div class="form-group">
<label asp-for="Printer.Previous_Address" class="control-label"></label>
<select asp-for="Printer.Previous_AddressID" name="PrevAddressID" class="form-control" asp-items="Model.AddressSelectList" ><option value=""> Select Previous Address</option></select>
<span asp-validation-for="Printer.Previous_AddressID" class="text-danger"></span>
</div>
Any help in resolving this issue so that the relationships show up in the database would be greatly appreciated.
EDIT: There appears to be 2 separate errors at play, an SQL exception and a DBUpdateException. I have included both of the sets of raw exception details below. The code that seems to be causing the issue is in some of the base libraries which I haven't touched at all so its confusing me a bit.
SQLException details:
Microsoft.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Customer_Address_AddressID". The conflict occurred in database "_360_Office_ManagementContext-577009b7-1da0-4f2f-8dbc-3196d6e7a3d9", table "dbo.Address", column 'ID'.
The statement has been terminated.
at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__164_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
ClientConnectionId:10f5732e-a540-4add-af22-746954b3f6f8
Error Number:547,State:0,Class:16
DbUpdateException details:
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
---> Microsoft.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Customer_Address_AddressID". The conflict occurred in database "_360_Office_ManagementContext-577009b7-1da0-4f2f-8dbc-3196d6e7a3d9", table "dbo.Address", column 'ID'.
The statement has been terminated.
at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__164_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
ClientConnectionId:10f5732e-a540-4add-af22-746954b3f6f8
Error Number:547,State:0,Class:16
--- End of inner exception stack trace ---
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
at _360_Office_Management.Pages.AccountsManagement.Customers.CreateModel.OnPostAsync() in C:\Users\Samuel\Desktop\Uni\3YP\360 Office Management\360 Office Management\Pages\AccountsManagement\Customers\Create.cshtml.cs:line 42
at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.ExecutorFactory.GenericTaskHandlerMethod.Convert[T](Object taskAsObject)
at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.ExecutorFactory.GenericTaskHandlerMethod.Execute(Object receiver, Object[] arguments)
at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeHandlerMethodAsync()
at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeNextPageFilterAsync()
at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.Rethrow(PageHandlerExecutedContext context)
at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeInnerFilterAsync()
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
Edit 2 - I have found the method that is causing the issue and have included it below. This method is the standard creation code so I am still none the wiser as to why it is causing the issue assigning Foreign Keys.
public async Task<IActionResult> OnPostAsync()
{
if (!ModelState.IsValid)
{
return Page();
}
_context.Customer.Add(Customer);
await _context.SaveChangesAsync();
return RedirectToPage("./Index");
}
User contributions licensed under CC BY-SA 3.0