Cannot insert explicit value for identity column in table form50 when IDENTITY_INSERT is set to OFF
Posted on 2007-10-15
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
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?