Tom Winslow
asked on
You tried to assign the null value to a variable that is not a variant data type.
I am trying to change a value from “01 ” to “ “ using either MS Access 2003 or SQL Server Management Studio 2008.
The MS Access SQL:
UPDATE PRODDTA_F41021 SET PRODDTA_F41021.LILOCN = " "
WHERE (((PRODDTA_F41021.LIITM)=9 89955) AND ((PRODDTA_F41021.LIPBIN)=" P"));
The SQL Server 2008 Management Studio SQL:
UPDATE PRODDTA.F41021 SET PRODDTA.F41021.LILOCN = ' ' WHERE (((PRODDTA.F41021.LIITM)=9 89955) AND ((PRODDTA.F41021.LIPBIN)=' P'));
I get this error:
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'F41021_PK'. Cannot insert duplicate key in object 'PRODDTA.F41021'. The duplicate key value is (989955, 24101, , ).
The statement has been terminated.
I know it can be done because the field, LILOCN, already contains the ‘Blank Values’ of " " in many places.
Here is the design speck for the field:
Field Description Type Length Decimals DataItem
LILOCN Location String 20 Location
Somehow, I gotta’ figure out how to do this. Any suggestions?
Tw
SQL-Error.xls
You-tried-to-assign-the-null-val.doc
The MS Access SQL:
UPDATE PRODDTA_F41021 SET PRODDTA_F41021.LILOCN = " "
WHERE (((PRODDTA_F41021.LIITM)=9
The SQL Server 2008 Management Studio SQL:
UPDATE PRODDTA.F41021 SET PRODDTA.F41021.LILOCN = ' ' WHERE (((PRODDTA.F41021.LIITM)=9
I get this error:
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'F41021_PK'. Cannot insert duplicate key in object 'PRODDTA.F41021'. The duplicate key value is (989955, 24101, , ).
The statement has been terminated.
I know it can be done because the field, LILOCN, already contains the ‘Blank Values’ of " " in many places.
Here is the design speck for the field:
Field Description Type Length Decimals DataItem
LILOCN Location String 20 Location
Somehow, I gotta’ figure out how to do this. Any suggestions?
Tw
SQL-Error.xls
You-tried-to-assign-the-null-val.doc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I used your suggestion to find my own solution.
ASKER
tw