Re: Can and Can't insert using DAO on SQL Server linked table

From: Mark A. Sam (msam_at_Plan-It-Earth.Net)
Date: 08/28/03 

Date: Thu, 28 Aug 2003 08:28:21 -0400

David,

Thank you for your reply. This isn't behaving as you describe, becuase
sometimes I am able to insert (especially on my test procedure) over and
over without an issue.

I added this particular Autonumber field to allow updates to the table after
upsizing, otherwise it has no purpose. There is no chance that I am writing
it and it doesn't have a default. I can add records to the table when it is
opened in the Enterprise manager, even when I can't insert through code, and
it will increment.

I will try the passthrough query. I modifed it for my table... set
identity_insert DBO.[Order Entry St Materials] off;
Is that correct? Testing didn't give an error. I'll let you know how it
went.

Thanks again and God Bless,

Mark

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:es$8vBPbDHA.1872@TK2MSFTNGP12.phx.gbl...
> Using JET 4, an append or insert that attempts to write a value
> into the Identity field will set Identity Insert on. The insert
> will succeed, and after that the autonumber won't work properly
> until the Identity Insert is turned off again.
>
> There is also a property that controls if JET tries to write
> to all fields, or just to fields that have changed.
>
> "Initialization Settings for Jet-connected ODBC Databases"
> "PreparedInsert: An indicator of whether to use a prepared INSERT
statement
> that inserts data in all columns. Values are 0 (use a custom INSERT
> statement that inserts only non-Null values) and 1 (use a prepared INSERT
> statement). The default is 0 (values are of type REG_DWORD).Using prepared
> INSERT statements can cause Nulls to overwrite server defaults and can
cause
> triggers to execute on columns that weren't inserted explicitly."
>
>
> I suspect that you are writing a 0 to the Identity field: after that
> it is not autonumber until Identity Insert is turned off again, and
> you get an error the second time because you are writing another 0
> (duplicate index) to the identity field. I don't know if you are
> writing the 0 because of (a) coding error (b) JET bug (c) Non-default
> value of PreparedInsert or (d) other.
>
> You can use a pass-through query to reset the Identity Insert Property
> "set identity_insert DBO.tblYC_YieldCurve off;"
>
> Also, you may find that inserting into a different table may reset
> this property: or you may find that inserting into a table may
> prevent identity insert on other tables.
>
> I am not aware of any documentation of this behaviour.
> I am not aware of any documentation of the change between Jet 3 and 4.
> I don't know if this varies between different releases of Jet 4.
>
> If you find any documentation, or reach any conclusions, please let me
know!
>
> (david)
>
>
> "Mark A. Sam" <msam@Plan-It-Earth.Net> wrote in message
> news:%233M3MPEbDHA.3248@tk2msftngp13.phx.gbl...
> > David,
> >
> > > 1) Which Version? A97/Jet 3.5? A2K/Jet 4? (they handle this
> differently)
> >
> > 4.0
> >
> > > 2) If Identity Insert is on for the table, it won't generate
AutoNumber
> > ID's
> >
> > I don't understand what you mean. How do I check that property?
Identity
> > is set to Yes for the Primary Key which is autoincrementing.
> >
> > > 3) If you Insert an Identity value, it will turn Identity Insert on,
and
> > > it won't generate autonumber ID's until you turn it off again.
> >
> > I guess I need to know how to Turn Indentity Insert to Off.
> >
> > God Bless,
> >
> > Mark
> >
> >
>
>

Relevant Pages

  • Re: @@Identity
    ... like 6 posts ago in this thread. ... This value will auto increment when a new row is ... >> into a table that has an identity field. ... >> If you are inserting a row with your ADO.Net code that will increment ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: @@Identity
    ... like 6 posts ago in this thread. ... This value will auto increment when a new row is inserted ... > into a table that has an identity field. ... > If you are inserting a row with your ADO.Net code that will increment the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Duplicate key error
    ... "David C" wrote in message ... > Identity field will it automatically set the SEED, or will I have to do ... Now to get your inferior workaround to actually work, ... >> must wrap the select maxand INSERT in a serializable transaction. ...
    (microsoft.public.sqlserver.programming)
  • get error when turn on identity_insert on table
    ... I am trying to insert data from one table into another which has primary key ... as Identity field. ... I did turn on the identity_insert on before inserting ... Han, ...
    (microsoft.public.sqlserver.programming)
  • Insert with response
    ... I am inserting a new record into my table that has an identity field. ... How do I get the Applicant ID back - there could be more that one Tom Smith ...
    (microsoft.public.sqlserver.programming)