• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1363
  • Last Modified:

Cannot insert the value NULL into column

I had terrible issue, I am using MS SQL 2005 and today by one of my colleague deleted some entries of two tables [products] and [prodcategories]>. Both tables have auto increment field ids, [catalogid] and [id] respectively.

Since I did not know how I can insert new record with old [catalogid] for instance in table [products], what I did I made new tables [products2]  and [prodcategories2]. Import data from my backup into these new tables where my [catalogid] and [id] are retained. Finally I rename[products2]  to [products] and [prodcategories2] to [prodcategories] respectively. Due to this my web application start working.

But I can no more insert new data from my asp applicaton, I get following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e2f'

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'catalogid', table 'wpg.dbo.products'; column does not allow nulls. INSERT fails.

I further check my new table [products] and found that my catalogid field is:
[catalogid] [int] NOT NULL
while original table has:
[catalogid] [int] IDENTITY(1,1) NOT NULL

Please help to fix it.

I still has my full backup of my database made in MS SQL 2005 but as I am new with 2005 so I am not sure how to restore only two tables from it  [products] &  [prodcategories]. If still I can restore these to over come this issue? Or you can help me to fix my new tables so that my application start working.

Thanks.

Nick
[products]
=======================
 
USE [wpg]
GO
/****** Object:  Table [dbo].[products]    Script Date: 11/15/2008 00:19:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[products](
	[catalogid] [int] IDENTITY(1,1) NOT NULL,
	[ccode] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
	[cname] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
	[cprice] [money] NULL CONSTRAINT [df_products_cprice]  DEFAULT (0),
	[ccategory] [int] NULL CONSTRAINT [df_products_ccategory]  DEFAULT (0),
	[cdescurl] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
	[features] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
	[cimageurl] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
	[cstock] [int] NULL CONSTRAINT [df_products_cstock]  DEFAULT (0),
	[weight] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
	[mfg] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
	[pother1] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
	[pother2] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
	[pother3] [varchar](5000) COLLATE Latin1_General_CI_AS NULL,
	[subcategoryid] [int] NULL CONSTRAINT [df_products_subcategoryid]  DEFAULT (0),
	[retailprice] [money] NULL,
	[specialoffer] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
	[category] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
	[buttonimage] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
	[cdateavailable] [datetime] NULL,
	[allowusertext] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
	[pother4] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
	[pother5] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
	[userid] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
	[keywords] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
	[template] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
	[extendedimage] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
	[selectlist] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
	[level3] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
	[level4] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
	[level5] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
	[minimumquantity] [int] NULL CONSTRAINT [df_products_minimumquantity]  DEFAULT (0),
	[supplierid] [int] NULL CONSTRAINT [df_products_supplierid]  DEFAULT (0),
	[crossselling] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
	[hide] [bit] NULL,
	[productmatch] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
	[customermatch] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
	[orderattachment] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
	[orderdownload] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
	[groupfordiscount] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
	[clanguage] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
	[points] [int] NULL,
	[pointstobuy] [int] NULL,
	[price2] [money] NULL,
	[price3] [money] NULL,
	[billprice] [money] NULL,
	[billinstallments] [int] NULL,
	[billinstallmenttype] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
	[billinterval] [int] NULL,
	[maximumquantity] [int] NULL,
	[frontpage] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
	[inventoryproducts] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
	[taxfree] [bit] NULL,
	[freeshipping] [bit] NULL,
	[highercatalogid] [int] NULL,
	[spdisplaytype] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
	[hassubproduct] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
	[impressions] [int] NULL,
	[cdateadded] [datetime] NULL,
	[cdateupdated] [datetime] NULL,
	[templatelisting] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
	[extraimage1] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
	[extraimage2] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
	[extraimage3] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
	[extraimage4] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
	[extraimage5] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
	[featuredflag] [bit] NULL,
	[customerimage] [bit] NULL,
	[rmadays] [int] NULL,
	[cdescription] [text] COLLATE Latin1_General_CI_AS NULL,
	[extendeddesc] [text] COLLATE Latin1_General_CI_AS NULL,
	[loc_uae] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
	[loc_us] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
	[loc_pak] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
 CONSTRAINT [pk_products] PRIMARY KEY NONCLUSTERED 
(
	[catalogid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Statistic [_dta_stat_1250103494_1_35_37]    Script Date: 11/15/2008 00:19:05 ******/
CREATE STATISTICS [_dta_stat_1250103494_1_35_37] ON [dbo].[products3]([catalogid], [hide], [customermatch])
GO
/****** Object:  Statistic [_dta_stat_1250103494_1_37]    Script Date: 11/15/2008 00:19:05 ******/
CREATE STATISTICS [_dta_stat_1250103494_1_37] ON [dbo].[products3]([catalogid], [customermatch])
GO
/****** Object:  Statistic [_dta_stat_1250103494_1_55_35_37_3_2_25]    Script Date: 11/15/2008 00:19:05 ******/
CREATE STATISTICS [_dta_stat_1250103494_1_55_35_37_3_2_25] ON [dbo].[products3]([catalogid], [highercatalogid], [hide], [customermatch], [cname], [ccode], [keywords])
GO
/****** Object:  Statistic [_dta_stat_1250103494_1_55_37_35]    Script Date: 11/15/2008 00:19:05 ******/
CREATE STATISTICS [_dta_stat_1250103494_1_55_37_35] ON [dbo].[products3]([catalogid], [highercatalogid], [customermatch], [hide])
GO
/****** Object:  Statistic [_dta_stat_1250103494_35_1]    Script Date: 11/15/2008 00:19:05 ******/
CREATE STATISTICS [_dta_stat_1250103494_35_1] ON [dbo].[products3]([hide], [catalogid])
GO
/****** Object:  Statistic [_dta_stat_1250103494_35_55_37_3_2_25]    Script Date: 11/15/2008 00:19:05 ******/
CREATE STATISTICS [_dta_stat_1250103494_35_55_37_3_2_25] ON [dbo].[products3]([hide], [highercatalogid], [customermatch], [cname], [ccode], [keywords])
GO
/****** Object:  Statistic [_dta_stat_1250103494_37_35]    Script Date: 11/15/2008 00:19:05 ******/
CREATE STATISTICS [_dta_stat_1250103494_37_35] ON [dbo].[products3]([customermatch], [hide])
GO
/****** Object:  Statistic [_dta_stat_1250103494_53_1]    Script Date: 11/15/2008 00:19:05 ******/
CREATE STATISTICS [_dta_stat_1250103494_53_1] ON [dbo].[products3]([taxfree], [catalogid])
GO
/****** Object:  Statistic [_dta_stat_1250103494_53_9]    Script Date: 11/15/2008 00:19:05 ******/
CREATE STATISTICS [_dta_stat_1250103494_53_9] ON [dbo].[products3]([taxfree], [cstock])
GO
/****** Object:  Statistic [_dta_stat_1250103494_55_35_1]    Script Date: 11/15/2008 00:19:05 ******/
CREATE STATISTICS [_dta_stat_1250103494_55_35_1] ON [dbo].[products3]([highercatalogid], [hide], [catalogid])
GO
/****** Object:  Statistic [_dta_stat_1250103494_55_35_37]    Script Date: 11/15/2008 00:19:05 ******/
CREATE STATISTICS [_dta_stat_1250103494_55_35_37] ON [dbo].[products3]([highercatalogid], [hide], [customermatch])
GO
/****** Object:  Statistic [_dta_stat_1250103494_55_37]    Script Date: 11/15/2008 00:19:05 ******/
CREATE STATISTICS [_dta_stat_1250103494_55_37] ON [dbo].[products3]([highercatalogid], [customermatch])
 
 
==========================
[prodcategories]
==========================
 
USE [wpg]
GO
/****** Object:  Table [dbo].[prodcategories]    Script Date: 11/15/2008 00:21:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[prodcategories](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[intcategoryid] [int] NULL CONSTRAINT [df_prodcategories_intcategoryid]  DEFAULT (0),
	[intcatalogid] [int] NULL CONSTRAINT [df_prodcategories_intcatalogid]  DEFAULT (0),
 CONSTRAINT [pk_prodcategories] PRIMARY KEY NONCLUSTERED 
(
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
GO
/****** Object:  Statistic [_dta_stat_181575685_3_2]    Script Date: 11/15/2008 00:21:57 ******/
CREATE STATISTICS [_dta_stat_181575685_3_2] ON [dbo].[prodcategories3]([intcatalogid], [intcategoryid])

Open in new window

0
NickHoward
Asked:
NickHoward
2 Solutions
 
tigin44Commented:
your  [catalogid] [int]  NOT NULL
column was an auto increment one... IDENTITY(1,1) shows this. and since your insert statement do not provide value for this column your insert operations fails. To overcome this just open the table to modify and make the [catalogid] field auto increment.
Regars..
0
 
ErnariashCommented:

It seems you have done the job. (do a back up firsts doing this). Just the IDENTITY(1,1)  is missing. you can do that using MS Server Management Studio...Rigth click on your table prodcategories Design select catalogid and set Identity Specification.

Or recreate your steps ...I think what you did was:
Restore your backup into a different database generate script for the two tables with the problems, including permissions and constrains.
Copy your two tables into temp tables
SELECT * INTO TMP_prodcategories FROM prodcategories
SELECT * INTO TMP_products FROM products
----DROP THE TABLES
DROP TABLE prodcategories
DROP TABLE products
---Run the script from to recreate the tables prodcategories, products
SET IDENTITY_INSERT dbo. prodcategories ON
INSERT INTO dbo.prodcategories
SELECT * FROM Gooddatadb.dbo.Prodcategories WITH (HOLDLOCK TABLOCKX)
---not sure you need the data from the backup or not&
INSERT INTO dbo.Products
SELECT * FROM Gooddatadb.dbo. Products WITH (HOLDLOCK TABLOCKX)
SET IDENTITY_INSERT dbo. Products OFF
SET IDENTITY_INSERT dbo.TableClone OFF
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now