Dont undertsand this error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dont undertsand this error

Author

Topic

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-10 : 11:14:12

Hi all, can anyone explain to me why i am getting this error:

Cannot insert the value NULL into column 'WORKPATTERN_END', table 'MockDownload_V52.dbo.EMPLOYEE_WORKPATTERN'; column does not allow nulls. INSERT fails.

The code i am using is:

DELETE EMPLOYEE_WORKPATTERN
INSERT INTO EMPLOYEE_WORKPATTERN(
EMPLOY_REF)
SELECT CAST(EMPLOY_REF AS VARCHAR(10))
FROM EMPLOYEE

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-10 : 11:15:50

you have a null value in EMPLOY_REF column.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-10 : 11:20:31

I had thought that myself............however when I do:

SELECT * FROM EMPLOYEE WHERE EMPLOY_REF IS NULL

I get nothing back. Also if i do:

SELECT EMPLOY_REF FROM EMPLOYEE

and manually go through the list there are no nulls shown. Also on the EMPLOYEE table is will not allow inserts of null values into the EMPLOY_REF column?????

Cheers people.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-10 : 11:23:49

If u have a null value in the data of EMPLOY_REF column which is selected, to be inserted to EMPLOYEE_WORKPATTERN, u should decide on what needs to be inserted. (Ur EMPLOYEE_WORKPATTERN's EMPLOY_REF column is created without allowing Nulls)

So u can allow nulls to that or Can use ISNULL function to change the data to whatever u want

Srinika

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-10 : 11:24:48

Hi.

There are definately no nulls in the EMPLOY_REF column????

Cheers.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-10 : 11:28:36

Just check :

SELECT EMPLOY_REF , CAST(EMPLOY_REF AS VARCHAR(10))
FROM EMPLOYEE where CAST(EMPLOY_REF AS VARCHAR(10)) IS NULL

Srinika

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-10 : 11:44:08

better do this...

DELETE EMPLOYEE_WORKPATTERN
INSERT INTO EMPLOYEE_WORKPATTERN(
EMPLOY_REF)
SELECT CAST(EMPLOY_REF AS VARCHAR(10))
FROM EMPLOYEE WHERE EMPLOY_REF IS NOT NULL

Harsh Athalye
India.
"Nothing is Impossible"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-10 : 11:51:05

try recreating an index/check constraint on EMPLOYEE_WORKPATTERN
I don't know if it's possible, but maybe it got corrupt somehow and it has nulls.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-10 : 13:55:52

This answer looks simple to me.

You are inserting a new row, and are not including WORKPATTERN_END in the insert list. Column WORKPATTERN_END does not allow nulls and has no default value, so the insert is giving an error.

You need something like this:


INSERT INTO EMPLOYEE_WORKPATTERN
	(EMPLOY_REF,
	WORKPATTERN_END)
SELECT
	CAST(EMPLOY_REF AS VARCHAR(10)) as EMPLOY_REF,
	some-value-here AS WORKPATTERN_END
FROM
	EMPLOYEE

CODO ERGO SUM

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-10 : 14:07:26

quote:
Originally posted by Michael Valentine Jones

This answer looks simple to me.

You are inserting a new row, and are not including WORKPATTERN_END in the insert list. Column WORKPATTERN_END does not allow nulls and has no default value, so the insert is giving an error.

You need something like this:

INSERT INTO EMPLOYEE_WORKPATTERN
(EMPLOY_REF,
WORKPATTERN_END)
SELECT
CAST(EMPLOY_REF AS VARCHAR(10)) as EMPLOY_REF,
some-value-here AS WORKPATTERN_END
FROM
EMPLOYEE

CODO ERGO SUM