Knowledgebase

Home > Item #25

'Cannot insert the value NULL into column XXX' Error

Item: #25
Date: 9 Aug 2001
Applies to
v3
v4
v5

Question

When I click the button to place an order, after having entered all my credit info, I get the error:

Microsoft OLE DB Provider for SQL Server error '80040e2f'
Cannot insert the value NULL into column 'CD_ID', table
'CardholderDetails'; column does not allow nulls. INSERT fails.
/store/asp/functions.asp, line 313

A similar error occurs when I view a product if Hit Tracking is turned on.

Answer

This occurs when a SQL Server has been created by upsizing from Access. The upsizing from Access isn't always perfect. The CD_ID field (as with the _ID fields in all the other tables) is a unique ID, it increments by one every time a new record is added to ensure every record is unique ("a primary key"). When you upsize to SQL server these don't always transition properly.

This isn't a major problem as they are easy to setup manually...

Navigate to the table in question in Enterprise manager in SQL Server. Right click the table and choose design so you get the field listing.

The _ID fields should be an Int (this is probably ok already). To make them like an Access autonumber, set
allow Nulls=N
Identity = Y
Identity seed = 1
Identity increment = 1

You should then set the field to be a primary key too.

You will need to do this for most/all of the tables, if they have an _ID field.

Can't find the answer you need? Click here to open a support ticket.

« Back