Petapoco update datetime error

Question

I was trying to update a few datetime field using Petapoco, but the update always fail so I look up my SQL profiler and found the following execution statement.

    exec sp_executesql N'UPDATE [db_Product] SET [aName] = @0, [aDesc] = @1, [EmpID] = @2, [UPCCode] = @3, [isActive] = @4, [isExpendable] = @5, [ProductCatID] = @6, [ProductSubCatID] = @7, [PromoCatID] = @8, 
    [Property1Name] = @9, [Property2Name] = @10, [RackPrice] = @11, [DiscountMethod] = @12, [DiscountAmount] = @13, [SellingPrice] = @14, [Cost] = @15, [CurrencyType] = @16, [qtyPerSaleMin] = @17, 
    [qtyPerSaleMax] = @18, [SupplierID] = @19, [BrandID] = @20, [StartDate] = @21, [EndDate] = @22, [MadeIn] = @23, [TakeStockName] = @24, [LifeCycleName] = @25, [ReOrderQty] = @26, [ReOrderPoint] = @27, 
    [ReOrderDays] = @28, [PreOrderDays] = @29, [aMemo] = @30, [Updator] = @31, [DateUpdate] = @32 WHERE [ProductID] = @33',N'@0 nvarchar(4000),@1 nvarchar(4000),@2 int,@3 nvarchar(4000),@4 int,@5 int,@6 int,@7 
    int,@8 int,@9 nvarchar(4000),@10 nvarchar(4000),@11 decimal(4,4),@12 nvarchar(4000),@13 decimal(4,4),@14 decimal(4,4),@15 decimal(4,4),@16 nvarchar(4000),@17 int,@18 int,@19 int,@20 int,@21 datetime,@22 
    datetime,@23 nvarchar(4000),@24 nvarchar(4000),@25 nvarchar(4000),@26 int,@27 int,@28 int,@29 int,@30 nvarchar(4000),@31 int,@32 datetime,@33 
    int',@0=N'testxxx',@1=N'',@2=0,@3=N'',@4=0,@5=0,@6=1,@7=0,@8=0,@9=N'Color',@10=N'Size',@11=0,@12=N'%',@13=0,@14=0,@15=0,@16=N'TWD',@17=0,@18=0,@19=0,@20=0,

@21=''2013-01-20 12:28:00:000'',@22=''2063-01-20 12:28:00:000'', <--problem

@23=N'TW ',@24=N'週盤',@25=N'新品',@26=1,@27=1,@28=3,@29=1,@30=N'',@31=0,

@32=''2013-02-04 02:45:47:640'' <----problem

,@33=1

for some reason, all the datetime fields have '' and thus causes error in update.

Edit: C# code, BAL

public int Update(int _id, ProductManager _M)
{
    int result = _M.Product.UpdateByID(_id, _M.Product);

    return result;

}

C# code, DAL

public virtual int UpdateByID(int _id, db_Product data)
{
    data.DateUpdate = DateTime.Now;
     using (var db = new ConnClass().ConnDB())
     {
         var result = db.Update(data, _id);
         return result;
     }
}

The BAL _M.Product.UpdateByID(_id, _M.Product); send the update to the DAL code for update. I checked the values in my ProductManager _M, the dateTime Values are valid.

Petapoco Execute command

    UPDATE [db_Product] SET [aName] = @0, [aDesc] = @1, [EmpID] = @2, [UPCCode] = @3, [isActive] = @4, [isExpendable] = @5, [ProductCatID] = @6, [ProductSubCatID] = @7, 
[PromoCatID] = @8, [Property1Name] = @9, [Property2Name] = @10, [RackPrice] = @11, [DiscountMethod] = @12, [DiscountAmount] = @13, [SellingPrice] = @14, [Cost] = @15, [CurrencyType] = @16, [qtyPerSaleMin] = @17, [qtyPerSaleMax] = @18, [SupplierID] = @19, [BrandID] = @20, 
[StartDate] = @21, [EndDate] = @22, [MadeIn] = @23, [TakeStockName] = @24, [LifeCycleName] = @25, [ReOrderQty] = @26, [ReOrderPoint] = @27, [ReOrderDays] = @28, [PreOrderDays] = @29, 
[aMemo] = @30, [Updator] = @31, [DateUpdate] = @32 WHERE [ProductID] = @33

I already spent a few days debugging this funny outcome....

In the end I solve the error. Actually there is no error, the data is indeed written to the database. But PetaPoco gave the update return int as -1 no matter it is a successful update or not. It seems to be a bug or something. — Hami, Feb 12 '13 at 09:48

score 1 · Accepted Answer · answered Mar 27 '13 at 16:41

Petapoco runs the UPDATE statement using ADO.net ExecuteNonQuery() and returns the value returned by this function.

The documentation of this function states:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

Source: MSDN

So I cannot know if an UPDATE is successful or not since if it returns -1 for both a single row update and a rollback? isn't it confusing? — Hami, Jun 06 '13 at 02:37

Petapoco update datetime error

1 Answers1