Gemini532
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,tx tCity,txtS tate,txtZi pCode,txtC ountry,txt Parent,txt Contact,tx tTitle,txt Telephone, txtFax,txt Email,txtW ebsite,txt Sales,txtP rojLocCity ,rdoAppTyp e,txtProjL ocCountry, txtProjLoc Desc,rdoPr ojLocContr act, rdoProjLocEnterprise,rdoPr ojLocEnter prise,rdoP rojLocInve st,txtProj LocInvestP erct,txtIn vTotal,txt InvInvestD ate,invest ment,inves tmentExpla nation,rdo InvPlan,rd oProjResEm ploy,chkCo mplete52,r doProjResI mpacts,sub mittedRegi stration,t xtInsName, txtInsTitl e,txtInsCo mpany,txtI nsAddress1 ,txtInsAdd ress2,txtI nsCity,txt InsState,t xtInsZipCo de,txtinsC ountry,txt InsTelepho ne,txtInsF ax,txtInsE mail,chkIn sHear,chkS pecify,chk InsHearOth er,rdoInvP lanOther,r doInvPlanO ther1,name ofofficer, investorCo mpany,date Signed,tot alErrors 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("txtConta ct")
strAddress1=objRS("txtAddr ess1")
strAddress2=objRS("txtAddr ess2")
strCity=objRS("txtCity")
strState=objRS("txtState")
strZipCode=objRS("txtZipCo de")
strCountry=objRS("txtCount ry")
strParent=objRS("txtParent ")
strTitle=objRS("txtTitle")
strTelephone=objRS("txtTel ephone")
strFax=objRS("txtFax")
strEmail=objRS("txtEmail")
strWebsite=objRS("txtWebsi te")
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?
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,
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,tx
'strSQL = "SELECT * from form50 where userid =" & Session("userid")
'Create the recordset
set objRS = server.createObject("adodb
objRS.Open strSQL, objConn
if not objRS.eof then
strContact=objRS("txtConta
strAddress1=objRS("txtAddr
strAddress2=objRS("txtAddr
strCity=objRS("txtCity")
strState=objRS("txtState")
strZipCode=objRS("txtZipCo
strCountry=objRS("txtCount
strParent=objRS("txtParent
strTitle=objRS("txtTitle")
strTelephone=objRS("txtTel
strFax=objRS("txtFax")
strEmail=objRS("txtEmail")
strWebsite=objRS("txtWebsi
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER