ASP.NET

when IDENTITY_INSERT is set to OFF. -- LINQ RSS

6 replies

Last post Apr 16, 2009 04:01 AM by greatnaqvi

‹ Previous Thread|Next Thread ›

- Reply
pentup

0 Points

3 Posts
when IDENTITY_INSERT is set to OFF. -- LINQ

Jan 21, 2008 05:45 PM|LINK
I'm new to ASP/VS/Linq and I'm having a small problem.

I have one table setup in SQL Server Express 2005 through Visual Studio 2008. The table name is "Users" and has three columns (accountID, userName, email). AccountID is the primary key and set to auto incriment. I've added a couple of records by hand and it works.

I have a single form with a button, a label, and two text boxes. The button code is below. After entering some fake data that does not already exist in the database and clicking the button I get this.

Cannot insert explicit value for identity column in table 'Users' when IDENTITY_INSERT is set to OFF.

I understand that it is trying to insert something into the accountID field but I don't understand why since I'm only providing a username and e-mail address to insert.

Your help is greatly appreciated.
```
protected void Button1_Click(object sender, EventArgs e)
        {
            MyDatabaseDataContext db = new MyDatabaseDataContext();
            var query = from u in db.Users
                        where u.email == txtEmail.Text
                        select u;


            var count = query.Count();
            if (count == 0)
            {
                //Create a new user object.
                User newUser = new User();
                

                newUser.username = txtUsername.Text;
                newUser.email = txtEmail.Text;


                //Add the user to the User table.
                db.Users.InsertOnSubmit(newUser);
                db.SubmitChanges();
            }
            else
            {
                Label1.Text = txtEmail.Text + " already exists in the database.";
            }
```
- Moved by joteke on Jan 21, 2008 05:48 PM
- Edited by pentup on Jan 21, 2008 06:33 PM
- Edited by pentup on Jan 21, 2008 06:46 PM
- Edited by pentup on Jan 21, 2008 11:40 PM
- Reply
pentup

0 Points

3 Posts

Re: when IDENTITY_INSERT is set to OFF. -- LINQ

Jan 22, 2008 02:28 AM|LINK

This is driving me crazy. Any LINQ guru's out there?
- Reply
Wencui Qian ...

All-Star

56784 Points

5796 Posts

Microsoft

Re: when IDENTITY_INSERT is set to OFF. -- LINQ

Jan 23, 2008 02:07 AM|LINK

Hi pentup,

It is a strange issue as all of your codes run well on my computer. So I think you maybe have some issues when creating the table. Please check the table definition, and if convenient, you can re-create the table to see whether the table has any problems. This is the my .sql scripts to create the table.

go

create table Users

(

accountID int primary key identity(1,1),

userName char(20),
email char(30)
)

insert into Users select 'woshiccye','woshicc@gmail.com'

insert into Users select 'mmzhang','mm@hotmail.com'

insert into Users select 'qian','wencui@hotmail.com'

go

Hope my suggestion helps you!

Please mark the replies as answers if they help or unmark if not.

If you have any feedback about my replies, please contact msdnmg@microsoft.com

Microsoft One Code Framework
- Reply
pentup

0 Points

3 Posts

Re: when IDENTITY_INSERT is set to OFF. -- LINQ

Jan 23, 2008 02:59 PM|LINK

Thanks for your reply but I found the answer on another forum.

Check the primary key on the mapping class. It should have an attribute something like this:

<div class=codeseg> <div class=codecontent> <div class=codesniptitle>[Column(Storage="_FileID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]</div>The important bit is the IsDbGenerated=true. If your attribute lacks that, we have found the error! </div></div>

If you're using the designer, just regenerate this class (by deleting the table in the designer and dropping it from the server explorer).
- Marked as answer by pentup on Jan 23, 2008 02:59 PM
- Reply
BFreakout

Member

2 Points

1 Post

Re: when IDENTITY_INSERT is set to OFF. -- LINQ

Apr 22, 2008 08:24 PM|LINK

[Yes] Thank you!!!!! i have the self problem and your post help me...
- Reply
henesse

Member

2 Points

1 Post

Re: when IDENTITY_INSERT is set to OFF. -- LINQ

Mar 18, 2009 04:28 PM|LINK

If you made changes to the table in the db after you created your table in the server explorer (*.dbml file)

You need to drop the table from the designer and add it back in to the server explorer.
- Reply
greatnaqvi

Member

2 Points

1 Post

Re: when IDENTITY_INSERT is set to OFF. -- LINQ

Apr 16, 2009 04:01 AM|LINK

U have made a table and map into linq.. file for example db.html. then after that u go into database and set identity yes... and not refresh the db.html

refresh linq file after making changes in database.. then u will not find this problem..

Regards

Aftab Hussain

Assistant Manager IT