none
Entity Framework: How to add row data with supporting to identity column on? RRS feed

  • Question

  • Table:

    USE [TestDB]

    GO

    /****** Object:  Table [dbo].[tblEmployee]    Script Date: 12/28/2012 12:47:16 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[tblEmployee](

          [nID] [int] IDENTITY(1,1) NOT NULL,

          [nDeptID] [int] NULL,

          [sName] [varchar](20) NULL,

          [fSalary] [float] NULL,

     CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED

    (

          [nID] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    .........................................................................................................................................................................................

    Code:

    using (TestDBEntities objTestDBEntities = new TestDBEntities())

                    {

                       

                        objTestDBEntities.tblEmployees.AddObject(

                            new tblEmployee

                            {                           

                                sName = "Name1",

                                nDeptID = 5,

                                fSalary = 20000.0

                            });

                        objTestDBEntities.SaveChanges();                   

                     

                    }

    .........................................................................................................................................................................................

    Error: An error occurred while updating the entries. See the inner exception for details.

    Note: I want to add row data in table supporting with identity column auto increment on.

    I tried:

    StoredGeneratedPattern=none,Identity,computed..... but still shows same error.

    Could anyone help me?

    Friday, December 28, 2012 7:21 AM

Answers

  • Dear Dan,

    Thanks for reply,

    I have removed .edmx file and added again, now its working.

    Might be some fields had not updated, as you suggested.

    Thanks all of you

    Ashish

    Tuesday, January 1, 2013 6:46 AM

All replies

  • I don't see anything wrong with the current code, IMHO setting the StoreGeneratedPattern to Identity in the .edmx should work.

    Could you show the inner exception ? Hopefully that will give a clue.


    Hope this helps,

    Here to learn and share. Please tell if an answer was helpful or not at all. This adds value to the answers and enables me to learn more.

    About me

    Friday, December 28, 2012 8:52 AM
  • Your code don't have errors.

    Your table don't have changed? It can the motive


    Twitter: @MayogaX
    Blog: Dev Blog

    Friday, December 28, 2012 10:52 AM
  • Dear Mr KeesDijk,

    Thanks for reply.

    InnerException = {"Cannot insert explicit value for identity column in table 'tblEmployee' when IDENTITY_INSERT is set to OFF."}

    Is it necessary to set it OFF?

    if yes, How to do it in c#?

    I know how to do it in SQL, but don't want to touch my database?

    Thanks

    Ashish 

    Friday, December 28, 2012 11:28 AM
  • Hi,

    Identity insert can be off, should not be the problem, you are not trying to insert your own value for identity.

    What ever I try I cannot duplicate the error with your code.

    Could you please :

    - update the model from the database.

    - check that for tblEmployee the nID has Entity to true and has StoreGeneratedPattern to Identity

    See what happens.

    Which version of EF and which sql server are you using, if EF is not the latest trying the latest version might be an option.


    Hope this helps,

    Here to learn and share. Please tell if an answer was helpful or not at all. This adds value to the answers and enables me to learn more.

    About me


    • Edited by KeesDijk Friday, December 28, 2012 2:23 PM
    Friday, December 28, 2012 12:41 PM
  • Dear Mr KeesDijk,

    Thanks for reply.

     - I have updated the model from the database many times.

    - tblEmployee, the nID has Entity to true and has StoreGeneratedPattern to Identity

    But still have same error.

    I am using VS2010, EF version 4.0.0.0, and SQL Server 2008 R2

    Could u suggest some more ideas?

    Thanks

    Ashish


    Monday, December 31, 2012 8:50 AM
  • Ashish,

    Please verify that in your edmx file under:

    <EntityType Name="tblEmployee">
              <Key>
                <PropertyRef Name="nID" />
              </Key>
              <Property Name="nID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
    
    ...

    You need to have nID set up like this in order for the AppendObject method to ignore the identity field.

    If you altered your nID field to an Identity type, you need to right-click the designer view of the edmx file in VS and choose "Update model from database..."

    Then in the Update Wizard, choose the "Refresh" tab and click "Finish". This should change your SSDL xml in the edmx to enable identity field updates using server generated data.


    Dan Randolph - My Code Samples List

    Monday, December 31, 2012 10:48 PM
  • Dear Dan,

    Thanks for reply,

    I have removed .edmx file and added again, now its working.

    Might be some fields had not updated, as you suggested.

    Thanks all of you

    Ashish

    Tuesday, January 1, 2013 6:46 AM
  • Hi, I am having a similar problem with Identity - when I try and edit the edmx file and add the StoreGeneratedPattern Visual Studio 2008 says that I can't do that.  Any suggestions?

    Thanks, Neil

    Monday, May 6, 2013 6:41 PM