Cannot insert the value NULL into column

I'm having some problems. I am trying to get a table to update, however it is not updating because one of the fields contains rows with a NULL value.

Heres the original query which gives no error:

        sql="UPDATE empPac SET quantityLimit = allocation, allocationStart = '"&allocationStart&"', nextUpdate = DATEADD(mm, allocationMonths, "&allocationStart&"), lastUpdate = GETDATE(), quantityIssued = 0, quantityShipped = 0 WHERE allocationMonths <> 0 AND nextUpdate <= DATEADD(mm, "&checkCondition&", GETDATE()) and empIdent in (select empIdent from employee where custIdent='"&custIdent&"')"

Now, the nextUpdate column might be NULL, so I am trying to accommodate for that with this query:

        sql="UPDATE empPac SET quantityLimit = allocation, allocationStart = '"&allocationStart&"', nextUpdate = DATEADD(mm, allocationMonths, "&allocationStart&"), lastUpdate = GETDATE(), quantityIssued = 0, quantityShipped = 0 WHERE allocationMonths <> 0 AND (nextUpdate <= DATEADD(mm, "&checkCondition&", GETDATE()) OR nextUpdate IS NULL) and empIdent in (select empIdent 开发者_JAVA技巧from employee where custIdent='"&custIdent&"')"

You can see I've added "OR nextUpdate IS NULL" in brackets with the other condition for nextUpdate.

However, I am getting an error "Cannot insert the value NULL into column 'quantityLimit', table 'myname.dbo.EmpPac'; column does not allow nulls. UPDATE fails."

This makes no sense to me as, when viewing myLittleAdmin, shows me a NULL value for some rows in that table column.

The issue is not the column nextUpdate; according to the error message, the problem is an attempt to assign NULL into the column quantityLimit. Looking at your code, you are setting quantityLimit equal to the value allocation, which I presume is another column in your database - make sure that column or input is not null.

This has nothing to do with the NextUpdate column being NULL, this has to do with the allocation column in empPac table being NULL. Handle the allocation column being NULL (or fix the data) and it will work fine.

EDIT: The only thing I can guarantee with this is that the error will go away:

sql="UPDATE empPac SET quantityLimit = allocation, allocationStart = '"&allocationStart&"', nextUpdate = DATEADD(mm, allocationMonths, "&allocationStart&"), lastUpdate = GETDATE(), quantityIssued = 0, quantityShipped = 0 WHERE allocationMonths <> 0 AND (nextUpdate <= DATEADD(mm, "&checkCondition&", GETDATE()) OR nextUpdate IS NULL) and empIdent in (select empIdent from employee where custIdent='"&custIdent&"') AND allocation IS NOT NULL"

Adding OR nextUpdate IS NULL to your WHERE clause widens the search criteria - i.e. more rows can be found.

Apparently, one of these additional rows has a NULL in allocation field (which then gets assigned to quantityLimit which, according to the error message, is NOT NULL).

Just an Observation, if you try to Edit a table column to make it Not Null which were null previously . You may get an error saying:

"cannot insert the value null into column update fails"

Even though you got the correct update sql statement in place

ALTER TABLE
[dbo].[Table name] 
ALTER COLUMN
  [Column Name]
    NVARCHAR(250) NOT NULL;

Solution: Very simple

1. Insert some value on the empty rows of the same column
2. Try to execute update statement now . Hope this save some time and would helps.

Vinu Nair