locked
Error: invalid column name Departments.DepartmentID RRS feed

  • Question

  • User-973886032 posted

    hi guys

    I am getting the error below, and dont understand why, as the column DepartmentID does exist in the database, Here is my code

    Basically I have two related tables (1) Departments with PK DepartmentID and (2) Departments_Category_Registration with FK DepartmentID

    Controller

     [Route("Categories/{Categories}")]
            public async Task<IActionResult> Index(string Categories)
            {
     
                ViewData["Dept"] = "No Records Found!";
                Categories = Categories.Replace("_", " ");
     
    
                //CHECK FOR INTEGER
                try
                { Int32.Parse(Categories); }
                catch //(Exception err)
                { }
    
                if (Categories != null)
                {
    
    
    
    
                    if (Categories.Trim() != "")
                    {
                        Categories = Categories.ToLower().Trim();
                        // DATABASE 2 - Main records
                        var DataContext2 = _context.Departments_Category_Registration.Include(c => c.Departments)
                           .Where(r => r.Departments.Department_Name == Categories).Select(u => new Departments_Category_Registration
                           {
                               CategoryID = u.CategoryID,
                               Category_Name = u.Category_Name,
                               DepartmentID = u.DepartmentID,
                               Description = u.Description
    
    
    
                           });
                       
                        ViewData["Dept"] = Categories;
                        return View(await DataContext2.ToListAsync());
     
                    };
                };
                var DataContext = _context.Departments_Category_Registration.Include(c => c.Departments);
               return View(await DataContext.ToListAsync());
            }
    
    

    And models 

    1st department 

      [Key]
            public int CategoryID { get; set; }              // This is the PK
    
              [ForeignKey("DepartmentID")]
            public int DepartmentID { get; set; } // this is a FK
            public Xadosh.Models.Department.Departments Departments { get; set; }
            public string Category_Name { get; set; }
            public DateTime EntryDate { get; set; }
            public string Description { get; set; }
            public string Description_Detail { get; set; }
            public string Notes { get; set; }
            public string Reference { get; set; }
            public Guid UniqueId { get; set; }
            public bool IsEnabled { get; set; }

    and Departments_Category_Registration model

      [Key]
            public int DepartmentID { get; set; }
    	//[Display(Name ="Department_Name")]
    	[Required]
            [StringLength(150, ErrorMessage = "First name cannot be longer than 150 characters.")]
            public string Department_Name { get; set; }
    	//[Display(Name ="Department_Long")]
    	[Required]
            public string Description_Long { get; set; }
    	//[Display(Name ="Short Description_Short")]
    	[Required]
            [StringLength(150, ErrorMessage = "First name cannot be longer than 150 characters.")]
            public string Description_Short { get; set; }
            public bool IsEnabled { get; set; }
    
            [NotMapped]
            public List<Departments> departments { get; set; }

    and SQL DB

    CREATE TABLE [dbo].[Departments](
    	[DepartmentID] [int] IDENTITY(950123450,1) NOT NULL,
    	[Department_Name] [nvarchar](150) NOT NULL,
    	[Description_Long] [nvarchar](max) NULL,
    	[Description_short] [nvarchar](150) NULL,
    	[Department_UniqueID] [uniqueidentifier] NOT NULL,
    	[EntryDate] [datetime] NOT NULL,
    	[Notes] [nvarchar](500) NULL,
    	[IsEnabled] [bit] NOT NULL,
    
    
    
    CREATE TABLE [dbo].[Departments_Category_Registration](
    	[CategoryID] [int] IDENTITY(1250000,1) NOT NULL,
    	[DepartmentID] [int] NULL,
    	[Category_Name] [nvarchar](50) NULL,
    	[EntryDate] [datetime] NULL,
    	[Description] [nvarchar](200) NULL,
    	[Description_Detail] [nvarchar](max) NULL,
    	[Notes] [nvarchar](max) NULL,
    	[Reference] [nvarchar](50) NULL,
    	[UniqueId] [uniqueidentifier] NULL,
    	[IsEnabled] [bit] NOT NULL,

    Error message

    SqlException: Invalid column name 'DepartmentsDepartmentID'.
    
    
    /// -------------------------------------------------------
    +
                        return View(await DataContext2.ToListAsync());

    Monday, July 1, 2019 3:16 PM

Answers

  • User-854763662 posted

    Hi afrika ,

    It seems that there is a tiny error on using [ForeignKey] attribute , the ForeignKey attribute can be used in the following ways to configure the relationship correctly,

    1. On the foreign key property in the dependent class, passing in the name of the navigation property:

     public class Departments_Category_Registration
    {
        [Key]
        public int CategoryID { get; set; } 
    
        [ForeignKey("Departments")]
        public int DepartmentID { get; set; } 
        public Departments Departments { get; set; }   
    }

    2. On the navigation property in the dependent class, passing in the name of the foreign key property:

    public class Departments_Category_Registration
    {
        [Key]
        public int CategoryID { get; set; } 
    
        public int DepartmentID { get; set; } 
        [ForeignKey("DepartmentID ")]
        public Departments Departments { get; set; }   
    }

    Best Regards ,

    Sherry

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 2, 2019 6:56 AM

All replies

  • User475983607 posted

    The code has a lot of potential issues; empty catch block, odd ball projection query when an Include would work just fine, delimited input parameters...

    But more importunately, you have not provide what line of code is causing the error. 

    Monday, July 1, 2019 5:33 PM
  • User-973886032 posted

    i had edited some of the code and removed a few things, e.g the catch on the error handling, which basically checks to see if the query string is an int or char. It also replaces the spaces with and underscore

    The error line is 

     return View(await DataContext2.ToListAsync());

    • SqlException: Invalid column name 'DepartmentsDepartmentID'.

    Monday, July 1, 2019 9:06 PM
  • User-854763662 posted

    Hi afrika ,

    It seems that there is a tiny error on using [ForeignKey] attribute , the ForeignKey attribute can be used in the following ways to configure the relationship correctly,

    1. On the foreign key property in the dependent class, passing in the name of the navigation property:

     public class Departments_Category_Registration
    {
        [Key]
        public int CategoryID { get; set; } 
    
        [ForeignKey("Departments")]
        public int DepartmentID { get; set; } 
        public Departments Departments { get; set; }   
    }

    2. On the navigation property in the dependent class, passing in the name of the foreign key property:

    public class Departments_Category_Registration
    {
        [Key]
        public int CategoryID { get; set; } 
    
        public int DepartmentID { get; set; } 
        [ForeignKey("DepartmentID ")]
        public Departments Departments { get; set; }   
    }

    Best Regards ,

    Sherry

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 2, 2019 6:56 AM
  • User-973886032 posted

    Hi afrika ,

    It seems that there is a tiny error on using [ForeignKey] attribute , the ForeignKey attribute can be used in the following ways to configure the relationship correctly,

    2. On the navigation property in the dependent class, passing in the name of the foreign key property:

    public class Departments_Category_Registration
    {
        [Key]
        public int CategoryID { get; set; } 
    
        public int DepartmentID { get; set; } 
        [ForeignKey("DepartmentID ")]
        public Departments Departments { get; set; }   
    }

    Best Regards ,

    Sherry

    Number 1 did not work, but number 2 did

    thank you

    Tuesday, July 2, 2019 11:28 AM