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

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.
Thanks.
Ghassan.
0
Ghassano
Asked:
Ghassano
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Drizzt95Commented:
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.
0
 
pkaledaCommented:
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.

PK
0
 
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
custom.asp?id=123
...................
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 ...
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
pkaledaCommented:
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.

PK
0
 
rohanbairat3Commented:
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.

-rohan
0
 
pkaledaCommented:
Best choice is to use teh Autoincrement that is built into SQL.
0
 
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 !!!
0
 
rohanbairat3Commented:
you should give points to pkaleda His answer was more accurate i guess ... best suited for ur needs but it depends on you :)

-rohan
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now