?
Solved

Run-time error

Posted on 2003-03-05
9
Medium Priority
?
782 Views
Last Modified: 2010-04-07
Error message when using an application developed in VB6 which connects to a database using ODBC SQL.

Run-Time Error '2147217900 (80040e14)
[Microsoft][ODBC SQL Server Driver][SQL Server] An Explicit value for the identity column in table 'applicationform' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Any ideas on what this means - In English ??
0
Comment
Question by:ScoobyTwo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 18

Expert Comment

by:Sethi
ID: 8071139
Are you trying to use @@Identity with Microsoft Access 97. If yes then it wont work with 97 but works with Access 2000 or later.
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 8071219
If you have a table which has an identity field contained within it such as a unique row identifier, you will get this message if you attempt to specify a value for this field in an insert or update statement.

There are basically two ways to resolve the issue;

1) Don't try to set a value for this field yourself, let SQL do it:

  Insert Into MyTable (Field1,Field2) Values('MyValue1',100)

Where MyTable has three fields:

KeyField int Identity(1,1)
Field1 varchar(200)
Field2 int

2) If you must specify the value yourself for some reason:

  Set IDENTITY_INSERT MyTable ON
  Insert Into MyTable (KeyField,Field1,Field2) Values(1234,'MyValue1',100)
  Set IDENTITY_INSERT MyTable OFF

In your SQL batch.
0
 

Author Comment

by:ScoobyTwo
ID: 8071397
Sorry for being really dense but I don't really have any experience in this area.  (Deep end and dropped !)

What I have found out so far is that one user has this problem on a test machine.

(It works fine for me)

They are trying to save an application form which has been generated using forms produced within a VB6 application which links to a DBO on a SQL Server.

The database (applicationform.dbo) has a number of fields.
One of which is called applicationformid which is listed as the identity field in database properties.

I run on Access 97 on an XP machine - No problem
I believe the user is running on Access 97 on an NT machine.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_7zas.asp

Above site says :

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

I tried opening several instances of the program to see if that might have caused the problem of a SET IDENTITY_INSERT ON but it worked fine.  Searched through the VB code to see if I could identify at which point the SET was happening but couldn't see any mention of it. ?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 43

Expert Comment

by:TimCottee
ID: 8071447
Ok, do you have the code for the area around where the problem occurs?
0
 

Author Comment

by:ScoobyTwo
ID: 8071508
Sorry for being really dense but I don't really have any experience in this area.  (Deep end and dropped !)

What I have found out so far is that one user has this problem on a test machine.

(It works fine for me)

They are trying to save an application form which has been generated using forms produced within a VB6 application which links to a DBO on a SQL Server.

The database (applicationform.dbo) has a number of fields.
One of which is called applicationformid which is listed as the identity field in database properties.

I run on Access 97 on an XP machine - No problem
I believe the user is running on Access 97 on an NT machine.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_7zas.asp

Above site says :

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

I tried opening several instances of the program to see if that might have caused the problem of a SET IDENTITY_INSERT ON but it worked fine.  Searched through the VB code to see if I could identify at which point the SET was happening but couldn't see any mention of it. ?
0
 

Author Comment

by:ScoobyTwo
ID: 8071737
Tim - I believe this is the code that is running.  The msg appears when the user tries to save the record.

Private Sub cmdSaveApplicationform_Click()

    Dim blnNewCandidate                         As Boolean
    Dim blnDBUpdateOK                           As Boolean
    Dim blnNonUKCitizen                         As Boolean
    Dim blnLegalResident                        As Boolean
    Dim blnImmigrationControl                   As Boolean
    Dim blnUKResidentRestriction                As Boolean
    Dim blnEnglishWelshFluent                   As Boolean
   
    Dim blnAbortSave                            As Boolean
    Dim blnValidated                            As Boolean
    Dim blnGuaranteedInterview                  As Boolean
    Dim dtmDateOfBirth                          As Date
    Dim dtmReceivedDate                         As Date
    Dim dtmAdvertDate                           As Date
    Dim intButton                               As Integer
    Dim intTab                                  As Integer
    Dim intThisTab                              As Integer
    Dim intCount                                As Integer
    Dim intIndex                                As Integer
    Dim intSalutation                           As Integer
    Dim intCandidateid                          As Integer
    Dim intNationalityAtBirth                   As Integer
    Dim intNationalityNow                       As Integer
    Dim intWelshAbility                         As Integer
    Dim intPreferredInterviewLanguage           As Integer
    Dim intSource                               As Integer
    Dim strCandidateRef                         As String
    Dim strNextReference                        As String
    Dim strKeyReference                         As String
    Dim strReference                            As String
    Dim strSalutation                           As String
    Dim strFirstName                            As String
    Dim strMiddleName                           As String
    Dim strSurname                              As String
    Dim strNINumber                             As String
    Dim strErrMess                              As String
    Dim strHomePhone                            As String
    Dim strBusinessPhone                        As String
    Dim strMobilePhone                          As String
    Dim strEmail                                As String
    Dim strNonUKCitizenComment                  As String
    Dim strImmigrationControlComment            As String
    Dim strUKResidenceRestrictionComment        As String
    Dim strEmploymentGapComment                 As String
    Dim strInterviewRequirement                 As String
    Dim strSourceName                           As String
    Dim strOtherComments                        As String
    Dim strMessage                              As String
   
   
    blnAbortSave = False
    blnValidated = False
    intThisTab = tabApplicationForm.Tab
    validateTab intThisTab, False, strErrMess
   
    intTab = 0
    While (Not blnValidated) And (Not blnAbortSave)
        If strErrMess <> "" Then
            intButton = MsgBox(strErrMess, _
                        vbExclamation, _
                        "Application Form")
            mblnValidationCausedSwitch = True
            tabApplicationForm.Tab = intTab
            mblnValidationCausedSwitch = False
            blnAbortSave = True
        Else
            validateTab intTab, False, strErrMess
            If strErrMess = "" Then
                If intTab = 9 Then
                    blnValidated = True
                End If
                intTab = intTab + 1
            End If
        End If
    Wend
       
    If blnValidated Then
        blnNewCandidate = False
       
        If txtCandidateReference.Text = "" Then
            mclsCandidate.fetchNextCandidateReference strNextReference, _
                                                        intCount
            blnNewCandidate = True
        Else
            strReference = txtCandidateReference.Text
        End If
       
        blnDBUpdateOK = True
'
' Retrieve Candidate information from form
'
        intIndex = cboSalutation.ListIndex
        mclsSalutation.returnIDfromComboIndex intIndex, intSalutation
        strFirstName = txtFirstName.Text
        strMiddleName = txtMiddleName.Text
        strSurname = txtSurname.Text
        strNINumber = txtNINumber.Text
        If txtDateOfBirth.Text <> "" Then
            dtmDateOfBirth = txtDateOfBirth.Text
        End If
'
' Retrieve ApplicationForm information from form
'
        strHomePhone = txtHomePhone.Text
        strBusinessPhone = txtBusinessPhone.Text
        strMobilePhone = txtMobilePhone.Text
        strEmail = txtEmail.Text
        intNationalityAtBirth = _
            mclsNationalityAtBirth.returnIDfromComboText(cboNationalityAtBirth)
        intNationalityNow = _
            mclsNationalityNow.returnIDfromComboText(cboNationalityNow)
        blnNonUKCitizen = checkBoxBoolean(chkNonUKCitizen)
        strNonUKCitizenComment = txtNonUKCitizen.Text
        blnLegalResident = checkBoxBoolean(chkLegalResident)
        blnImmigrationControl = checkBoxBoolean(chkImmigrationControl)
        strImmigrationControlComment = txtImmigrationControl.Text
        blnUKResidentRestriction = checkBoxBoolean(chkUKResidenceRestriction)
        strUKResidenceRestrictionComment = txtUKResidenceRestriction
        strEmploymentGapComment = txtEmploymentGapComments.Text
        blnEnglishWelshFluent = checkBoxBoolean(chkEnglishWelshFluent)
        intWelshAbility = mclsWelshAbility.returnIDfromComboText(cboWelshAbility)
        If cboInterviewLanguage.Text = "" Then
            cboInterviewLanguage.Text = "English"
        End If
        intPreferredInterviewLanguage = _
            mclsInterviewLanguage.returnIDfromComboText(cboInterviewLanguage)
        blnGuaranteedInterview = checkBoxBoolean(chkGuaranteedInterview)
        strInterviewRequirement = txtInterviewRequirements.Text
        dtmReceivedDate = txtReceivedDate.Text
        intSource = mclsSource.returnIDfromComboText(cboSource)
        strSourceName = cboSourceName.Text
        If txtAdvertDate.Text <> "" Then
            dtmAdvertDate = txtAdvertDate.Text
        End If
        strOtherComments = txtOtherInf.Text
'
' If its a new candidate Insert Candidate and Application form
' Otherwise Update both.
'
'        MsgBox "Salutation           " & intSalutation & _
'                "Nationality at Birth " & intNationalityAtBirth & _
'                "Nationality Now      " & intNationalityNow
                       
        gclsDBControl.beginTransaction
        If blnNewCandidate Then
            intCandidateid = mclsCandidate.insertCandidate(strNextReference, _
                                                             intSalutation, _
                                                             strFirstName, _
                                                             strMiddleName, _
                                                             strSurname, _
                                                             strNINumber, _
                                                             dtmDateOfBirth, _
                                                             intCount)
            gintApplicationFormID = mclsApplicationForm.insertApplicationForm( _
                                            intCandidateid, strHomePhone, _
                                            strBusinessPhone, _
                                            strMobilePhone, _
                                            strEmail, _
                                            intNationalityAtBirth, _
                                            intNationalityNow, _
                                            blnNonUKCitizen, _
                                            strNonUKCitizenComment, _
                                            blnLegalResident, _
                                            blnImmigrationControl, _
                                            strImmigrationControlComment, _
                                            blnUKResidentRestriction, _
                                            strUKResidenceRestrictionComment, _
                                            strEmploymentGapComment, _
                                            blnEnglishWelshFluent, _
                                            intWelshAbility, _
                                            intPreferredInterviewLanguage, _
                                            blnGuaranteedInterview, _
                                            strInterviewRequirement, _
                                            dtmReceivedDate, _
                                            intSource, _
                                            strSourceName, _
                                            dtmAdvertDate, strOtherComments, _
                                            intCount)
                   
        Else
            strKeyReference = txtCandidateReference.Text
            intCandidateid = mclsCandidate.updateCandidate(strKeyReference, _
                                                            intSalutation, _
                                                            strFirstName, _
                                                            strMiddleName, _
                                                            strSurname, _
                                                            strNINumber, _
                                                            dtmDateOfBirth, _
                                                            intCount)
            mclsApplicationForm.updateApplicationForm gintApplicationFormID, intCandidateid, _
                                                        strHomePhone, _
                                                        strBusinessPhone, _
                                                        strMobilePhone, _
                                                        strEmail, _
                                                        intNationalityAtBirth, _
                                                        intNationalityNow, _
                                                        blnNonUKCitizen, _
                                                        strNonUKCitizenComment, _
                                                        blnLegalResident, _
                                                        blnImmigrationControl, _
                                                        strImmigrationControlComment, _
                                                        blnUKResidentRestriction, _
                                                        strUKResidenceRestrictionComment, _
                                                        strEmploymentGapComment, _
                                                        blnEnglishWelshFluent, _
                                                        intWelshAbility, _
                                                        intPreferredInterviewLanguage, _
                                                        blnGuaranteedInterview, _
                                                        strInterviewRequirement, _
                                                        dtmReceivedDate, _
                                                        intSource, _
                                                        strSourceName, _
                                                        dtmAdvertDate, strOtherComments, _
                                                        intCount
        End If
       
        If blnDBUpdateOK Then
            blnDBUpdateOK = False
            If mclsAddress.updateDBfromFlex(msgAddress) Then
                If mclsLanguageAbility.updateDBfromFlex(msgLanguages) Then
                    If mclsPreviousEmployment.updateDBfromFlex(msgPreviousEmployment) Then
                        If mclsProfQualifications.updateDBfromFlex(msgProfQuals) Then
                            If mclsQualifications.updateDBfromFlex(msgEduQuals) Then
                                If mclsReferee.updateDBfromFlex(msgReferees) Then
                                    If mclsRelation.updateDBfromFlex(msgRelations) Then
                                        If mclsVacancyApplication.updateDBfromFlex _
                                                                    (msgVacancies) Then
                                            blnDBUpdateOK = True
                                        End If
                                    End If
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        End If
        If blnDBUpdateOK Then
            gclsDBControl.commitTransaction
            If blnNewCandidate Then
                strMessage = "Candidate has been added to the database." + _
                            vbCrLf + vbCrLf + _
                            "Assigned Candidate Reference Number: " + _
                            strNextReference + vbCrLf + vbCrLf + _
                            "Do you want to enter/modify Equal Opportunity data now"
            Else
                strMessage = "Candidate/Application form updated succesfully" + _
                            vbCrLf + vbCrLf + _
                            "Do you want to enter/modify Equal Opportunity data now"
            End If
            intButton = MsgBox(strMessage, _
                        vbYesNo, "Application Form")
            If intButton = vbYes Then
                    openForm frmEqualOpportunities, False
            End If
'            clearApplicationForm
        Else
            gclsDBControl.rollbackTransaction
            MsgBox "Database error prevented changes from being applied", _
                    vbExclamation + vbOKOnly, "ApplicationForm"
        End If
           
    End If
   
End Sub
0
 

Expert Comment

by:CleanupPing
ID: 8900659
ScoobyTwo:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Experts: Post your closing recommendations!  Who deserves points here?
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 9046932
ScoobyTwo, an EE Moderator will handle this for you.
Moderator, my recommended disposition is:

    Refund points and save as a 0-pt PAQ.

DanRollins -- EE database cleanup volunteer
0
 

Accepted Solution

by:
YensidMod earned 0 total points
ID: 9151075
Question is PAQ'd and points refunded.

YensidMod
Community Support Moderator @Experts Exchange
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month10 days, 21 hours left to enroll

770 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