1

I want to turn on IDENTITY_INSERT before inserting data into the database. But when I run my code it throws an error

"Cannot find the object "UniversityDB.StudentRegistrationModels" because it does not exist or you do not have permissions"

My control code is:

public ActionResult Create([Bind(Include = "StudentId,CourseId,IsPaymentComplete")] StudentRegistrationModels studentRegistration)
{
    using (var db = new UniversityDBContext())
    {
        db.StudentRegistration.Add(studentRegistration);
        db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT UniversityDB.StudentRegistrationModels ON;");
        db.SaveChanges();
        db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT UniversityDB.StudentRegistrationModels OFF");
    }
}

and database model is:

public class StudentRegistrationModels
{
    [Key, ForeignKey("Student"), Required]
    public int StudentId { get; set; }
    [ForeignKey("Course"), Required]
    public int CourseId { get; set; }
    public DateTime EnrollDate { get; set; }
    public bool IsPaymentComplete { get; set; }
    public virtual StudentModel Student { get; set; }
    public virtual CourseModels Course { get; set; }
    
}

Please help me to fix it

Charlieface
  • 52,284
  • 6
  • 19
  • 43
mnt
  • 11
  • 1
  • 1
    `StudentId` shouldn't be an identity column anyway since it's also a foreign key. But the exception tells that the database isn't even found (i.e. not accessible). – Gert Arnold Feb 21 '22 at 19:57
  • You don't specify a schema so if this is SQL Server for instance it would default to "dbo". Try `SET IDENTITY_INSERT UniversityDB.dbo.StudentRegistrationModels ON` assuming your database name is UniversityDB. – Steve Py Feb 21 '22 at 20:29
  • isnt the default schema dbo? hence it should be `dbo.StudentRegistrationModels` – Jerdine Sabio Feb 21 '22 at 22:45
  • Does this answer your question? [SQL dot notation](https://stackoverflow.com/questions/21419127/sql-dot-notation) – Charlieface Feb 22 '22 at 00:32

1 Answers1

0

If something wrong in turn on/off IDENTITY_INSERT, The error message will be: Cannot insert explicit value for identity column in table 'xxxxxxx' when IDENTITY_INSERT is set to OFF.

But your error message is not that. So I presume that UniversityDB.StudentRegistrationModels doesn't map correctly to a table in your database.

You can try to modify your code in the following format:

SET IDENTITY_INSERT [ [ database_name . ] schema_name . ] table_name { ON | OFF }

database_name Is the name of the database in which the specified table resides.

schema_name Is the name of the schema to which the table belongs.

table_name Is the name of a table with an identity column.

Xinran Shen
  • 8,416
  • 2
  • 3
  • 12