Error message: Cannot insert explicit value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error message: Cannot insert explicit value

Author

Topic

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-25 : 08:26:40

Hello Everyone,

I am trying to insert a new record into a sql table and I get the following error message:

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'tbl_MLS_ReportCriteria' when IDENTITY_INSERT is set to OFF.

Here is the code:


USE MLS
INSERT INTO dbo.tbl_MLS_ReportCriteria (CriteriaID, Name, DisplayName, DetailedInstructions, TypeID, Source, DefaultValue, ValueField, DescriptionField, IncludeAllOption)
VALUES ('65', 'Summary3', 'Tertiary Summarize By', NULL, '2', 'SelectOptions', 'Company', NULL, NULL, '0')

What do I need to change and or look for?

TIA

Kurt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-25 : 08:28:32

Is criteraid your identity column?
If so, do not insert into that column...

Peter Larsson
Helsingborg, Sweden

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-25 : 08:33:32

Hello Peso,

I am learning this on the fly obviously.

Is the identity column also the primary key column?

I see Identity, Identity Seed, and Identity Increment in the properites for CriteriaID should Identity be set to False in order to insert a value?

TIA

Kurt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-25 : 08:35:34

You can do that with SET IDENTITY_INSERT {ON | OFF} command before inserting, if you really mean force an insertion of a identity value. Books Online is your very best friend!

Peter Larsson
Helsingborg, Sweden

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-25 : 08:38:29

Hello Peso,

TIA with your help on the Excel question as well. I am not trying to skirt the issue. I just check the other link you sent me and I like this connection string:

oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"Integrated Security=SSPI"

I will try it later.

Priorities have been changed in my daily work routine here.

As always thanks for your prompt and courteous answers.

Have a great day!

Kurt

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-25 : 08:45:48

Hello Peso,

Here is the code


USE MLS
SET IDENTITY_INSERT ON
INSERT INTO dbo.tbl_MLS_ReportCriteria (CriteriaID, Name, DisplayName, DetailedInstructions, TypeID, Source, DefaultValue, ValueField, DescriptionField, IncludeAllOption)
VALUES ('65', 'Summary3', 'Tertiary Summarize By', NULL, '2', 'SelectOptions', 'Company', NULL, NULL, '0')

I get this error message:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'ON'.

TIA

Kurt

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-25 : 08:55:47

Hello Peso and Everyone,

I am now trying this code:


USE MLS
SET IDENTITY_INSERT dbo.tbl_MLS_ReportCriteria ON
INSERT INTO dbo.tbl_MLS_ReportCriteria (CriteriaID, Name, DisplayName, DetailedInstructions, TypeID, Source, DefaultValue, ValueField, DescriptionField, IncludeAllOption)
VALUES ('65', 'Summary3', 'Tertiary Summarize By', NULL, '2', 'SelectOptions', 'Company', NULL, NULL, '0')

And I am getting this error message:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_tbl_MLS_ReportCriteria'. Cannot insert duplicate key in object 'tbl_MLS_ReportCriteria'.
The statement has been terminated.

(0 row(s) affected)

TIA

Kurt

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-25 : 09:16:01

Hello EVeryone,

The winner is:


INSERT INTO dbo.tbl_MLS_ReportCriteria(Name, DisplayName, DetailedInstructions, TypeID, Source, DefaultValue, ValueField, DescriptionField, IncludeAllOption)
VALUES ('Summary3', 'Tertiary Summarize By', NULL, '2', 'SelectOptions', 'Company', NULL, NULL, '0')

Got it!!

Kurt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-25 : 09:22:00

Great work!
Don't forget to switch back the IDENTITY_INSERT flag.

Peter Larsson
Helsingborg, Sweden

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-25 : 09:32:55

Hello Peso,

THANKS!!

Kurt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources