3

yeah another one of these question. I have looked here but to no avail..
this is my situation.
i started a ASP site, and initially was using SQLce. This turned out to be bad as the hoster didn't support it. I converted the sdf file to SQL server 2012 db via the Web Matrix method. The database is all good, and everything can read the data. the problem comes in when i try to write anything to a table. VS comes up saying:
Cannot insert explicit value for identity column in table 'Groups' when IDENTITY_INSERT is set to OFF.
Now.. it was all working sweet before. here is an example of a tale i have set up: 

Column1: ID - Int - Primary key. - Identity specification yes, with a increment of 1  
Column2: GroupName - nvarchar(100) - just a normal text field

then. where is the code it chokes on: 

        using (SLEntities context = new SLEntities())  
        {  
            var namecheck = (from n in context.Groups  
                             where n.GroupName == newname  
                             select n).Count();  

            if (namecheck > 0)  
            {  
                // the name already exists.. return  
                lab_Error.Text = newname + " is already in the database. Please use another name";  
                return;  
            }  

            // namecheck is new.. add to db  
            Group g = new Group();  
            g.GroupName = newname;  
            context.Groups.Add(g);  
            context.SaveChanges();  
        }

it dies on the savechanges().
My question is how do i fix this? (i seen the IDENTITY_INSERT ON thing, but isnt that when you only create the table?).. or, instead of using and auto increment ID, what is another way ?

podiluska
  • 50,950
  • 7
  • 98
  • 104
Tony
  • 53
  • 2
  • 7

2 Answers2

4

Go to your datamodel and verify that the "StoreGeneratedPattern" property for the identity column is effectively set to "Identity".

Jan Van Herck
  • 2,254
  • 17
  • 15
0

while creating the table in DB if you have putted IDENTITY_INSERT on any column then you do not need to provide value for that column when inserting data from code .

for example :-

CREATE TABLE dummy(id int identity(1,1), data varchar(100))

Insert into dummy values('First row')// it will work 

Insert into dummy values(1,'First row')// it will give error

so better way is while inserting data from code mention column name also

like 

Insert into dummy(data) values('First row')

OR else you can do like (:-This will allow you to insert value into identity columns)

SET IDENTITY_INSERT dummy ON
INSERT INTO dummy(id, data) VALUES(3, 'garden shovel')

you should check this article first :- http://msdn.microsoft.com/en-us/library/aa259221(v=sql.80).aspx

Pranav
  • 8,563
  • 4
  • 26
  • 42