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

NickHowardAsked:
Who is Participating?
 
tigin44Connect With a Mentor Commented:
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
 
ErnariashConnect With a Mentor Commented:

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.