SQL update or deletion failed (row not found)

nfung · **Joined:** Mon May 30, 2005 11:20 pm **Posts:** 66

Hi, I keep getting:

SQL update or deletion failed (row not found)

When I try to delete a record... I can't figure out what's wrong with my code:

Code:

sess = connManager.GetWarehouseNHibernateSession(True)
            trx = sess.BeginTransaction()

            zInv = sess.Load(GetType(warehouse.to.zInventory), inv_id, LockMode.None)

            If Not zInv Is Nothing Then
               If PerformAdditionalChecks(...) Then
                  trx.Rollback()

                  Return DataManager.ERROR_ZINVENTORY_DELETE_OUTSTANDING_SWATCHES
               End If
            Else
               trx.Rollback()

               Return DataManager.ERROR_ZINVENTORY_DELETE_ITEMNOTFOUND
            End If


            'STEP 2. Remove from Database
            sess.Delete(zInv)
            sess.Flush() [b]'Every time I flush I get ADOException??[/b]

            trx.Commit()

Help guys! Thanks in advance.

nfung · **Joined:** Mon May 30, 2005 11:20 pm **Posts:** 66

Hey I still can't solved this... But, is this why?

Quote:

MS SQL Server with JSQLConnect driver throws exception with delete()

Hibernate 2.0.1 propagates an exception from Session.flush() following Session.delete(Object):

net.sf.hibernate.HibernateException: SQL update or deletion failed (row not found)
at net.sf.hibernate.impl.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:25)

NonBatchingBatcher.addToBatch expects PreparedStatement.executeUpdate() to return 1, but it returns 0 in this case, even though it has deleted 1 row from the database.

I solved this problem by appending "/lastUpdateCount=true" to the JDBC url for JSQLConnect. This makes the JDBC driver behave as Hibernate expects. Without this property, it seems the driver returned the number of rows updated by a trigger that was executed as a side-effect of the requested change. See http://www.j-netdirect.com/JSQLConnect/ ... ctSettings for more information.

Reference: http://www.google.ca/url?sa=t&ct=res&cd=5&url=http%3A//www.hibernate.org/74.html%3Fcmd%3Dcomphist%26histnode%3D436&ei=sgEUQ8DbI7WaiAKl4rHODg

So, does it has to do with lastUpdateCount? How do I configure this though? Thanks in advance!

nfung · **Joined:** Mon May 30, 2005 11:20 pm **Posts:** 66

I dont see how lastUpdateCount can be configured for NHibernate...

Reference: http://www.google.ca/url?sa=t&ct=res&cd ... iAKuhfHGDg

nfung · **Joined:** Mon May 30, 2005 11:20 pm **Posts:** 66

1. Grand parent zInventory

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="warehouse.to.zInventory, WarehouseLib" table="zInventory">
      <id name="Inv_ID" column="inv_id" type="Int32" unsaved-value="0" > 
         <generator class="identity" /> 
      </id> 
      <property name="Article_NO" column= "article_no" type="String" length="20" /> 
      <property name="InventoryCategoryID" column= "inv_category_id" type="Int16" />
      <property name="Color_NO" column= "color_no" type="String" length="20" />
      <property name="AT_Color_NO" column= "at_color_no" type="String" length="20" />
      <property name="Inv_Desc" column= "inv_desc" type="String" length="500" />
      <property name="Status_ID" column="status_id" type="Int16" />
      <property name="Sample_Copy" column="sample_copy" type="BinaryBlob" />
      <property name="Remarks" column= "remarks" type="String" length="100" />
      
      <set name="Swatches" inverse="true" cascade="none" table="inv_swatches" lazy="false">
         <key column="inv_id"/>
         <one-to-many class="warehouse.to.inv_swatches, WarehouseLib"/>
      </set>
      
      <property name="CreateBy" column= "CreateBy" type="Int32"/>
      <property name="CreateDate" column= "CreateDate" type="DateTime"/>
      <property name="LastUpdateBy" column= "LastUpdateBy" type="Int32"/>
      <property name="LastUpdateDate" column= "LastUpdateDate" type="DateTime"/>
   </class>
</hibernate-mapping>

2. Parent Swatch

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="warehouse.to.inv_swatches, WarehouseLib" table="inv_swatches">
      <id name="SwatchID" column="swatch_id" type="Int32" unsaved-value="0" >
         <generator class="identity" /> 
      </id>
      
      <many-to-one name="ParentInventoryMaster" column="inv_id" class="warehouse.to.zInventory, WarehouseLib" not-null="true" />
      
      <property name="DeliveryID" column="delivery_id" type="Int32" />
      <property name="UOMID" column= "uom_id" type="Int16" />
      <property name="RunningItem" column="running_item" type="Boolean" />
      <property name="StatusID" column="status_id" type="Int16" />
      <property name="Remarks" column="remarks" type="String" length="2147483647" />
      
      <property name="CreateBy" column= "CreateBy" type="Int32"/>
      <property name="CreateDate" column= "CreateDate" type="DateTime"/>
      <property name="OwnerGroup" column= "OwnerGroupUIN" type="Int32"/>
      <property name="LastUpdateBy" column= "LastUpdateBy" type="Int32"/>
      <property name="LastUpdateDate" column= "LastUpdateDate" type="DateTime"/>
      
      <set name="Items" inverse="true" cascade="all" table="inv_item" lazy="false">
         <key column="swatch_id"/>
         <one-to-many class="warehouse.to.inv_item, WarehouseLib"/>
      </set>
   </class>
</hibernate-mapping>

3. Child InvRequest

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="warehouse.to.InvRequest, WarehouseLib" table="inv_request">
   
      <id name="RequestID" column="request_id" type="Int32" unsaved-value="0" >
         <generator class="identity" />
      </id>
      
      <property name="RequestedBy" column="requested_by" type="Int32" />
      <property name="DeliverTo" column="deliver_to" type="String" length="100" />
      <property name="DeliverDate" column="deliver_date" type="DateTime" />
      <property name="InvAdjust" column="inv_adjust" type="Boolean" />
      <property name="Remarks" column="remarks" type="String" length="2147483647" />
      
      <property name="CreateBy" column= "CreateBy" type="Int32"/>
      <property name="CreateDate" column= "CreateDate" type="DateTime"/>
      <property name="OwnerGroup" column= "OwnerGroupUIN" type="Int32"/>
      <property name="LastUpdateBy" column= "LastUpdateBy" type="Int32"/>
      <property name="LastUpdateDate" column= "LastUpdateDate" type="DateTime"/>
      
      <set name="RequestItems" inverse="true" cascade="all" table="inv_request_item">
         <key column="request_id"/>
         <one-to-many class="warehouse.to.inv_request_item, WarehouseLib"/>
      </set>
   </class>
</hibernate-mapping>

nfung · **Joined:** Mon May 30, 2005 11:20 pm **Posts:** 66

help guys! inverse=true/false --- could this be why?

http://nhibernate.sourceforge.net/forum ... .php?t=653

nfung · **Joined:** Mon May 30, 2005 11:20 pm **Posts:** 66

I was looking at NonBatchingBatcher code ...

Code:

using System;
using System.Data;
using NHibernate.Engine;
namespace NHibernate.Impl 
{
   /// <summary>
   /// An implementation of the <c>IBatcher</c> inteface that does no batching
   /// </summary>
   internal class NonBatchingBatcher : BatcherImpl 
   {
      
      public NonBatchingBatcher(ISessionImplementor session) : base(session) 
      { 
      }

      public override void AddToBatch(int expectedRowCount) 
      {
         int rowCount = GetCommand().ExecuteNonQuery();

         //negative expected row count means we don't know how many rows to expect
         if ( expectedRowCount>0 && expectedRowCount!=rowCount ) 
         {
            throw new HibernateException("SQL update or deletion failed (row not found)");
         }
      }

      protected override void DoExecuteBatch(IDbCommand ps) 
      {
      }

   }
}

Perhaps I should "SET ROWCOUNT OFF"

But how can I do that with NHibernate?

Reference: http://www.google.ca/url?sa=t&ct=res&cd ... 24aqiuxeAN

nfung · **Joined:** Mon May 30, 2005 11:20 pm **Posts:** 66

took out children (so, there's no more collection mapping and child/parent issue here) from mapping, still have this problem:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="warehouse.to.zInventory, WarehouseLib" table="zInventory">
   
      <id name="Inv_ID" column="inv_id" type="Int32" [b]unsaved-value="0"[/b] > 
         <generator class="identity" /> 
      </id> 
      
      <property name="Article_NO" column= "article_no" type="String" length="20" /> 
      <property name="InventoryCategoryID" column= "inv_category_id" type="Int16" />
      <property name="Color_NO" column= "color_no" type="String" length="20" />
      <property name="AT_Color_NO" column= "at_color_no" type="String" length="20" />
      <property name="Inv_Desc" column= "inv_desc" type="String" length="500" />
      <property name="Status_ID" column="status_id" type="Int16" />
      <property name="Sample_Copy" column="sample_copy" type="BinaryBlob" />
      <property name="Remarks" column= "remarks" type="String" length="100" />
      
      <property name="CreateBy" column= "CreateBy" type="Int32"/>
      <property name="CreateDate" column= "CreateDate" type="DateTime"/>
      <property name="LastUpdateBy" column= "LastUpdateBy" type="Int32"/>
      <property name="LastUpdateDate" column= "LastUpdateDate" type="DateTime"/>
      
   </class>
</hibernate-mapping>

The code to execute delete is as follows:

Code:

sess = connManager.GetWarehouseNHibernateSession(True)
            trx = sess.BeginTransaction()

            zInv = sess.Load(GetType(warehouse.to.zInventory), inv_id, LockMode.None)
            
            sess.Delete(zInv)
            sess.Flush() [b]'Exception here! It says "Row not found"[/b]

            trx.Commit() [b]'If I comment out the transaction the selected record will still be deleted [b]successfully [/b]even though exception is raised on Flush()[/b]

I need help guys! It's been a drain and it is becoming to have real impact on my schedule ...

KPixel · **Posted:** Fri Sep 02, 2005 6:51 am

I don't know if it is related to your problem, but you should Commit() instead of Flush()...
Flush() is done internally and should be done manually only when you really need to force it.

nfung · **Joined:** Mon May 30, 2005 11:20 pm **Posts:** 66

I think this depends on your flush mode. Anyway, I think if you set Flush mode to "Auto" then changes are flushed upon commit or closing of current session.

But, I tried both. I tried committing trx or closing session WITHOUT flushing, in that case, it failed on the closing or commit statement instead.

Notice that if I flush WITHOUT a transaction wrapping around the flush statement, the changes would actually be committed to database even though exception was raised at flush statement!

KPixel · **Posted:** Sun Sep 04, 2005 11:46 am

Can you post the content of your log file ? (specifically the whole exception...)

nfung · **Joined:** Mon May 30, 2005 11:20 pm **Posts:** 66

I can't find the log file! But anyway, let's deal with that later. The captured exception is as follows:

1. Exception at "Flush()"

Code:

Server Error in '/WarehouseWeb' Application.
--------------------------------------------------------------------------------

SQL update or deletion failed (row not found) 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: NHibernate.HibernateException: SQL update or deletion failed (row not found)

Source Error: 


Line 804:            'STEP 2. Update
Line 805:            sess.Update(zInv_before_update)
Line 806:            sess.Flush()
Line 807:
Line 808:            trx.Commit()
 

Source File: c:\inetpub\wwwroot\WarehouseWeb\utilities\DataManager.vb    Line: 806 

Stack Trace: 


[HibernateException: SQL update or deletion failed (row not found)]
   NHibernate.Impl.NonBatchingBatcher.AddToBatch(Int32 expectedRowCount)
   NHibernate.Persister.EntityPersister.Update(Object id, Object[] fields, Boolean[] includeProperty, Object oldVersion, Object obj, SqlString sqlUpdateString, ISessionImplementor session)
   NHibernate.Persister.EntityPersister.Update(Object id, Object[] fields, Int32[] dirtyFields, Object oldVersion, Object obj, ISessionImplementor session)
   NHibernate.Impl.ScheduledUpdate.Execute()
   NHibernate.Impl.SessionImpl.ExecuteAll(ICollection coll) +100
   NHibernate.Impl.SessionImpl.Execute() +83

[ADOException: could not synchronize database state with session]
   NHibernate.Impl.SessionImpl.Execute() +223
   NHibernate.Impl.SessionImpl.Flush() +23
   warehouse.web.utilities.DataManager.UpdateInventoryMaster(zInventory zInv) in c:\inetpub\wwwroot\WarehouseWeb\utilities\DataManager.vb:806

[ExApplicationException]
   warehouse.web.utilities.DataManager.UpdateInventoryMaster(zInventory zInv) in c:\inetpub\wwwroot\WarehouseWeb\utilities\DataManager.vb:827
   warehouse.web.dataentry.InventoryMaster.btnUpload_Click(Object sender, ImageClickEventArgs e) in c:\inetpub\wwwroot\WarehouseWeb\pages\desktop\dataentry_forms\InventoryMaster.aspx.vb:1052

[ExApplicationException]
   warehouse.web.dataentry.InventoryMaster.btnUpload_Click(Object sender, ImageClickEventArgs e) in c:\inetpub\wwwroot\WarehouseWeb\pages\desktop\dataentry_forms\InventoryMaster.aspx.vb:1086
   System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e)
   System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
   System.Web.UI.Page.ProcessRequestMain() +1277

 


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573 

**************************************************
2. IF I commented out "Flush()" - in which case, you get the same exception but it fails on "Commit()" statement instead.

Code:

Server Error in '/WarehouseWeb' Application.
--------------------------------------------------------------------------------

SQL update or deletion failed (row not found) 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: NHibernate.HibernateException: SQL update or deletion failed (row not found)

Source Error: 


Line 806:            'sess.Flush()
Line 807:
Line 808:            trx.Commit()
Line 809:         Catch ex As SqlClient.SqlException
Line 810:            If Not trx Is Nothing Then
 

Source File: c:\inetpub\wwwroot\WarehouseWeb\utilities\DataManager.vb    Line: 808 

Stack Trace: 


[HibernateException: SQL update or deletion failed (row not found)]
   NHibernate.Impl.NonBatchingBatcher.AddToBatch(Int32 expectedRowCount)
   NHibernate.Persister.EntityPersister.Update(Object id, Object[] fields, Boolean[] includeProperty, Object oldVersion, Object obj, SqlString sqlUpdateString, ISessionImplementor session)
   NHibernate.Persister.EntityPersister.Update(Object id, Object[] fields, Int32[] dirtyFields, Object oldVersion, Object obj, ISessionImplementor session)
   NHibernate.Impl.ScheduledUpdate.Execute()
   NHibernate.Impl.SessionImpl.ExecuteAll(ICollection coll) +100
   NHibernate.Impl.SessionImpl.Execute() +83

[ADOException: could not synchronize database state with session]
   NHibernate.Impl.SessionImpl.Execute() +223
   NHibernate.Impl.SessionImpl.Flush() +23
   NHibernate.Transaction.Transaction.Commit() +101
   warehouse.web.utilities.DataManager.UpdateInventoryMaster(zInventory zInv) in c:\inetpub\wwwroot\WarehouseWeb\utilities\DataManager.vb:808

[ExApplicationException]
   warehouse.web.utilities.DataManager.UpdateInventoryMaster(zInventory zInv) in c:\inetpub\wwwroot\WarehouseWeb\utilities\DataManager.vb:827
   warehouse.web.dataentry.InventoryMaster.btnUpload_Click(Object sender, ImageClickEventArgs e) in c:\inetpub\wwwroot\WarehouseWeb\pages\desktop\dataentry_forms\InventoryMaster.aspx.vb:1052

[ExApplicationException]
   warehouse.web.dataentry.InventoryMaster.btnUpload_Click(Object sender, ImageClickEventArgs e) in c:\inetpub\wwwroot\WarehouseWeb\pages\desktop\dataentry_forms\InventoryMaster.aspx.vb:1086
   System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e)
   System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
   System.Web.UI.Page.ProcessRequestMain() +1277

 


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573 

Hope you'd be able to see what I failed to ...

nfung · **Joined:** Mon May 30, 2005 11:20 pm **Posts:** 66

Hi, FYI:

1. Database schema:

Code:

CREATE TABLE [zInventory] (
   [inv_id] [int] IDENTITY (1, 1) NOT NULL ,
   [article_no] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [inv_category_id] [smallint] NOT NULL ,
   [color_no] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [at_color_no] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [inv_desc] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [status_id] [smallint] NOT NULL ,
   [sample_copy] [image] NULL ,
   [remarks] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [CreateBy] [int] NOT NULL ,
   [CreateDate] [smalldatetime] NOT NULL ,
   [LastUpdateBy] [int] NOT NULL ,
   [LastUpdateDate] [smalldatetime] NOT NULL ,
   CONSTRAINT [PK_zInventory] PRIMARY KEY  CLUSTERED 
   (
      [inv_id]
   ) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
   CONSTRAINT [FK_zInventory_zInvCategory] FOREIGN KEY 
   (
      [inv_category_id]
   ) REFERENCES [zInvCategory] (
      [inv_category_id]
   ),
   CONSTRAINT [FK_zInventory_zStatus] FOREIGN KEY 
   (
      [status_id]
   ) REFERENCES [zStatus] (
      [status_id]
   )
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

2. Mapping BEFORE (With parent/child relation -- ie. zInventory = parent of Swatch)

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="warehouse.to.zInventory, WarehouseLib" table="zInventory">
   
      <id name="Inv_ID" column="inv_id" type="Int32" unsaved-value="null" > 
         <generator class="identity" /> 
      </id> 
      
      <property name="Article_NO" column= "article_no" type="String" length="20" /> 
      <property name="InventoryCategoryID" column= "inv_category_id" type="Int16" />
      <property name="Color_NO" column= "color_no" type="String" length="20" />
      <property name="AT_Color_NO" column= "at_color_no" type="String" length="20" />
      <property name="Inv_Desc" column= "inv_desc" type="String" length="500" />
      <property name="Status_ID" column="status_id" type="Int16" />
      <property name="Sample_Copy" column="sample_copy" type="BinaryBlob" />
      <property name="Remarks" column= "remarks" type="String" length="100" />
      
      <set name="Swatches" inverse="true" cascade="all" table="inv_swatches" lazy="false">
         <key column="inv_id"/>
         <one-to-many class="warehouse.to.inv_swatches, WarehouseLib"/>
      </set>
      
      <property name="CreateBy" column= "CreateBy" type="Int32"/>
      <property name="CreateDate" column= "CreateDate" type="DateTime"/>
      <property name="LastUpdateBy" column= "LastUpdateBy" type="Int32"/>
      <property name="LastUpdateDate" column= "LastUpdateDate" type="DateTime"/>
      
   </class>
</hibernate-mapping>

3. mapping AFTER (Parent/child relation betweeen zInventory and Swatch - for sake of simplification and to narrow down possible cause to this problem...)

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="warehouse.to.zInventory, WarehouseLib" table="zInventory">
   
      <id name="Inv_ID" column="inv_id" type="Int32" unsaved-value="0" > 
         <generator class="identity" /> 
      </id> 
      
      <property name="Article_NO" column= "article_no" type="String" length="20" /> 
      <property name="InventoryCategoryID" column= "inv_category_id" type="Int16" />
      <property name="Color_NO" column= "color_no" type="String" length="20" />
      <property name="AT_Color_NO" column= "at_color_no" type="String" length="20" />
      <property name="Inv_Desc" column= "inv_desc" type="String" length="500" />
      <property name="Status_ID" column="status_id" type="Int16" />
      <property name="Sample_Copy" column="sample_copy" type="BinaryBlob" />
      <property name="Remarks" column= "remarks" type="String" length="100" />
      
      <property name="CreateBy" column= "CreateBy" type="Int32"/>
      <property name="CreateDate" column= "CreateDate" type="DateTime"/>
      <property name="LastUpdateBy" column= "LastUpdateBy" type="Int32"/>
      <property name="LastUpdateDate" column= "LastUpdateDate" type="DateTime"/>
      
   </class>
</hibernate-mapping>

Thanks in advance ...

Dan · **Joined:** Thu Aug 25, 2005 5:05 am **Posts:** 6

Are you using MS SQL Server and triggers (before or after)?

I havent tested it with some of the later versions of NHibernate, but with 6.0 (I think it was) I couldnt get anything to work because for some reason the actions of the triggers add to the number of rows changed. So you never got the right number of rows updated/deleted.

nfung · **Joined:** Mon May 30, 2005 11:20 pm **Posts:** 66

Yes you're right. It's the triggers... I tried NHIbernate 0.6.0.0 and 0.9.1.0 --- BOTH have the same problem.... *** sigh ***

Any suggestion guys?

Cepheus · **Joined:** Fri May 20, 2005 5:06 am **Posts:** 28

Try in the trigger: SET NOCOUNT ON

Maybe that helps.