insert into a talbe with an ID that is set to autoincrement

[sparks]

The table only has 4 columns.
I am trying to insert 3 columns and I thought that the column
DisplayOrderID would auto increment and I didn't have to do anything
to it.

but I get this error.

Cannot insert the value NULL into column 'DisplayOrderID'

the column DisplayOrderID has these properties

Identity Specification Yes
(Is Identity) Yes
Identity Increment 1
Identity Seed 1
the default value or binding is empty.


I asked and they want it to be 1 to max autoincrement.

shouldn't this just start at 1 and increment 1 each record?

[Erland Sommarskog]

Post by sparks
The table only has 4 columns.
I am trying to insert 3 columns and I thought that the column
DisplayOrderID would auto increment and I didn't have to do anything
to it.
but I get this error.
Cannot insert the value NULL into column 'DisplayOrderID'
the column DisplayOrderID has these properties
Identity Specification Yes
(Is Identity) Yes
Identity Increment 1
Identity Seed 1
the default value or binding is empty.
I asked and they want it to be 1 to max autoincrement.
shouldn't this just start at 1 and increment 1 each record?

Have you indavertently run the command "SET IDENTITY_INSERT ON" for this
table?

What does your INSERT statement look like?

[sparks]

this is what I have.
copying data from another database

insert into [dbo].[DALookup](
[DrkID],[Doable],[Unit])
select
IONonId,Doable,Unit
from [merged_new].[dbo].[tblDAINFO]


and the only other variable in [dbo].[DALookup] is 'DisplayOrderID'


On Fri, 22 Jun 2012 17:10:38 +0200, Erland Sommarskog

Post by Erland Sommarskog

Post by sparks
The table only has 4 columns.
I am trying to insert 3 columns and I thought that the column
DisplayOrderID would auto increment and I didn't have to do anything
to it.
but I get this error.
Cannot insert the value NULL into column 'DisplayOrderID'
the column DisplayOrderID has these properties
Identity Specification Yes
(Is Identity) Yes
Identity Increment 1
Identity Seed 1
the default value or binding is empty.
I asked and they want it to be 1 to max autoincrement.
shouldn't this just start at 1 and increment 1 each record?

Have you indavertently run the command "SET IDENTITY_INSERT ON" for this
table?
What does your INSERT statement look like?

[Erland Sommarskog]

Post by sparks
this is what I have.
copying data from another database
insert into [dbo].[DALookup](
[DrkID],[Doable],[Unit])
select
IONonId,Doable,Unit
from [merged_new].[dbo].[tblDAINFO]
and the only other variable in [dbo].[DALookup] is 'DisplayOrderID'

So either DisplayOrderID is not an IDENTITY column (and in your previous
post you were looking in the wrong database or at the wrong table), or you
have IDENTITY_INSERT ON.

Do this:

SET IDENTITY_INSERT DALookup OFF

and try again. If this fails, run

sp_help DALookup

and review whether there actually is an identity column.

[sparks]

Thank you very much for your help.
I was working on this and said I did not have much luck.
It set off something.
DONT TOUCH THAT.
(I think they made a mess and trying to cover it up)
I was told about all kinds of admin stuff and other BS.
on the next thing it was a lookup table.
at least it was called that.
it had 3 variables and 3 primary keys.
was not tied to anything.
WT HECK IS THAT
again don't touch it.

So anyway I didn't do it and got finished LOL
man its a mess..but what isn't

thanks again I guess it was a friday.



On Fri, 22 Jun 2012 20:32:15 +0200, Erland Sommarskog

Post by Erland Sommarskog

Post by sparks
this is what I have.
copying data from another database
insert into [dbo].[DALookup](
[DrkID],[Doable],[Unit])
select
IONonId,Doable,Unit
from [merged_new].[dbo].[tblDAINFO]
and the only other variable in [dbo].[DALookup] is 'DisplayOrderID'

So either DisplayOrderID is not an IDENTITY column (and in your previous
post you were looking in the wrong database or at the wrong table), or you
have IDENTITY_INSERT ON.
SET IDENTITY_INSERT DALookup OFF
and try again. If this fails, run
sp_help DALookup
and review whether there actually is an identity column.