Solved

Cannot insert the value NULL into column 'ID'

Posted on 2004-10-28
15,627 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
Question by:Ghassano
    8 Comments
     
    LVL 3

    Expert Comment

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

    Expert Comment

    by:pkaleda
    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
    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:
    Best choice is to use teh Autoincrement that is built into SQL.
    0
     
    LVL 1

    Author Comment

    by:Ghassano
    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
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    The Complete Ruby on Rails Developer Course

    Ruby on Rails is one of the most popular web development frameworks, and a useful tool used by both startups and more established companies to build strong graphic user interfaces, and responsive websites and apps.

    Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
    I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
    Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    856 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now