SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Dont undertsand this error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GavinD1977
Yak Posting Veteran

United Kingdom
83 Posts

Posted - 08/10/2006 :  11:14:12  Show Profile  Reply with Quote
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

Slovenia
11750 Posts

Posted - 08/10/2006 :  11:15:50  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

GavinD1977
Yak Posting Veteran

United Kingdom
83 Posts

Posted - 08/10/2006 :  11:20:31  Show Profile  Reply with Quote
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.
Go to Top of Page

Srinika
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 08/10/2006 :  11:23:49  Show Profile  Reply with Quote
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
Go to Top of Page

GavinD1977
Yak Posting Veteran

United Kingdom
83 Posts

Posted - 08/10/2006 :  11:24:48  Show Profile  Reply with Quote
Hi.

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

Cheers.
Go to Top of Page

Srinika
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 08/10/2006 :  11:28:36  Show Profile  Reply with Quote
Just check :

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


Srinika
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 08/10/2006 :  11:44:08  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 08/10/2006 :  11:51:05  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/10/2006 :  13:55:52  Show Profile  Reply with Quote
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
Go to Top of Page

Srinika
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 08/10/2006 :  14:07:26  Show Profile  Reply with Quote
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





Srinika

Edited by - Srinika on 08/10/2006 14:08:03
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 08/10/2006 :  14:35:49  Show Profile  Visit spirit1's Homepage  Reply with Quote
have to agree with Srinika 100% on this one



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000