Cannot insert the value NULL into column 'ID'

Hello Experts,

well it seems that i always have errors with SQL Server tabels.
Microsoft OLE DB Provider for SQL Server error '80040e2f'

Cannot insert the value NULL into column 'ID', table 'database.dbo.UserLayouts'; column does not allow nulls. INSERT fails.

/custom.asp, line 26
line 26 :
SQL2 = "INSERT INTO UserLayouts ([UserName], [ValueType], [ValueText]) VALUES('" & Session(Right(Setup("WebSiteName"),4) & "UserName") & "', 'Top5Class', '" & Request("Top5Class") & "')"
SET RS2 = Conn.Execute(SQL2)

so "ID" thati have at my table has a primary key which make autonumber for any record set .... i converted my MS access database to SQL and need to know if i want to insert any record to any table from a form with autonumber ID because i am using a query at 3 tables ,,, counts on ID CatID SubCatID.
Who is Participating?
pkaledaConnect With a Mentor Commented:
Best choice is to use teh Autoincrement that is built into SQL.
When you convert an MS Access table to SQL server. it doesn't bring across the autonumber.  You need to go into the table, right click and select properties, and set the identity column to equal ID.
Actually, SQL never sets the ID field to autoincrement.  You need to open Enterprise Manager and navigate to the table.  Right click the table and choose Design Table.  You then set the field to be a type of INT and then Identity should be changed to Yes.

Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

GhassanoAuthor Commented:
no i can't make it as NULL because i am counting on every ID at those records
like every request must take an ID
i need any way to isert a record that bring across the autonumber ...
like if my last record of ID was 13 , the new one will be 14 ...
I gave you this already.

open Enterprise Manager
navigate to the table
Right click the table
choose Design Table
set the field to be a type of INT
Set Identity to Yes.
Click Apply.

This turns off allow null inserts, will autoincrement the numbers.

If you dont want to do that

u should do

select max(id) from the table  increament it by 1 and then store it but you should be using transactions or lock the application for that period so that u avoid the problem of assigning same number to two diff records.

GhassanoAuthor Commented:
hi all and thanks to you all ,
well @rohanbairat3 : well i know this max(id) but i would prefer to do it from the identity because i can't edit again my all codes...
and thanks to Drizzt95 , pkaleda .

Now what should i do ?? split points t you or i must give it to only one person so here i am seeing the same answrs !!!
you should give points to pkaleda His answer was more accurate i guess ... best suited for ur needs but it depends on you :)

All Courses

From novice to tech pro — start learning today.