Developer forum

Seems it posted twice :-(

You say that you chose Shop1, but the XML says Shop2. Have you had a look in Shop2?

Sorensen wrote:

You say that you chose Shop1, but the XML says Shop2. Have you had a look in Shop2?

I checked the Order table, and it's completely empty, I'm trying to import the Orders on a "fresh" developer solutions.
 

I used the Pipeline "Export orders from DB to XML" to get my XML.

I remove the nodes that dont validate against the XML schema for order import xml.

I edited the ShopIDs in my XML to match my solutions IDs.

but when i run "Import orders from XML to DB" nothing is importet.

That's probably why, then. The format is pretty strick, so don't change it. If you need to change data, use an XSL Transformation activity to do the modifications. You might want to check out the "Part 2" video on this page for details: http://developer.dynamicweb.dk/Tech-Review-2008-2-28453.aspx

Sorensen wrote:

That's probably why, then. The format is pretty strick, so don't change it. If you need to change data, use an XSL Transformation activity to do the modifications. You might want to check out the "Part 2" video on this page for details: http://developer.dynamicweb.dk/Tech-Review-2008-2-28453.aspx

There are no errors in my XML. It validates against the XML schema for order XML.

well. atleast i get no errors at all when i import.

You can't import XML exported via the "orders DB to XML" pipeline directly, since the format is not valid for "orders XML to DB".

For instance OrderShippingMethodDescription and OrderPaymentMethodDescription arent allowed.

I got the following error:

Order destination Process error: Error: The element 'OrderLine' has invalid child element 'OrderLineDiscountID'.

Removed the node from the XML, and it the import ran successfully. Even created the shop for me. Are you saying that you got this XML from eCommerce, and it refuces to import it?

I should mention that the order does not appear in the order view of the shop, but it is saved in the database.

Sorensen wrote:

I got the following error:

Order destination Process error: Error: The element 'OrderLine' has invalid child element 'OrderLineDiscountID'.

 

 

Removed the node from the XML, and it the import ran successfully. Even created the shop for me. Are you saying that you got this XML from eCommerce, and it refuces to import it?

yes.

I removed all invalid elements, and nothing is imported.

Did you import my XML into your Ecom?

Yes, I copied it directly.

Okay, i tried not selecting a shop in the "Order destination" step. and now me orders imported correctly.

I was hoping that importing the same XML again would just update the already importet order. but that wasn't the case.

Any thoughts on how i can easyly change the state of an order, after i run the "Order DB to XML", say to "Exported" instead of "New"?

It should update existing and create new orders based on the OrderID. Just noticed that it is a cart you posted the XML for (OrderID starts with CART, not ORDER). That's probably why it didn't show in my order view;)

Sorensen wrote:

It should update existing and create new orders based on the OrderID. Just noticed that it is a cart you posted the XML for (OrderID starts with CART, not ORDER). That's probably why it didn't show in my order view;)

I've given up on trying to use the standard integration to update my orders, instead i've begun making a custom activities, but i get a time out everytime i try to change an orders state.

I think i'm done with the Activity, but i get a timeout when i run it.

For every order in my input i run this function:

Where my function argument is the specific orderID of the current order.

private void ChangeState( string OrderID ) {
  // Find specific order
  Dynamicweb.eCommerce.Orders.Order order = new Dynamicweb.eCommerce.Orders.Order( OrderID );
  if ( order != null ) {
 // Update and change OrderstateID and save
 order.StateID = OrderStateID.ToString();
 order.Save( OrderID );
  }
}

When the line is "order.Save( OrderID );" is run i get this error:
 

Besked : Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.
Kilde : .Net SqlClient Data Provider
Stack trace : at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet) at Dynamicweb.eCommerce.Orders.Order.Save(String IDStr) at Co3.DW.ImportExport.Activities.OrderstateUpdater.ChangeState(String OrderID) in C:\Users\mn\Documents\SourceSafe\Co3\Dynamicweb\Co3.DW.ImportExport.Activities\Co3.DW.ImportExport.Activities\OrderstateUpdater.cs:line 68 at Co3.DW.ImportExport.Activities.OrderstateUpdater.Process() in C:\Users\mn\Documents\SourceSafe\Co3\Dynamicweb\Co3.DW.ImportExport.Activities\Co3.DW.ImportExport.Activities\OrderstateUpdater.cs:line 50 at Dynamicweb.Integration.Activity.Next() at Dynamicweb.Integration.Activity.Next() at Dynamicweb.Integration.Pipeline.Process() at Dynamicweb.Admin.Integration.PipelineRunner.Page_Load(Object sender, EventArgs e)
 

What happens when you open an order in admin, change the state and save it? It's the same code used here and when you use the API. What happens when you attempt to update an order record in the EcomOrders or an orderline record in EcomOrderLines directly on the SQL server?

It's a bit freaky that you receive an exception as it's a rather simple operation that is being performed. Could it be that you have a billion records with the specified EcomOrderID in the database og a bad index on the server?

Sorensen wrote:

What happens when you open an order in admin, change the state and save it? It's the same code used here and when you use the API. What happens when you attempt to update an order record in the EcomOrders or an orderline record in EcomOrderLines directly on the SQL server?

 

It's a bit freaky that you receive an exception as it's a rather simple operation that is being performed. Could it be that you have a billion records with the specified EcomOrderID in the database og a bad index on the server?

There is no problems when updating via the eCom interface. so yeah.. it's weird.
 

Do you have the opportunity to set up SQL Server Profiler to see, what we're actually telling the server to do?

Sorensen wrote:

Do you have the opportunity to set up SQL Server Profiler to see, what we're actually telling the server to do?

Hi Lars,
 

I tried running a profiler and logged what was executed when i updated an order via the eCom interface, and what ran when i started my pipeline.

You can download the history here http://test.co3.dk/martin/Profiler_Import-export.zip.

WHERE statement is insane!!

Oh, I forgot that you were working on your own activity, so I was comparing with the statement I received in the Profiler:)

Why does it feel the need to match all fields in the row? Don't you just:

SELECT * FROM EcomOrders WHERE OrderID = 'something'

And then update the dataset? How are your key column definitions on the EcomOrders table?

Sorensen wrote:

Oh, I forgot that you were working on your own activity, so I was comparing with the statement I received in the Profiler:)

 

Why does it feel the need to match all fields in the row? Don't you just:

SELECT * FROM EcomOrders WHERE OrderID = 'something'

 

And then update the dataset? How are your key column definitions on the EcomOrders table?

I'm using the dynamic API to update, so i dont knpw what happens behind the scene.

this could be an example of what i'd call in my activity

Dynamicweb.eCommerce.Orders.Order order = new Dynamicweb.eCommerce.Orders.Order( "ORDER1" );

        // Update and change OrderstateID and save
        order.StateID = "OS55"
        order.Save( OrderID ); // or order.Save(); makes no difference

Sorensen wrote:

Oh, I forgot that you were working on your own activity, so I was comparing with the statement I received in the Profiler:)

 

Why does it feel the need to match all fields in the row? Don't you just:

SELECT * FROM EcomOrders WHERE OrderID = 'something'

 

And then update the dataset? How are your key column definitions on the EcomOrders table?

I'm not really sure what you mean bykey column definitions but maybe this answers your question, here's the "CREATE to" sql for the table.

/****** Object:  Table [dbo].[EcomOrders]    Script Date: 01/26/2009 12:20:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EcomOrders](
    [OrderID] [nvarchar](50) NOT NULL,
    [OrderShopID] [nvarchar](255) NULL,
    [OrderDate] [datetime] NULL,
    [OrderModified] [datetime] NULL,
    [OrderComplete] [bit] NULL CONSTRAINT [DF__EcomOrder__Order__355DD6AE]  DEFAULT ((0)),
    [OrderDeleted] [bit] NULL CONSTRAINT [DF__EcomOrder__Order__3651FAE7]  DEFAULT ((0)),
    [OrderStateID] [nvarchar](50) NULL,
    [OrderVAT] [float] NULL CONSTRAINT [DF__EcomOrder__Order__37461F20]  DEFAULT ((0)),
    [OrderIP] [nvarchar](255) NULL,
    [OrderReferrer] [nvarchar](255) NULL,
    [OrderTransactionValue] [nvarchar](2) NULL,
    [OrderTransactionType] [nvarchar](50) NULL,
    [OrderTransactionStatus] [nvarchar](50) NULL,
    [OrderTransactionAmount] [float] NULL CONSTRAINT [DF__EcomOrder__Order__383A4359]  DEFAULT ((0)),
    [OrderTransactionPayGatewayCode] [nvarchar](4) NULL,
    [OrderTrackTraceNumber] [nvarchar](50) NULL,
    [OrderShippingMethod] [nvarchar](50) NULL,
    [OrderShippingMethodFee] [float] NULL CONSTRAINT [DF__EcomOrder__Order__392E6792]  DEFAULT ((0)),
    [OrderPaymentMethod] [nvarchar](50) NULL,
    [OrderPaymentMethodFee] [float] NULL CONSTRAINT [DF__EcomOrder__Order__3A228BCB]  DEFAULT ((0)),
    [OrderSalesDiscount] [float] NULL CONSTRAINT [DF__EcomOrder__Order__3B16B004]  DEFAULT ((0)),
    [OrderCurrencyName] [nvarchar](50) NULL,
    [OrderCurrencyRate] [float] NULL CONSTRAINT [DF__EcomOrder__Order__3C0AD43D]  DEFAULT ((0)),
    [OrderCurrencyCode] [nvarchar](10) NULL,
    [OrderCart] [bit] NULL CONSTRAINT [DF__EcomOrder__Order__3CFEF876]  DEFAULT ((0)),
    [OrderFieldsXML] [nvarchar](max) NULL,
    [OrderReSendEmail] [nvarchar](255) NULL,
    [OrderCustomerNumber] [nvarchar](255) NULL,
    [OrderCustomerCompany] [nvarchar](255) NULL,
    [OrderCustomerName] [nvarchar](255) NULL,
    [OrderCustomerAddress] [nvarchar](255) NULL,
    [OrderCustomerAddress2] [nvarchar](255) NULL,
    [OrderCustomerZip] [nvarchar](50) NULL,
    [OrderCustomerCity] [nvarchar](255) NULL,
    [OrderCustomerCountry] [nvarchar](50) NULL,
    [OrderCustomerRegion] [nvarchar](50) NULL,
    [OrderCustomerPhone] [nvarchar](50) NULL,
    [OrderCustomerFax] [nvarchar](50) NULL,
    [OrderCustomerEmail] [nvarchar](50) NULL,
    [OrderCustomerCell] [nvarchar](50) NULL,
    [OrderCustomerRefID] [nvarchar](255) NULL,
    [OrderCustomerEAN] [nvarchar](255) NULL,
    [OrderCustomerVatRegNumber] [nvarchar](50) NULL,
    [OrderDeliveryCompany] [nvarchar](255) NULL,
    [OrderDeliveryName] [nvarchar](255) NULL,
    [OrderDeliveryAddress] [nvarchar](255) NULL,
    [OrderDeliveryAddress2] [nvarchar](255) NULL,
    [OrderDeliveryZip] [nvarchar](50) NULL,
    [OrderDeliveryCity] [nvarchar](255) NULL,
    [OrderDeliveryCountry] [nvarchar](50) NULL,
    [OrderDeliveryRegion] [nvarchar](50) NULL,
    [OrderDeliveryPhone] [nvarchar](50) NULL,
    [OrderDeliveryFax] [nvarchar](50) NULL,
    [OrderDeliveryEmail] [nvarchar](50) NULL,
    [OrderDeliveryCell] [nvarchar](50) NULL,
    [OrderTotalPrice] [float] NULL CONSTRAINT [DF__EcomOrder__Order__3DF31CAF]  DEFAULT ((0)),
    [OrderComment] [nvarchar](max) NULL,
    [OrderCustomerComment] [nvarchar](max) NULL,
    [OrderWeight] [float] NULL,
    [OrderVolume] [float] NULL,
    [OrderPriceWithVAT] [float] NULL,
    [OrderPriceWithoutVAT] [float] NULL,
    [OrderPriceVAT] [float] NULL,
    [OrderPriceVATPercent] [float] NULL,
    [OrderShippingFeeWithVAT] [float] NULL,
    [OrderShippingFeeWithoutVAT] [float] NULL,
    [OrderShippingFeeVAT] [float] NULL,
    [OrderShippingFeeVATPercent] [float] NULL,
    [OrderPaymentFeeWithVAT] [float] NULL,
    [OrderPaymentFeeWithoutVAT] [float] NULL,
    [OrderPaymentFeeVAT] [float] NULL,
    [OrderPaymentFeeVATPercent] [float] NULL,
    [OrderPriceBeforeFeesWithVAT] [float] NULL,
    [OrderPriceBeforeFeesWithoutVAT] [float] NULL,
    [OrderPriceBeforeFeesVAT] [float] NULL,
    [OrderPriceBeforeFeesVATPercent] [float] NULL,
    [OrderCustomerAccessUserID] [int] NULL,
    [OrderCustomerAccessUserUserName] [nvarchar](255) NULL,
    [OrderShippingMethodID] [nvarchar](50) NULL,
    [OrderPaymentMethodID] [nvarchar](50) NULL,
    [OrderGatewayResult] [ntext] NULL,
    [OrderStepNum] [int] NULL,
    [OrderTransactionNumber] [nvarchar](255) NULL,
    [OrderCustomerCountryCode] [nvarchar](50) NULL,
    [OrderDeliveryCountryCode] [nvarchar](50) NULL,
    [OrderStepHistory] [ntext] NULL,
    [OrderLanguageID] [nvarchar](25) NULL,
    [OrderTransactionMailSend] [bit] NOT NULL DEFAULT ((0)),
    [OrderShippingMethodDescription] [nvarchar](max) NULL,
    [OrderPaymentMethodDescription] [nvarchar](max) NULL,
    [OrderCustomerNewsletterSubcribe] [bit] NOT NULL DEFAULT ((0)),
 CONSTRAINT [EcomOrders$PrimaryKey] PRIMARY KEY NONCLUSTERED
(
    [OrderID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[EcomOrders]  WITH NOCHECK ADD  CONSTRAINT [EcomOrders$EcomOrderStatesEcomOrders] FOREIGN KEY([OrderStateID])
REFERENCES [dbo].[EcomOrderStates] ([OrderStateID])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[EcomOrders] CHECK CONSTRAINT [EcomOrders$EcomOrderStatesEcomOrders]

This is very weird. Are you getting the same result in other environments? If this is not hosted by us, I'd like you to send me the following, so I can simulate. Otherwise, let me know the details of the website:

 - The CustomModules VS solution

 - The standard Dynamicweb application you use for this solution

 - A backup of the SQL database

Sorensen wrote:

This is very weird. Are you getting the same result in other environments? If this is not hosted by us, I'd like you to send me the following, so I can simulate. Otherwise, let me know the details of the website:

 

 - The CustomModules VS solution

 - The standard Dynamicweb application you use for this solution

 - A backup of the SQL database

Do you have a mail adress i can send it to.

I'm not interested in posting everything on the forum.
 

If any of you have been following this thread, we found what caused the timeout.
 

It turned out my ordre table had some bad indexes, so to fix this, i deleted my orders and created some new ones.

Thanks again Lars.