SqlException: Cannot insert the value NULL into column 'Tno', table 'dbTaskManager.dbo.tblTask'; column does not allow nulls. INSERT fails

0
votes

I have a following exception in my code. I already read many solutions but never worked in my case. Table tblTask contains columns taskId and Tno. taskId is set to be an identity column, and I pass a specific value for Tno, but I get this exception:

SqlException: Cannot insert the value NULL into column 'Tno', table 'dbTaskManager.dbo.tblTask'; column does not allow nulls. INSERT fails.

Here is the table design

CREATE TABLE [dbo].[tblTask] (
[TaskId]             NUMERIC (18)    IDENTITY (1, 1) NOT NULL,
[ClientId]           INT             NULL,
[UserName]           VARCHAR (50)    NULL,
[Task]               VARCHAR (MAX)   NULL,
[EmpId]              INT             NULL,
[AssignedDate]       DATETIME2 (7)   NULL,
[DueDate]            DATETIME2 (7)   NULL,
[CompletionDate]     DATETIME2 (7)   NULL,
[EstTime]            FLOAT (53)      NULL,
[ActualTime]         VARCHAR (50)    NULL,
[Status]             VARCHAR (50)    NULL,
[Priority]           VARCHAR (50)    NULL,
[Remarks]            VARCHAR (8000)  NULL,
[TechnicalRemarks1]  VARCHAR (8000)  NULL,
[TechnicalRemarks2]  VARCHAR (8000)  NULL,
[TechnicalRemarks3]  VARCHAR (8000)  NULL,
[TaskPercentage]     NUMERIC (18, 2) NULL,
[StartTime]          DATETIME2 (7)   NULL,
[Tno]                INT             NOT NULL,
[DeliverToday]       BIT             NULL,
[dt_Status]          BIT             NULL,
[UpdateStatusNO]     INT             CONSTRAINT [DF__tblTask__UpdateS__5070F446] DEFAULT ((0)) NULL,
[patchStatus]        INT             NULL,
[SupportFile]        VARCHAR (MAX)   NULL,
[Codecommit]         INT             NULL,
[Tremark1file]       VARCHAR (MAX)   NULL,
[Tremark2file]       VARCHAR (MAX)   NULL,
[remarkfile]         VARCHAR (MAX)   NULL,
[Taskfile]           VARCHAR (MAX)   NULL,
[Moduleid]           INT             NULL,
[StatusDate]         DATETIME2 (7)   NULL,
[ParentID]           INT             NULL,
[TaskTypeid]         INT             NULL,
[Projectid]          INT             NULL,
[SubTaskTypeid]      INT             NULL,
[Ptaskid]            INT             NULL,
[DepartmentID]       INT             NULL,
[SuggesionRemarks]   VARCHAR (8000)  NULL,
[DevHours]           FLOAT (53)      NULL,
[Start]              INT             NULL,
[IsStartDate]        INT             NULL,
[ReleaseID]          NVARCHAR (MAX)  NOT NULL,
[Bug]                INT             NULL,
[Suggestions]        INT             NULL,
[Approved]           INT             NULL,
[Movement]           INT             NULL,
[QAHrs]              NUMERIC (18)    NULL,
[MiscHrs]            NUMERIC (18)    NULL,
[summary]            VARCHAR (500)   NULL,
[Email]              VARCHAR (500)   NULL,
[LocalClientDetails] VARCHAR (500)   NULL,
CONSTRAINT [PK_tblTask] PRIMARY KEY CLUSTERED ([Tno] ASC)
);


GO
CREATE TRIGGER [dbo].[UpdateTblTask]  ON  dbo.tblTask
for Update
AS 
BEGIN

if Update(Status) or Update(EmpID)
begin
    insert into tblTaskLog (TaskId,ClientId,UserName,Task,EmpIdFrom,EmpIdTo,AssignedDate,DueDate,EstTime,StatusFrom,StatusTo,SystemName,CreationDate)
    select  D.TaskId,D.ClientId,D.UserName,D.Task,D.EmpId,I.EmpId,D.AssignedDate,D.DueDate,D.EstTime,D.Status,i.Status,Host_Name(),Getdate()
    From Deleted D inner join Inserted I on D.TaskId=I.TaskId
    Where D.Status<>I.Status or D.EmpID<>I.EmpID
    ------------------------------------------------------

    ---------------------Status Change---------------------
    if Update(Status)
    begin
         Declare @OldStatus  varchar(200)
         Declare @NewStatus  varchar(200)
        select @NewStatus=Status  from inserted
        select @OldStatus=Status from deleted
        --select @NewStatus NewStatus,@OldStatus OldStatus
        --if((Select status from inserted)<>(select Status from deleted))
        if @NewStatus <> @OldStatus
        begin

            Update tbltask set Statusdate=Getdate() where taskid=(Select Taskid from inserted)
        end

    end
    --------------------------------------------------------------

End

End
GO
DISABLE TRIGGER [dbo].[UpdateTblTask]
    ON [dbo].[tblTask];

Here is TaskMainDTO.cs

[Table("tblTask")]
public class TaskMainDTO
{
    public int? ClientId { get; set; }
    public string Username { get; set; }
    public string Task { get; set; }
    public int? EmpId { get; set; }
    public DateTime? AssignedDate { get; set; }
    public DateTime? DueDate { get; set; }
    public DateTime? CompletionDate { get; set; }
    public double? EstTime { get; set; }
    public string ActualTime { get; set; }
    public string Status { get; set; }
    public string Priority { get; set; }
    public string Remarks { get; set; }
    public string TechnicalRemarks1 { get; set; }
    public string TechnicalRemarks2 { get; set; }
    public string TechnicalRemarks3 { get; set; }
    public decimal? TaskPercentage { get; set; }
    public DateTime? StartTime { get; set; }
    [Key]
    public int Tno { get; set; }
    public bool? DeliverToday { get; set; }
    public bool? dt_Status { get; set; }
    public int? UpdateStatusNo { get; set; }
    public int? patchStatus { get; set; }
    public string SupportFile { get; set; }
    public int? Codecommit { get; set; }
    public string Tremark1file { get; set; }
    public string Tremark2file { get; set; }
    public string remarkfile { get; set; }
    public string Taskfile { get; set; }
    public int? Moduleid { get; set; }
    public DateTime StatusDate { get; set; }
    public int? ParentID { get; set; }
    public int? TaskTypeid { get; set; }
    public int? Projectid { get; set; }
    public int? SubTaskTypeid { get; set; }
    public int? Ptaskid { get; set; }
    public int? DepartmentID { get; set; }
    public string SuggesionRemarks { get; set; }
    public double? DevHours { get; set; }
    public int? Start { get; set; }
    public int? IsStartDate { get; set; }
    public string ReleaseID { get; set; }
    public int? Bug { get; set; }
    public int? Suggestions { get; set; }
    public int? Approved { get; set; }
    public int? Movement { get; set; }
    public decimal? QAHrs { get; set; }
    public decimal? MiscHrs { get; set; }
    public string summary { get; set; }
    public string Email { get; set; }
    public string LocalClientDetails { get; set; }

Here is TaskMainVM.cs

 public class TaskMainVM
{
    public TaskMainVM()
    {

    }
    public TaskMainVM(TaskMainDTO model)
    {
        ClientId = model.ClientId;
        Projectid = model.Projectid;
        Moduleid = model.Moduleid;
        TaskTypeid = model.TaskTypeid;
        SubTaskTypeid = model.SubTaskTypeid;
        EmpId = model.EmpId;
        DepartmentID = model.DepartmentID;
        Username = model.Username;
        Status = model.Status;
        Priority = model.Priority;
        ReleaseID = model.ReleaseID;
        AssignedDate = model.AssignedDate;
        DueDate = model.DueDate;
        CompletionDate = model.CompletionDate;
        StartTime = model.StartTime;
        ActualTime = model.ActualTime;
        DevHours = model.DevHours;
        QAHrs = model.QAHrs;
        MiscHrs = model.MiscHrs;
        Tno = model.Tno;
        summary = model.summary;
        Task = model.Task;
        Taskfile = model.Taskfile;
        LocalClientDetails = model.LocalClientDetails;
        Email = model.Email;

    }

    public int? ClientId { get; set; }
    public string Username { get; set; }
    public string Task { get; set; }
    public int? EmpId { get; set; }
    public DateTime? AssignedDate { get; set; }
    public DateTime? DueDate { get; set; }
    public DateTime? CompletionDate { get; set; }
    public double? EstTime { get; set; }
    public string ActualTime { get; set; }
    public string Status { get; set; }
    public string Priority { get; set; }
    public string Remarks { get; set; }
    public string TechnicalRemarks1 { get; set; }
    public string TechnicalRemarks2 { get; set; }
    public string TechnicalRemarks3 { get; set; }
    public decimal? TaskPercentage { get; set; }
    public DateTime? StartTime { get; set; }

    public int Tno { get; set; }
    public bool? DeliverToday { get; set; }
    public bool? dt_Status { get; set; }
    public int? UpdateStatusNo { get; set; }
    public int? patchStatus { get; set; }
    public string SupportFile { get; set; }
    public int? Codecommit { get; set; }
    public string Tremark1file { get; set; }
    public string Tremark2file { get; set; }
    public string remarkfile { get; set; }
    public string Taskfile { get; set; }
    public int? Moduleid { get; set; }
    public DateTime StatusDate { get; set; }
    public int? ParentID { get; set; }
    public int? TaskTypeid { get; set; }
    public int? Projectid { get; set; }
    public int? SubTaskTypeid { get; set; }
    public int? Ptaskid { get; set; }
    public int? DepartmentID { get; set; }
    public string SuggesionRemarks { get; set; }
    public double? DevHours { get; set; }
    public int? Start { get; set; }
    public int? IsStartDate { get; set; }
    public string ReleaseID { get; set; }
    public int? Bug { get; set; }
    public int? Suggestions { get; set; }
    public int? Approved { get; set; }
    public int? Movement { get; set; }
    public decimal? QAHrs { get; set; }
    public decimal? MiscHrs { get; set; }
    public string summary { get; set; }
    public string Email { get; set; }
    public string LocalClientDetails { get; set; }

Here is TaskController.cs

 // save task data
    public JsonResult SaveTaskDataInDb(TaskMainVM model)
    {
        var result = false;
        try
        {
            using (Db db = new Db())
            {
                    TaskMainDTO dto = new TaskMainDTO();

                dto.ClientId = model.ClientId;
                dto.Projectid = model.Projectid;
                dto.Moduleid = model.Moduleid;
                dto.TaskTypeid = model.TaskTypeid;
                dto.SubTaskTypeid = model.SubTaskTypeid;
                dto.EmpId = model.EmpId;
                dto.DepartmentID = model.DepartmentID;
                dto.Username = model.Username;
                dto.Status = model.Status;
                dto.Priority = model.Priority;
                dto.ReleaseID = model.ReleaseID;
                dto.AssignedDate = model.AssignedDate;
                dto.DueDate = model.DueDate;
                dto.CompletionDate = model.CompletionDate;
                dto.StartTime = model.StartTime;
                dto.ActualTime = model.ActualTime;
                dto.DevHours = model.DevHours;
                dto.QAHrs = model.QAHrs;
                dto.MiscHrs = model.MiscHrs;
                dto.Tno = 78999;
                dto.summary = model.summary;
                dto.Task = model.Task;
                dto.Taskfile = model.Taskfile;
                dto.LocalClientDetails = model.LocalClientDetails;
                dto.Email = model.Email;


                db.TaskMain.Add(dto);
                    db.SaveChanges();
                    result = true;


            }


        }
        catch (Exception ex)
        {
            throw ex;
        }
        return Json(result, JsonRequestBehavior.AllowGet);

    }
1
c#asp.netsql-serverasp.net-mvcexception
So for one of the columns you are not setting a value so it passes it NULL which is not allowed. Can you post the table script used to create it and the code you are using to add the data to this table? - HenryMigo
Your insert query does not set a value for Tno but the table is designed to require a value. - Crowcoder
SHOW US THE CODE! we cannot possibly read your screen, nor your mind - you have to show us what you're doing, then we might be able to help ! - marc_s
@HenryMigo kindly see now ...i add all the code. - Aaqib Ali Bhutta
@marc_s kindly see now ...i add all the code. - Aaqib Ali Bhutta

1 Answers

0
votes

Your table has the following NOT NULL columns TaskId, Tno, ReleaseID.

As you are making an insertion operation, then you have not to check a column TaskID as this column has IDENTITY(1,1,). This means that SQL Server will care about this column by itself.

However, the rest two NOT NULL columns such as Tno, ReleaseID should be cared by you. So just use null coalescing operator ?? to set a fallback value to these columns if they are assigned null values: 

using (Db db = new Db())
{
     TaskMainDTO dto = new TaskMainDTO();
     //...
     dto.ReleaseID = model.ReleaseID ?? 0;
     //...
     dto.Tno = 78999;
}

UPDATE:

This row means that if model.ReleaseID does not have any value(it means NULL), then dto.ReleaseID will be set to 0. The 0 here is fallback value to prevent of getting exception SqlException: Cannot insert the value NULL ...:

dto.ReleaseID = model.ReleaseID ?? 0;

If you do not want to set 0, then you can throw an exception:

if(model.ReleaseID == null || dto.Tno == null)
    throw new ArgumentNullException("SaveTaskDataInDb(TaskMainVM model) method 
        received a null argument!");