-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 
Author Message
 Post subject: Cannot insert explicit value...IDENTITY_INSERT is set to OFF
PostPosted: Fri Mar 14, 2008 10:39 pm 
Newbie

Joined: Wed Feb 20, 2008 8:08 pm
Posts: 10
I am getting the following error when I try to create a new record:

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

Any help would be really appreciated as I am going round in circles with this.

Thanks,

Brendan

------------------------------------------------------------------------------
-- HIBERNATE VERSION: 2.2
------------------------------------------------------------------------------

------------------------------------------------------------------------------
-- MAPPING FILE
------------------------------------------------------------------------------
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false">
  <class name="HughGRice.Portal.Core.Models.Gallery, HughGRice.Portal.Core" table="Gallery">
      <id name="Id" type="Int32" unsaved-value="0">
         <column name="Id" length="4" sql-type="int" not-null="true" unique="true" index="PK_Gallery"/>
         <generator class="identity" />
      </id>
      <property name="Name" type="String">
         <column name="Name" length="150" sql-type="nvarchar" not-null="false"/>
      </property>
      <property name="Telephone" type="String">
         <column name="Telephone" length="50" sql-type="nvarchar" not-null="false"/>
      </property>
      <property name="URL" type="String">
         <column name="URL" length="50" sql-type="nvarchar" not-null="false"/>
      </property>
      <property name="Email" type="String">
         <column name="Email" length="50" sql-type="nvarchar" not-null="false"/>
      </property>
      <many-to-one name="Address" class="HughGRice.Portal.Core.Models.Address, HughGRice.Portal.Core">
         <column name="Id" length="4" sql-type="int" not-null="true" unique="true" index="PK_Gallery"/>
      </many-to-one>
      <bag name="GalleryPaintings" inverse="true" lazy="true" cascade="all-delete-orphan">
         <key column="GalleryId"/>
         <one-to-many class="HughGRice.Portal.Core.Models.Painting, HughGRice.Portal.Core"/>
      </bag>
   </class>
</hibernate-mapping>


------------------------------------------------------------------------------
-- SQL TABLE DEFINITION
------------------------------------------------------------------------------
Code:
USE [hughgrice]
GO
/****** Object:  Table [dbo].[Gallery]    Script Date: 03/15/2008 01:58:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Gallery](
   [Id] [int] IDENTITY(1,1) NOT NULL,
   [Name] [nvarchar](150) NULL,
   [Telephone] [nvarchar](50) NULL,
   [URL] [nvarchar](250) NULL,
   [Email] [nvarchar](50) NULL,
   [AddressId] [int] NULL,
CONSTRAINT [PK_Gallery] PRIMARY KEY CLUSTERED
(
   [Id] 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
ALTER TABLE [dbo].[Gallery]  WITH CHECK ADD  CONSTRAINT [FK_Gallery_Address] FOREIGN KEY([AddressId])
REFERENCES [dbo].[Address] ([Id])
GO
ALTER TABLE [dbo].[Gallery] CHECK CONSTRAINT [FK_Gallery_Address]


Top
 Profile  
 
 Post subject:
PostPosted: Sat Mar 15, 2008 7:23 am 
Expert
Expert

Joined: Thu Dec 14, 2006 5:57 am
Posts: 1185
Location: Zurich, Switzerland
Sounds like you assign an id to your object before you sve it. When you use id generator "identity" you can not do that. Post your code to clarify that.

_________________
--Wolfgang


Top
 Profile  
 
 Post subject:
PostPosted: Sat Mar 15, 2008 8:58 am 
Newbie

Joined: Wed Feb 20, 2008 8:08 pm
Posts: 10
Hi Wolli,

thanks for the reply. Please find the code below. I cannot see anywhere where I am assigning an Id and when the creat method is called the Id is pass through as 0.

Any ideas?

Thanks again for the help.

Brendan


------------------------------------------------------
Controller Class
------------------------------------------------------
Code:
        [AccessibleThrough(Verb.Post)]
        public void AddGallery([DataBind("galleryToAdd")] Gallery galleryToAdd)
        {
            if (galleryToAdd == null)
            {
                throw new NullReferenceException("The gallery parameter cannot be null.");
            }

            if (string.IsNullOrEmpty(galleryToAdd.Name))
            {
                galleryToAdd.Name = "";
                _errors.Add("The gallery name is required.");
            }

            if (_errors.Count > 0)
            {
                Gallery[] galleries = _galleryService.FindAll();
                Painting[] paintings = _paintingService.FindAll();
                AddGalleriesForPaintings(paintings, galleries);

                PropertyBag.Add("galleryToDelete", new Gallery());
                PropertyBag.Add("galleryToAdd", galleryToAdd);
                PropertyBag.Add("galleries", galleries);
                PropertyBag.Add("paintings", paintings);
                PropertyBag.Add("paintingTypes", _paintingService.FindAllPaintingTypes());

                Flash.Add("errors", _errors.ToArray(typeof (string)));

                RenderView("index");

                return;
            }

            _galleryService.Create(galleryToAdd);

            Flash.Add("success", "The gallery was successfully added.");

            RenderView("index");
        }



------------------------------------------------------
Service Class
------------------------------------------------------
Code:
      public void Create(Gallery gallery)
      {
            repository.BeginTransaction();
            repository.Create(gallery);
            repository.CommitTransaction();
      }


------------------------------------------------------
Repository Class
------------------------------------------------------
Code:
   
        /// <summary>
        /// Begins an NHibernate transaction.
        /// </summary>
        public void BeginTransaction()
        {
            if (!_isSessionCreator)
            {
                throw new InvalidOperationException(
                    "A gateway that is sharing the session of another gateway cannot start a transaction on the shared session.");
            }

            if (!this.IsOpen)
            {
                throw new InvalidOperationException(
                    "Repository must be open before a transaction can be started on the gateway.");
            }

            if (_transaction == null)
            {
                _transaction = _session.BeginTransaction();
            }
        }

        /// <summary>
        /// Saves the given entity in the data store.
        /// </summary>
        /// <param name="entity">The entity to save.</param>
        /// /// <remarks>The entity is not actually saved until the transaction is committed.</remarks>
        public void Create(T entity)
        {
            if (!this.IsOpen)
            {
                throw new InvalidOperationException("Repository must be open before an entity can be saved.");
            }

            if (_transaction == null)
            {
                throw new InvalidOperationException("Saves must be done within a transaction.");
            }

            _session.Save(entity);
        }

        /// <summary>
        /// Commits an NHibernate transaction.
        /// </summary>
        public void CommitTransaction()
        {
            if (!_isSessionCreator)
            {
                throw new InvalidOperationException(
                    "A gateway that is sharing the session of another gateway cannot commit a transaction on the shared session.");
            }

            if (!this.IsOpen)
            {
                throw new InvalidOperationException("Repository must be open before a transaction can be committed.");
            }

            if (_transaction == null)
            {
                throw new InvalidOperationException("Repository must have an open transaction in order to commit.");
            }

            // Do the commit then kill the transaction
            _transaction.Commit();
            _transaction = null;
           
        }



Top
 Profile  
 
 Post subject:
PostPosted: Sat Mar 15, 2008 9:18 am 
Newbie

Joined: Wed Feb 20, 2008 8:08 pm
Posts: 10
I have just ran SQL Profiler and it looks like it doesn't like the 0 being set for the ID.

Code:
exec sp_executesql N'INSERT INTO Gallery (Name, Telephone, URL, Email, Id) VALUES (@p0, @p1, @p2, @p3, @p4); select SCOPE_IDENTITY()',N'@p0 nvarchar(17),@p1 nvarchar(4000),@p2 nvarchar(62),@p3 nvarchar(4000),@p4 int',@p0=N'Woodlands Gallery',@p1=NULL,@p2=N'http://www.woodlandsgallery.com/dynamic/artist.asp?ArtistID=41',@p3=NULL,@p4=0



This alternative SQL query will insert the record. It just leaves out any reference to the ID.

Code:
exec sp_executesql N'INSERT INTO Gallery (Name, Telephone, URL, Email) VALUES (@p0, @p1, @p2, @p3); select SCOPE_IDENTITY()',N'@p0 nvarchar(17),@p1 nvarchar(4000),@p2 nvarchar(62),@p3 nvarchar(4000)',@p0=N'Woodlands Gallery',@p1=NULL,@p2=N'http://www.woodlandsgallery.com/dynamic/artist.asp?ArtistID=41',@p3=NULL

How would I replicate this funstionality in the hbm.xml file.

Thanks again for the help,

B


Top
 Profile  
 
 Post subject:
PostPosted: Sat Mar 15, 2008 9:31 am 
Expert
Expert

Joined: Thu Dec 14, 2006 5:57 am
Posts: 1185
Location: Zurich, Switzerland
Shouldn't the column in the many-to-one assoiation to address be "AddressId" ? I think hibernate tries to store the address's id in your id column.

_________________
--Wolfgang


Top
 Profile  
 
 Post subject:
PostPosted: Sat Mar 15, 2008 9:40 am 
Newbie

Joined: Wed Feb 20, 2008 8:08 pm
Posts: 10
Good spot Wolli I would have been tearing my hair out over that.

Code:
exec sp_executesql N'INSERT INTO Gallery (Name, Telephone, URL, Email, AddressId) VALUES (@p0, @p1, @p2, @p3, @p4); select SCOPE_IDENTITY()',N'@p0 nvarchar(17),@p1 nvarchar(4000),@p2 nvarchar(62),@p3 nvarchar(4000),@p4 int',@p0=N'Woodlands Gallery',@p1=NULL,@p2=N'http://www.woodlandsgallery.com/dynamic/artist.asp?ArtistID=41',@p3=NULL,@p4=0



The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Gallery_Address". The conflict occurred in database "hughgrice", table "dbo.Address", column 'Id'.
The statement has been terminated.


Top
 Profile  
 
 Post subject:
PostPosted: Sat Mar 15, 2008 9:46 am 
Newbie

Joined: Wed Feb 20, 2008 8:08 pm
Posts: 10
Here is the address mapping...

Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false">
   <class name="HughGRice.Portal.Core.Models.Address, HughGRice.Portal.Core" table="Address" >
      <id name="Id" type="Int32" unsaved-value="null">
         <column name="Id" length="4" sql-type="int" not-null="true" unique="true" index="PK_Address"/>
         <generator class="identity" />
      </id>
      <property name="AddressLine1" type="String">
         <column name="AddressLine1" length="200" sql-type="nvarchar" not-null="false"/>
      </property>
      <property name="AddressLine2" type="String">
         <column name="AddressLine2" length="200" sql-type="nvarchar" not-null="false"/>
      </property>
      <property name="Country" type="String">
         <column name="Country" length="50" sql-type="nvarchar" not-null="false"/>
      </property>
      <property name="PostalCode" type="String">
         <column name="PostalCode" length="10" sql-type="nvarchar" not-null="false"/>
      </property>
    <one-to-one class="HughGRice.Portal.Core.Models.Gallery, HughGRice.Portal.Core" name="Galleries"/>
   </class>

</hibernate-mapping>


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.