[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Cannot insert the value NULL into column 'ID'

Posted on 2004-10-28
8
Medium Priority
?
15,636 Views
Last Modified: 2012-06-21
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
Comment
Question by:Ghassano
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 3

Expert Comment

by:Drizzt95
ID: 12431151
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
 
LVL 6

Expert Comment

by:pkaleda
ID: 12432618
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
 
LVL 1

Author Comment

by:Ghassano
ID: 12434972
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:pkaleda
ID: 12435129
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
 
LVL 7

Expert Comment

by:rohanbairat3
ID: 12437782
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
 
LVL 6

Accepted Solution

by:
pkaleda earned 500 total points
ID: 12438023
Best choice is to use teh Autoincrement that is built into SQL.
0
 
LVL 1

Author Comment

by:Ghassano
ID: 12438038
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
 
LVL 7

Expert Comment

by:rohanbairat3
ID: 12438191
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question