Cannot insert the value NULL into column , column does not allow nulls. INSERT fails.

Hi There

I tried to insert a new record to JobSpMeter table in my project, when it runs unitofWork.saveChange(), I got an Exception:Cannot insert the value NULL into column 'Jobid', table 'AutoTrackDB.dbo.JobSPMeter'; column does not allow nulls. INSERT fails.

The real database (named JobSpMeter ) need to insert has following fields: Jobid(int, not null) EquipmentType(char(1), not null) MeterNumber(varchar(15), null) RegiserNumber(smallint, null) ThisReading(float, null)

I updated the associated Entity table in lightspeed model and set the identity column Name to Jobid and Identity Method to IdentityColumn, I also override the generedId method in model.cs file Like:

public partial class JobSpMeter : Entity { [Transient] private int _Jobid;

protected override object GeneratedId()
{

    return _Jobid;
}

}

The partial codes in my insert method is like this:

var newMeter = new JobSpMeter() {Id =jobId, EquipmentType = EquipmentType, MeterNumber = MeterNumber, RegisterNumber = RegisterNumber, ThisReading = ThisReading };

var selectJob = Common.GetParameterFromSession(Constant.SELECTEDJOB); if (selectJob != null) selectJob.JobSpMeters.Add(newMeter);

                UnitOfWork.Add(newMeter);
                UnitOfWork.SaveChanges();

I checked the instance of newMeter and found all paramters were set properly (including the Id field) before it went to unitofwork.saveChanges and got the exception. Could you help find the problem? Many thanks!!!

How is _Jobid being assigned? Or is there an error in your example code above where Id should be JobId in your initializatin of JobSpMeter?

If _Jobid has a correctly assigned value then could you please provide us with a minimal repro (A console application or NUnit test with only the minimal set of entity classes including the partial code for GeneratedId() required to exhibit the error) please? Thanks!

Jeremy

Hi Jeremy

Thank you very much for your reply. I do changed the method set _Jobid like:

var newMeter = new JobSpMeter() {Jobid =jobId, EquipmentType = EquipmentType, MeterNumber = MeterNumber, RegisterNumber = RegisterNumber, ThisReading = ThisReading };

when I checked the object in debug mode, it seems set properly. But still got error when it call : UnitOfWork.SaveChanges(); to update in database. I am wondering the enity class or one to many relationship is not set correctly in model. I have create a simple console app to reproduce the problem and attached the app folder. Please have a look and let me know what's the problem. Many Thanks!!!

Hi Jeremy

Sorry I didn't tell you the setting of our real database tables, the two tables are like the follows:

TABLE JobSP( JobID [int] NOT NULL, -- Key field GroupID [int] NOT NULL, VehicleID [int] NULL, ... )

TABLE JobSPMeter( Jobid [int] NOT NULL, EquipmentType char NOT NULL, MeterNumber varchar NOT NULL, RegisterNumber [smallint] NULL, ThisReading [float] NULL )

Hope it help solve the problem. Thanks again!

The supplied console app did not compile. I have made some updates to include the partial class you described above and fix the complication errors. The error I am then getting is that you have an FK violation as I don't have any test data for JobSp but outside of that the code executes correctly.

The original issue you mentioned was NULL being inserted into the JobId field for JobSpMeter, however that is the mapped Id property for JobSpMeter and that is marked as being IdentityColumn based. If this is still the problem you are seeing I would check that your database has JobId marked as an identity column as LightSpeed is not sending down any value for this field as it expects it to be generated at the database. Additionally you can remove the _JobId and GeneratedId overload as they are redundant given you are using an identity column.

Jeremy

Hi Jeremy

It's works now. Thank you very much for your help!