Here I am able to bind the multi-select select list. And when I attempt to save I do not get an error but it does not save the officer information.
And here is the error while trying to load the page
An unhandled exception occurred while processing the request. SqlException: Invalid column name 'OfficerID'. Microsoft.Data.SqlClient.SqlCommand+<>c.b__164_0(Task result)
Stack Query Cookies Headers Routing SqlException: Invalid column name 'OfficerID'. Microsoft.Data.SqlClient.SqlCommand+<>c.b__164_0(Task result) System.Threading.Tasks.ContinuationResultTaskFromResultTask.InnerInvoke() System.Threading.Tasks.Task+<>c.<.cctor>b__274_0(object obj) System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, object state) System.Threading.Tasks.Task.ExecuteWithThreadLocal(ref Task currentTaskSlot, Thread threadPoolThread) Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable+AsyncEnumerator.InitializeReaderAsync(DbContext _, bool result, CancellationToken cancellationToken) Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync(TState state, Func> operation, Func>> verifySucceeded, CancellationToken cancellationToken) Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable+AsyncEnumerator.MoveNextAsync() Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync(IQueryable source, CancellationToken cancellationToken) Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync(IQueryable source, CancellationToken cancellationToken) SecurityCore.PaginatedList.CreateAsync(IQueryable source, int pageIndex, int pageSize) in PaginatedList.cs + var items = await source.Skip( SecurityCore.Pages.SecurityLogs.IndexModel.OnGetAsync(string sortOrder, string currentFilter, string searchString, Nullable pageIndex, string entitySelect) in Index.cshtml.cs + SecurityLog = await PaginatedList.CreateAsync(sort Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.ExecutorFactory+NonGenericTaskHandlerMethod.Execute(object receiver, object[] arguments) Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeHandlerMethodAsync() Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeNextPageFilterAsync() Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.Rethrow(PageHandlerExecutedContext context) Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted) Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeInnerFilterAsync() Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted) Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context) Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted) Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted) Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Logged|17_1(ResourceInvoker invoker) Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger) Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context) Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
Here is my code...
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.AspNetCore.Mvc.Rendering;
using Microsoft.EntityFrameworkCore;
using SecurityCore.Models;
using System.ComponentModel.DataAnnotations;
namespace SecurityCore.Pages.SecurityLogs
{
public class IndexModel : PageModel
{
private readonly SecurityCore.Models.SecurityCoreContext _context;
public IndexModel(SecurityCore.Models.SecurityCoreContext context)
{
_context = context;
}
public string EventDateSort { get; set; }
public string EventStartSort { get; set; }
public string EventEndSort { get; set; }
public string ContactNameSort { get; set; }
public string EventTypeSort { get; set; }
public string ShiftRangeSort { get; set; }
public string EntitySort { get; set; }
public string LocationSort { get; set; }
public string NarrativeSort { get; set; }
public string OfficerNameSort { get; set; }
public string FullNameSort { get; set; }
public string SubjectDOBSort { get; set; }
public string RecordLockedSort { get; set; }
public string CurrentFilter { get; set; }
public string CurrentSort { get; set; }
public string IDSort { get; set; }
public Nullable<DateTime> dateEnd { get; set; }
public Nullable<DateTime> dateBegin { get; set; }
[TempData]
public string Message { get; set; }
public bool ShowMessage => !string.IsNullOrEmpty(Message);
public PaginatedList<SecurityLog> SecurityLog { get; set; }
public async Task OnGetAsync(string sortOrder, string currentFilter, string searchString, int? pageIndex, string entitySelect)
{
ViewData["EntityID"] = new SelectList(_context.Entity.Where(a => a.Active == "Y"), "ID", "Name");
CurrentSort = sortOrder;
IDSort = sortOrder == "ID" ? "ID_Desc" : "ID";
EventDateSort = sortOrder == "EventDate" ? "EventDate_Desc" : "EventDate";
ContactNameSort = sortOrder == "ContactName" ? "ContactName_Desc" : "ContactName";
EventTypeSort = sortOrder == "EventType" ? "EventType_Desc" : "EventType";
ShiftRangeSort = sortOrder == "ShiftRange" ? "ShiftRange_Desc" : "ShiftRange";
EntitySort = sortOrder == "Entity" ? "Entity_Desc" : "Entity";
LocationSort = sortOrder == "Location" ? "Location_Desc" : "Location";
NarrativeSort = sortOrder == "Narrative" ? "Narrative_Desc" : "Narrative";
FullNameSort = sortOrder == "FullName" ? "FullName_Desc" : "FullName";
RecordLockedSort = sortOrder == "Locked" ? "NotLocked" : "Locked";
OfficerNameSort = sortOrder == "OfficerName" ? "OfficerName_Desc" : "OfficerName";
if (searchString != null)
{
pageIndex = 1;
}
else
{
searchString = currentFilter;
}
CurrentFilter = searchString;
IQueryable<SecurityCore.Models.SecurityLog> sort = from s in _context.SecurityLog select s;
if (!String.IsNullOrEmpty(searchString))
{
sort = sort.Where(s => s.Narrative.Contains(searchString)
|| s.RecordLocked.Contains(searchString)
|| s.EventDate.ToString().Contains(searchString)
|| s.ContactName.Contains(searchString)
|| s.Entity.Name.Contains(searchString)
|| s.Location.Name.Contains(searchString)
|| s.EventType.Name.Contains(searchString)
|| s.ShiftRange.Name.Contains(searchString)
|| s.ID.ToString().Contains(searchString)
|| s.SubjectFirst.Contains(searchString)
|| s.SubjectLast.Contains(searchString)
|| s.Officer.FullName.Contains(searchString)
);
}
switch (sortOrder)
{
case "ID_Desc":
sort = sort.OrderByDescending(s => s.ID);
break;
case "ID":
sort = sort.OrderBy(s => s.ID);
break;
case "EventDate":
sort = sort.OrderBy(s => s.EventDate);
break;
case "ContactName":
sort = sort.OrderBy(s => s.ContactName).ThenBy(s => s.EventDate);
break;
case "ContactName_Desc":
sort = sort.OrderByDescending(s => s.ContactName).ThenBy(s => s.EventDate);
break;
case "ShiftRange":
sort = sort.OrderBy(s => s.ShiftRange.Name).ThenBy(s => s.EventDate);
break;
case "ShiftRange_Desc":
sort = sort.OrderByDescending(s => s.ShiftRange.Name).ThenBy(s => s.EventDate);
break;
case "EventType":
sort = sort.OrderBy(s => s.EventType.Name).ThenBy(s => s.EventDate);
break;
case "EventType_Desc":
sort = sort.OrderByDescending(s => s.EventType.Name).ThenBy(s => s.EventDate);
break;
case "Entity":
sort = sort.OrderBy(s => s.Entity.Name).ThenBy(s => s.EventDate);
break;
case "Entity_Desc":
sort = sort.OrderByDescending(s => s.Entity.Name).ThenBy(s => s.EventDate);
break;
case "Location":
sort = sort.OrderBy(s => s.Location.Name).ThenBy(s => s.EventDate);
break;
case "Location_Desc":
sort = sort.OrderByDescending(s => s.Location.Name).ThenBy(s => s.EventDate);
break;
case "Narrative":
sort = sort.OrderBy(s => s.Narrative).ThenBy(s => s.EventDate);
break;
case "Narrative_Desc":
sort = sort.OrderByDescending(s => s.Narrative).ThenBy(s => s.EventDate);
break;
case "NotLocked":
sort = sort.OrderBy(s => s.RecordLocked).ThenBy(s => s.EventDate);
break;
case "Locked":
sort = sort.OrderByDescending(s => s.RecordLocked).ThenBy(s => s.EventDate);
break;
case "OfficerName":
sort = sort.OrderBy(s => s.Officer.FullName).ThenBy(s => s.EventDate);
break;
case "OfficerName_Desc":
sort = sort.OrderByDescending(s => s.Officer.FullName).ThenBy(s => s.EventDate);
break;
default:
sort = sort.OrderByDescending(s => s.EventDate);
break;
}
int pageSize = 12;
SecurityLog = await PaginatedList<SecurityLog>.CreateAsync(sort
.Include(a => a.Entity)
.Include(b => b.EventType)
.Include(c => c.Location)
.Include(d => d.ShiftRange)
.Include(e => e.Officer)
.Include(f => f.SecurityLogOfficer)
.AsNoTracking(), pageIndex ?? 1, pageSize);
}
}
}
//The Security Log Model
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel;
using ExpressiveAnnotations.Attributes;
namespace SecurityCore.Models
{
public class SecurityLog
{
public int ID { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM/dd/yyyy}")]
[Required]
[Display(Name = "Event Date")]
public System.DateTime EventDate { get; set; }
[Required]
[Display(Name = "Shift Range")]
//[ForeignKey("ShiftRange")]
public Nullable<int> ShiftRangeID { get; set; }
[Required]
//[ForeignKey("EventType")]
[Display(Name = "Event Type")]
public Nullable<int> EventTypeID { get; set; }
[Required]
[Display(Name = "Event Start")]
[DataType(DataType.DateTime)]
[DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM/dd/yyyy hh:mm}")]
public System.DateTime EventStart { get; set; }
[Required]
[Display(Name = "Event End")]
[DataType(DataType.DateTime)]
[DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM/dd/yyyy hh:mm}")]
public System.DateTime EventEnd { get; set; }
[Required]
[Display(Name = "Reporting Person")]
public string ContactName { get; set; }
[Required]
//[ForeignKey("Entity")]
[Display(Name = "Entity")]
public Nullable<int> EntityID { get; set; }
[Required]
//[ForeignKey("Location")]
[Display(Name = "Location")]
public Nullable<int> LocationID { get; set; }
[Required]
public string Narrative { get; set; }
//Set length of narrative displayed
private int NarrativeLimit = 200;
[Display(Name = "Narrative")]
public string NarrativeTrimmed
{
get
{
if (Narrative.Length > this.NarrativeLimit)
return this.Narrative.Substring(0, this.NarrativeLimit) + "...";
else
return this.Narrative;
}
}
[Required]
[Display(Name = "Subject's First Name")]
public string SubjectFirst { get; set; }
[Required]
[Display(Name = "Subject's Last Name")]
public string SubjectLast { get; set; }
[Display(Name = "Subject's Name")]
public string FullName
{
get
{
return SubjectFirst + " " + SubjectLast;
}
}
[Display(Name = "Subject's B#/DOB")]
public string SubjectDOB { get; set; }
private string _RecordLocked;
[Display(Name = "Record Locked?")]
public string RecordLocked
{
get { return _RecordLocked; }
set
{
_RecordLocked = value;
if (_RecordLocked == "Y")
{
try {
}
catch(Exception ex)
{
}
}
}
}
[Display(Name = "Entered By")]
public string EnteredBy { get; set; }
[Display(Name = "Create Date")]
[ReadOnly(true)]
[DataType(DataType.Date)]
public Nullable<System.DateTime> CreateDate { get; set; }
[Display(Name = "Modified Date")]
[DataType(DataType.DateTime)]
public Nullable<System.DateTime> ModifiedDate { get; set; }
[Display(Name = "Modified By")]
public string ModifiedBy { get; set; }
[Display(Name ="Number of Extinguishers")]
public Nullable<int> ExtinguisherNo { get; set; }
[Display(Name ="Total Blankets")]
public Nullable<int> BlanketNo { get; set; }
[Display(Name ="Cause of Alarm")]
public string FireAlarmCause { get; set; }
[Display(Name ="Doors Closed in Area?")]
public string DoorsClosed { get; set; }
[Display(Name ="Number of Staff Responding")]
public Nullable<int> StaffNo { get; set; }
public virtual Entity Entity { get; set; }
public virtual ShiftRange ShiftRange { get; set; }
public virtual EventType EventType { get; set; }
public virtual Location Location { get; set; }
public virtual Officer Officer { get; set; }
public virtual SecurityLogOfficer SecurityLogOfficer { get; set; }
}
}
//The Officer Model
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;
namespace SecurityCore.Models
{
public class Officer
{
[Required]
public int ID { get; set; }
//[Required]
[Display(Name = "Officer's First Name")]
public string FirstName { get; set; }
//[Required]
[Display(Name = "Officer's Last Name")]
public string LastName { get; set; }
[Display(Name = "Officer's Name")]
public string FullName
{
get
{
return FirstName + " " + LastName;
}
}
[Required]
public string Active { get; set; }
public string LoginName { get; set; }
public virtual SecurityLogOfficer SecurityLogOfficer { get; set; }
}
}
//The SecurityLogOfficer Model
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;
namespace SecurityCore.Models
{
public class SecurityLogOfficer
{
[Required]
public int ID { get; set; }
[Required]
public int SecurityLogID { get; set; }
public int OfficerID { get; set; }
}
}
//The Create Page that I am trying to post from…
using System;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.AspNetCore.Mvc.Rendering;
using SecurityCore.Models;
namespace SecurityCore.Pages.SecurityLogs
{
public class CreateModel : PageModel
{
private readonly SecurityCore.Models.SecurityCoreContext _context;
public CreateModel(SecurityCore.Models.SecurityCoreContext context)
{
_context = context;
}
public IActionResult OnGetAsync()
{
ViewData["EntityID"] = new SelectList(_context.Entity.Where(a=>a.Active == "Y"), "ID", "Name");
ViewData["ShiftRangeID"] = new SelectList(_context.ShiftRange.Where(a=>a.Active == "Y"), "ID", "Name");
ViewData["LocationID"] = new SelectList(_context.Location.Where(a=>a.Active == "Y"), "ID", "Name");
ViewData["EventTypeID"] = new SelectList(_context.EventType.Where(a=>a.Active == "Y"), "ID","Name");
ViewData["Officer"] = new SelectList(_context.Officer.Where(a => a.Active == "Y"), "ID", "FullName");
return Page();
}
[TempData]
public string Message { get; set; }
[BindProperty]
public SecurityLog SecurityLog { get; set; }
[BindProperty(SupportsGet = true)]
public int entity { get; set; }
public int eventType { get; set; }
SelectList FilteredLocation;
SelectList FilteredEventType;
public JsonResult OnGetLocations()
{
FilteredLocation = new SelectList(_context.Location.Where(c => c.EntityID == entity).Where(c =>c.Active == "Y").OrderBy(c =>c.Name), "ID", "Name");
return new JsonResult(FilteredLocation);
}
public JsonResult OnGetEventTypes()
{
FilteredEventType = new SelectList(_context.EventType.Where(c => c.EntityID == entity).Where(c => c.Active == "Y").OrderBy(c => c.Name), "ID", "Name");
return new JsonResult(FilteredEventType);
}
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see https://aka.ms/RazorPagesCRUD.
public async Task<IActionResult> OnPostAsync()
{
ModelState.Remove("EnteredBy");
ModelState.Remove("ModifiedDate");
ModelState.Remove("CreateDate");
ModelState.Remove("ModifiedBy");
ModelState.Remove("RecordLocked");
if (!ModelState.IsValid)
{
return Page();
}
SecurityLog.EnteredBy = User.Identity.Name;
SecurityLog.ModifiedDate = DateTime.Now;
SecurityLog.CreateDate = DateTime.Now;
SecurityLog.ModifiedBy = User.Identity.Name;
SecurityLog.RecordLocked = "N";
_context.SecurityLog.Add(SecurityLog);
await _context.SaveChangesAsync();
Message = "Entry added successfully!";
return RedirectToPage("Index");
}
}
}
And here is my database schema and sample results
Any assistance would be appreciated!
