Explicit error in sqlserver

Question

0.00/5 (No votes)

See more:

HI I Have a stored procedure to insert datas from c#.When i create a stored Procedure it throw an error.The error is given below

An explicit value for the identity column in table 'tbl_seat' can only be specified when a column list is used and IDENTITY_INSERT is ON.

SQL

create procedure sp_insert_seat
@A1 varchar(5),@A2 varchar(5),@A3 varchar(5),@A4 varchar(5),@A5 varchar(5),@A6 varchar(5),@A7 varchar(5),@A8 varchar(5),@A9 varchar(5),@A10 varchar(5),@A11 varchar(5),@A12 varchar(5),@A13 varchar(5),@A14 varchar(5),@A15 varchar(5),@A16 varchar(5),@A17 varchar(5),@A18 varchar(5),@A19 varchar(5),@A20 varchar(5),
@S1 varchar(5),@S2 varchar(5),@S3 varchar(5),@S4 varchar(5),@S5 varchar(5),@S6 varchar(5),@S7 varchar(5),@S8 varchar(5),@S9 varchar(5),@S10 varchar(5),@S11 varchar(5),@S12 varchar(5),@S13 varchar(5),@S14 varchar(5),@S15 varchar(5),@S16 varchar(5),@S17 varchar(5),@S18 varchar(5),@S19 varchar(5),@S20 varchar(5),
@id int
as
begin
insert into tbl_seat values(@id,@A1,@A2,@A3,@A4,@A5 ,@A6 ,@A7 ,@A8 ,@A9 ,@A10 ,@A11 ,@A12 ,@A13,@A14 ,@A15 ,@A16 ,@A17 ,@A18 ,@A19 ,@A20,
@S1,@S2,@S3 ,@S4 ,@S5 ,@S6 ,@S7 ,@S8 ,@S9 ,@S10 ,@S11 ,@S12 ,@S13 ,@S14 ,@S15 ,@S16 ,@S17 ,@S18 ,@S19 ,@S20)

end

What I have tried:

I tried the "Set Identiy_insert tbl_name ON"

I try this method but also it throws the error .

Posted 4-Jan-17 2:11am

Member 12857356

Updated 4-Jan-17 8:53am

Add a Solution

Comments

Andy Lanng 4-Jan-17 8:39am

If the id column is an identity column then why are you inserting an id?

Member 12857356 4-Jan-17 8:42am

No,It is another Id am gettting from other table.

Andy Lanng 4-Jan-17 8:44am

Ah, what you're doing above will insert into the first column unless you specify the columns
insert into tbl_seat(col_id, col_2, col3, ...)
values (@is,@A1,@A2, ...)

Member 12857356 4-Jan-17 8:45am

No the first Column is identity column

Member 12857356 4-Jan-17 8:49am

thank you Got a answer

2 solutions

Solution 2

I know that you've already tackled the problem at hand but when looking at the procedure and the table definition, I can't help of wondering if the normalization level is sufficient or not...

What I mean is that if the situation is that you have 20 seats that can be reserved or free (or similar) and you have modeled the table so that each seat is a separate column then I think you would benefit from going a bit further in normalizing the structure.

An example would be to store each reservation in a separate row, like:

seat_reservation
- id (pointing to another, parent table)
- seatno (value between 1 and 20)
- reservation (the current parameter value if needed)

Now, if seats 1, 4, 5, and 17 are reserved the table would contain only those four rows.

This kind of design would make it easy to investigate which seats are reserved/free, make changes to seat count, implement reservation logic without procedural programming, and so on, again taken that I interpreted the situation correctly...

Posted 4-Jan-17 8:53am

Wendelius

Comments

Maciej Los 4-Jan-17 15:03pm

5ed!

Wendelius 5-Jan-17 15:17pm

Thank you.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2017-01-04T02:50:00

IDENTITY columns are controlled by SQL Server, and you should not normally write to them at all - it defeats the purpose of an IDENTITY column, which is to ensure that all values are unique. While you can do it for some occasional and exceptional tasks (such as removing gaps after a DB has been INSERTED and DELETED for a long time) even I wouldn't recommend it - it has be be considered very, very carefully as IDENTITY columns are often used as row ID columns and referenced via foreign keys from other tables. Just changing IDENTITY columns can very easily associate the wrong information with the row.

If @ID is a value for an identity column, then you are getting it from somewhere - and the chances are that you have "pre-allocated" the value by getting the max existing and adding one. That's dangerous - because it is far, far too easy to get two different users pre-allocate the same value and then your database goes completely out of order.
Don't do it. Use an IDENTITY column, never write to it, and fetch the id number back once you have INSERTED the row using the SCOPE_IDENTITY() function.

And do yourself two favours:
1) Always list the columns you are INSERTING into:

SQL

INSERT INTO MyTable (MyColumn1, MyColumn2) VALUES (@MyValueForColumn1, @MyValueForCoumn2)

If you don't then either your code breaks when columns are added or removed, or worse your database gets info in the wrong columns.
2) Use sensible names! A1 to A15, S1 to S20 aren't descriptive of what they doing, and that makes maintenance a lot harder later.