Re: IDENTITY_INSERT error
From: Ted W9999 (TedW9999_at_discussions.microsoft.com)
Date: 10/08/04
- Next message: Ted W9999: "Re: IDENTITY_INSERT error"
- Previous message: M Smith: "Re: Error using JET_SCHEMA_USERROSTER"
- In reply to: Val Mazur: "Re: IDENTITY_INSERT error"
- Next in thread: Ted W9999: "Re: IDENTITY_INSERT error"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 8 Oct 2004 09:27:07 -0700
That seems to have done it! Thank you.
It's strange that the code has been working for years and then quit. Also,
the definition of the same "session" doesn't seem to fit; it IS definitely
the same connection, just a different ADODB.Command. Apparently it now has to
be the same ADODB.Command, whereas previously it did not.
Anyway, thanks again!
"Val Mazur" wrote:
> Hi,
>
> Session means same opened connection. If you do not close connection
> somewhere in between the calls, then it should work. But it is possible that
> your command opens another connection and you could check it using SQL
> Profiler. What you could do is (if you are using OLEDB Provider for SQL
> Server) is to execute both statement in one batch separating them by
> semicolon (;)
>
> cmd.CommandText = "SET IDENTITY_INSERT Surveys ON; INSERT INTO SURVEYS
> (SurveyID,InsuredRef,Location,Sequence) VALUES (?,?,?,?)"
> cmd.parameters(0) = 123
> cmd.parameters(1) = 'abc'
> ......
>
> --
> Val Mazur
> Microsoft MVP
>
>
> "Ted W9999" <Ted W9999@discussions.microsoft.com> wrote in message
> news:3CABCFDD-82BC-4BAE-89DA-B2E1BAA51609@microsoft.com...
> > I've had an application in production for years, and after making minor
> > changes it is getting errors at my client site and now here. The error is
> > "Cannot insert explicit value for identity column in table 'Surveys' when
> > IDENTITY_INSERT is set to OFF."
> >
> > Here is part of the table:
> > CREATE TABLE [Surveys] (
> > [SurveyID] [int] IDENTITY (1, 1) NOT NULL ,
> > [InsuredRef] [varchar] (8) NULL ,
> > [Location] [smallint] NULL ,
> > [Sequence] [smallint] NULL
> > ) ON [PRIMARY]
> >
> > I am working in VB6 using ADO. I need to insert a record overriding the
> > identity column. Here is the essential paraphrased bits of the code:
> >
> > '--------------------- set identity_insert on
> > Set cmd = New ADODB.Command
> > Set cmd.ActiveConnection = adoCon
> > cmd.CommandText = "SET IDENTITY_INSERT Surveys ON"
> > On Error Resume Next
> > cmd.Execute
> > If Err.Number <> 0 Then
> > MsgBox "Error on line 'SET IDENTITY_INSERT Surveys ON'. Please
> > contact Technical Support." & vbCrLf & Err.Number & " " & Err.Description,
> > vbOKOnly Or vbCritical, "Error!"
> > End If
> > On Error GoTo ErrHandler
> > Set cmd = Nothing
> > DoEvents 'give it a chance to sink in
> >
> > That part works okay. Here is where I get the error:
> >
> > Set cmd = New ADODB.Command
> > Set cmd.ActiveConnection = adoCon
> > cmd.CommandText = "INSERT INTO SURVEYS
> > (SurveyID,InsuredRef,Location,Sequence) VALUES (?,?,?,?)
> > cmd.parameters(0) = 123
> > cmd.parameters(1) = 'abc'
> > cmd.parameters(2) = 1
> > cmd.parameters(3) = 2
> > cmd.Execute
> > Set cmd = Nothing
> >
> > Now, I've read that you have to have the IDENTITY_INSERT statment in the
> > same "session" as the insert statement. I don't know what the definition
> > of
> > "session" is, but we are using the same connection on sequential
> > statements.
> > I tried combining the statements separated by a space or vbCrLf as I've
> > seen
> > on other sites, but get an error due to the parameters (no way to get
> > around
> > those). This is the only IDENTITY_INSERT column anywhere in my tables and
> > the
> > only spot in the code where the IDENTITY_INSERT is changed. No other apps
> > are
> > messing with it either.
> >
> > How can this be done in ADO?
> > Has something recently changed in MSDE that broke this (as I said it's
> > been
> > working until a few days ago)?
> > Does IDENTITY_INSERT need to be set OFF or is it done implicitly?
> >
> > Thanks in advance!
> >
> >
>
>
>
- Next message: Ted W9999: "Re: IDENTITY_INSERT error"
- Previous message: M Smith: "Re: Error using JET_SCHEMA_USERROSTER"
- In reply to: Val Mazur: "Re: IDENTITY_INSERT error"
- Next in thread: Ted W9999: "Re: IDENTITY_INSERT error"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
