Cannot insert explicit value for identity column in table form50 when IDENTITY_INSERT is set to OFF

Posted on 2007-10-15
Last Modified: 2009-07-29
Hello Everyone, I have a table which allows NULL data, but the SELECT statement fails when one of those fields is NULL

Here's the ERROR:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'form50' when IDENTITY_INSERT is set to OFF.

/OPICForms/50v2/50-02.asp, line 140

I think this error has something to do with the primary key which is a data type numeric and has Identity set to Yes

Once this error goes away however, the next error will show the it cannot find the txtCountry field which is NULL, and then it will complain if the txtWebsite is null, but these fiels are allowed to be NULL in the SQL SERVER 2000 database... I am not sure what is wrong here, but clearly the database and the form are not working together too well...

I moved the data from a production server to the development server to test it and for some reason I keep getting errors...

Do you think you can help me?
If you need some more code, I will definitely include it, for now I will include my connection code with the SQL Statement:

strSQL = "SELECT txtAddress1,txtAddress2,txtCity,txtState,txtZipCode,txtCountry,txtParent,txtContact,txtTitle,txtTelephone,txtFax,txtEmail,txtWebsite,txtSales,txtProjLocCity,rdoAppType,txtProjLocCountry,txtProjLocDesc,rdoProjLocContract, rdoProjLocEnterprise,rdoProjLocEnterprise,rdoProjLocInvest,txtProjLocInvestPerct,txtInvTotal,txtInvInvestDate,investment,investmentExplanation,rdoInvPlan,rdoProjResEmploy,chkComplete52,rdoProjResImpacts,submittedRegistration,txtInsName,txtInsTitle,txtInsCompany,txtInsAddress1,txtInsAddress2,txtInsCity,txtInsState,txtInsZipCode,txtinsCountry,txtInsTelephone,txtInsFax,txtInsEmail,chkInsHear,chkSpecify,chkInsHearOther,rdoInvPlanOther,rdoInvPlanOther1,nameofofficer,investorCompany,dateSigned,totalErrors from form50 where userid =" & Session("userid")
'strSQL = "SELECT * from form50 where userid =" & Session("userid")

'Create the recordset
set objRS = server.createObject("adodb.recordset")
objRS.Open strSQL, objConn

if not objRS.eof then


End If      

I think the reason for this problem is that I am combining 2 versions of the same form. the First version has no country or website, but the second version does... So far the problem comes when I filled  in the second version and add a country and a website and the computer says that it cannot find it...

Surely using the same table, I could get both version to display, I have an if statment in the stored procedure which separates the versions and displays them based on the version ID

This used to work until I deleted the entire data from the database to start from scratch and the I added all the version 1 data from production, now I cannot see the verision 1 data or create a version 2 record....

Some of the fields used in version 1 and not used in version 2 and vice versa, but they are all in the same table and they all allow NULLS

Can you help me fix the first error, so I can get the second error to show up that way the problem can become a bit more clear

As far as I can see the first error is caused by the numeric primary key not haveing its identity set to Yes, but it is set to yes, do you think it's giving me an error because I got the data from the production database, each primary key is still unique though...  any ideas?

Question by:Gemini532
    LVL 142

    Accepted Solution

    >[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'form50' when IDENTITY_INSERT is set to OFF.

    a SELECT statement CANNOT give you that error, no way.
    only a INSERT statement can (or an update/delete in case there is a trigger that in the background issues another INSERT which then gives the problem).

    you must be looking on the wrong place.

    Author Comment

    THANK YOU ANGEL!!!  I'll check again!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how the fundamental information of how to create a table.

    779 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

    12 Experts available now in Live!

    Get 1:1 Help Now