Link to home
Start Free TrialLog in
Avatar of Gemini532
Gemini532Flag for United States of America

asked on

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

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:

objConn.Open
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

strContact=objRS("txtContact")
strAddress1=objRS("txtAddress1")
strAddress2=objRS("txtAddress2")
strCity=objRS("txtCity")
strState=objRS("txtState")
strZipCode=objRS("txtZipCode")
strCountry=objRS("txtCountry")
strParent=objRS("txtParent")
strTitle=objRS("txtTitle")
strTelephone=objRS("txtTelephone")
strFax=objRS("txtFax")
strEmail=objRS("txtEmail")
strWebsite=objRS("txtWebsite")

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?

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Gemini532

ASKER

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