USA Epay Authorize/Capture Problem

[BryanWarmoth]

When an order is made it still says that there is a balance due. If I go into the admin area, then to the order and tell it to do an authorize/Capture it works just fine. Is there a setting that I missing to tell the order to do an Authorize/Capture when the customer places and order? The gateway is working correctly. When the customer places the order, the gateway process the credit card but the customer still see that a balance is due even though it has been taking out of their card.

Any Ideas?

[BryanWarmoth]

I looked in our error log and it seems that when the customer places the order we are getting an error that doesn't occur when we capture the payment from the admin area. The error is: String or binary data would be truncated. The statement has been terminated.

[BryanWarmoth]

UPDATE: I have found that when the order is placed the payment isn't happening at all. When I went to the admin area and went to the order and then payments, there were no payments at all. After the order goes through and it says there is still a balance due if i click Pay Now, then fill out the credit card information again the payment is then posted to the account but it doesn't finish because i run into the error: String or binary data would be truncated. The statement has been terminated. The payment still goes through to the gateway but AC doesn't pick it up. However, if I then go to the payments section of the order in the admin area and choose to authorize it, it works. I am very confused any help would be greatly appreciated.

[dnoell]

I am having the same issue with a PayPal authorization. It only happens in my case when a physical item is shipped. It does not happen with a subscription or electronic items that does not require shipping.

Any help would be appreciated.

[WylieE]

I've been helping Bryan debug this one.

We have found that when 'Enable Credit Card Storage' is unchecked, the error and order processing problem go away. We're not sure what is causing the problem at this time though.

[dnoell]

I'm getting this problem with "Enable Credit Card Storage" checked or unchecked. I do not have the problem if I purchase items that are non-shippable.

Is there an update I can do to the database to elimine the error message: "String or binary data would be truncated. The statement has been terminated." and have a order with shipping information go through?

Thanks.

[sohaib]

Hello,

1. Are you using a custom created payment gateway?

2. Which build are you using? Please post the details from Help->About

3. Can you please have a look at App_Data/Logs/app.log and see if there is any 'String or binary data would be truncated' exception logged. If so can you please post the details.

Thanks,
Sohaib

[dnoell]

1. I am using the PayPal gateway.

2. Here is the About Information:

PLATFORM: ASP.NET
VERSION: 7.0
BUILD: 9879

AjaxControlToolkit: 1.0.10301.0
CommerceBuilder: 7.0.9876.0
CommerceBuilder.AuthorizeNet: 7.0.9764.0
CommerceBuilder.CanadaPost: 7.0.0.0
CommerceBuilder.CCH: 7.0.0.0
CommerceBuilder.Configuration: 7.0.9842.0
CommerceBuilder.CyberSource: 7.0.9764.0
CommerceBuilder.DataClient.Api: 1.0.0.19
CommerceBuilder.DataClient.Csv: 1.0.0.0
CommerceBuilder.DHL: 7.0.8825.0
CommerceBuilder.ECB: 7.0.0.0
CommerceBuilder.FedEx: 7.0.9725.0
CommerceBuilder.FRBNY: 7.0.0.0
CommerceBuilder.GoogleCheckout: 7.0.9632.0
CommerceBuilder.InternetSecure: 7.0.9764.0
CommerceBuilder.LinkPoint: 7.0.9764.0
CommerceBuilder.Paradata: 7.0.9764.0
CommerceBuilder.PayFlowPro: 7.0.9764.0
CommerceBuilder.PayJunction: 7.0.9764.0
CommerceBuilder.PayPal: 7.0.9877.0
CommerceBuilder.Services: 7.0.9733.0
CommerceBuilder.SkipJack: 7.0.9764.0
CommerceBuilder.UPS: 7.0.8695.0
CommerceBuilder.USPS: 7.0.9728.0
CommerceBuilder.Web: 7.0.9777.0
ComponentArt.Web.UI: 2007.1.1566.3
CyberSource.Base: 2.0.0.0
CyberSource.Clients: 5.0.2.0
CyberSource.Clients.XmlSerializers: 5.0.2.0
CyberSource.WSSecurity: 2.0.0.0
CybsWSSecurityIOP: 1.0.0.0
edtFTPnet: 1.2.4.0
FredCK.FCKeditorV2: 2.5.2912.21007
ICSharpCode.SharpZipLib: 0.85.1.271
LinkPointTransaction: 1.0.1797.29766
log4net: 1.2.10.0
Microsoft.Practices.EnterpriseLibrary.Common: 2.0.0.0
Microsoft.Practices.EnterpriseLibrary.Data: 2.0.0.0
Microsoft.Practices.ObjectBuilder: 1.0.51205.0
Microsoft.Web.Preview: 1.2.61025.0
NVelocity: 7.0.8530.0
PaygatewayNET: 3.0.0.0
paypal_base: 4.2.1.0
PFProCOMLib: 1.0.0.0
PFProdotNET: 0.0.0.0
Validators: 1.0.0.0
WebChart: 1.1.1.6
wwHoverPanel: 1.85.0.0


3. Logs - Here is the log data from the latest occurance:

INFO 2008-07-07 12:53:09,083 1590365ms Object a - Running maintenance routines
INFO 2008-07-07 12:53:09,349 1590630ms Object a - Maintenance routines complete
WARN 2008-07-07 12:53:22,293 1603574ms Object a - Error sending email with subject 'Confirmation - Order Number 24'.
System.InvalidOperationException: The SMTP host was not specified.
at System.Net.Mail.SmtpClient.CheckHostAndPort()
at System.Net.Mail.SmtpClient.Send(MailMessage message)
at CommerceBuilder.Messaging.EmailClient.Send(MailMessage mailMessage, SmtpSettings smtpSettings, Boolean throwOnError)
INFO 2008-07-07 12:53:22,293 1603574ms APICaller Call - DoDirectPayment sent
INFO 2008-07-07 12:53:24,057 1605339ms APICaller Call - DoDirectPayment Ack : FailureWithWarning Elapsed Time : 764 ms
INFO 2008-07-07 12:53:24,057 1605339ms APICaller Call - DoDirectPayment Error : There's an error with this transaction. Please enter a valid postal code in the shipping address.
INFO 2008-07-07 12:53:24,057 1605339ms APICaller Call - DoDirectPayment Error : There's an error with this transaction. Please enter a valid state in the billing address.
INFO 2008-07-07 12:53:24,057 1605339ms APICaller Call - DoDirectPayment Error : There's an error with this transaction. Please enter a valid state in the shipping address.
ERROR 2008-07-07 12:53:24,135 1605417ms Object a - Unhandled exception during checkout.
System.Data.SqlClient.SqlException: String or binary data would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteScalar(DbCommand command)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteScalar(DbCommand command)
at CommerceBuilder.Payments.Transaction.Save()
at CommerceBuilder.Common.PersistentCollection`1.Save()
at CommerceBuilder.Payments.Payment.SaveChildren()
at CommerceBuilder.Payments.Payment.BaseSave()
at CommerceBuilder.Payments.Payment.Save()
at CommerceBuilder.Payments.PaymentEngine.a(AuthorizeTransactionRequest A_0, Boolean A_1)
at CommerceBuilder.Payments.PaymentEngine.DoAuthorize(AuthorizeTransactionRequest request)
at CommerceBuilder.Payments.Payment.Authorize(Boolean async, String remoteIP)

[dnoell]

UPDATE: I have been able to eliminate the error message, "String or binary data would be truncated.
The statement has been terminated." It appears that this is coming from the ac_Transactions table. If you update the column ResponseMessage from nvarchar(255) to nvarchar(MAX) this message goes away.

This table appears to be used only when there is a Manual Capture. I'm not sure what is keeping the transaction from going through if it is a shippable item. This may be a problem on the PayPal side. I will check into it and get back with my results.

[dnoell]

I have looked through my PayPal Sandbox account and cannot find any record of the transactions where AbleCommerce reported "Capture Failed". This leads me to believe that the problem is with the shopping cart when shippable items are included with the order. Shipping is set to be 10% of the shippable order and the sales tax is set to be local. I am not using the integrated shipping because this is just a test account.

Hopefully, someone can help me get past this issue.

[sohaib]

It seems that you may not have completed Post Upgrade steps when upgrading to 7.0 final.
Please have a look at
http://help.ablecommerce.com/upgrades/a ... _final.htm

[WylieE]

This error message has raised its ugly head again. This time, we were creating a test order with an international address. We used an existing customer's information so we'd have a valid address.

During checkout, everything seemed to go well. A new order was created, but it was empty aside from the customer information. USAePay showed no activity and no payment was recorded. No error message was reported to the customer.

Here's the About information from us:
PLATFORM: ASP.NET
VERSION: 7.0
BUILD: 9879

AjaxControlToolkit: 1.0.10301.0
AxInterop.ZRush_ShipRush5: 1.0.0.0
CommerceBuilder: 7.0.9876.0
CommerceBuilder.AuthorizeNet: 7.0.9764.0
CommerceBuilder.CanadaPost: 7.0.0.0
CommerceBuilder.CCH: 7.0.0.0
CommerceBuilder.Configuration: 7.0.9842.0
CommerceBuilder.CyberSource: 7.0.9764.0
CommerceBuilder.DataClient.Api: 1.0.0.19
CommerceBuilder.DataClient.Csv: 1.0.0.0
CommerceBuilder.DHL: 7.0.8825.0
CommerceBuilder.ECB: 7.0.0.0
CommerceBuilder.FedEx: 7.0.9725.0
CommerceBuilder.FRBNY: 7.0.0.0
CommerceBuilder.GoogleCheckout: 7.0.9632.0
CommerceBuilder.InternetSecure: 7.0.9764.0
CommerceBuilder.LinkPoint: 7.0.9764.0
CommerceBuilder.Paradata: 7.0.9764.0
CommerceBuilder.PayFlowPro: 7.0.9764.0
CommerceBuilder.PayJunction: 7.0.9764.0
CommerceBuilder.PayPal: 7.0.9877.0
CommerceBuilder.Services: 7.0.9733.0
CommerceBuilder.SkipJack: 7.0.9764.0
CommerceBuilder.UPS: 7.0.8695.0
CommerceBuilder.USPS: 7.0.9728.0
CommerceBuilder.Web: 7.0.9777.0
ComponentArt.Web.UI: 2007.1.1566.3
ConfiguredItems: 1.0.0.0
CyberSource.Base: 2.0.0.0
CyberSource.Clients: 5.0.2.0
CyberSource.Clients.XmlSerializers: 5.0.2.0
CyberSource.WSSecurity: 2.0.0.0
CybsWSSecurityIOP: 1.0.0.0
DevExpress.Data.v6.3: 6.3.3.0
DevExpress.Utils.v6.3: 6.3.3.0
DevExpress.XtraBars.v6.3: 6.3.3.0
DevExpress.XtraEditors.v6.3: 6.3.3.0
DevExpress.XtraGrid.v6.3: 6.3.3.0
edtFTPnet: 1.2.4.0
FredCK.FCKeditorV2: 2.5.2912.21007
ICSharpCode.SharpZipLib: 0.85.1.271
Interop.ZRush_ShipRush5: 1.0.0.0
LinkPointTransaction: 1.0.1797.29766
log4net: 1.2.10.0
Microsoft.Practices.EnterpriseLibrary.Common: 2.0.0.0
Microsoft.Practices.EnterpriseLibrary.Data: 2.0.0.0
Microsoft.Practices.ObjectBuilder: 1.0.51205.0
Microsoft.Web.Preview: 1.2.61025.0
NVelocity: 7.0.8530.0
PaygatewayNET: 3.0.0.0
paypal_base: 4.2.1.0
PFProCOMLib: 1.0.0.0
PFProdotNET: 0.0.0.0
skmMenu: 2.2.2991.25093
UPSOnlineInterface: 1.0.0.0
UsaEpay: 1.0.0.0
USAePayAPI: 1.5.2818.15473
Validators: 1.0.0.0
WarmothBody: 1.0.0.0
WebChart: 1.1.1.6
wwHoverPanel: 1.85.0.0

The last error in the log file was:
ERROR 2008-07-14 12:18:48,021 7723010ms Object a - Unhandled exception during checkout.
System.Data.SqlClient.SqlException: String or binary data would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteScalar(DbCommand command)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteScalar(DbCommand command)
at CommerceBuilder.Orders.OrderShipment.Save()
at CommerceBuilder.Orders.Basket.Checkout(CheckoutRequest checkoutRequest, Boolean validate, Boolean triggerEvents)

When 7.0 final was released, we went through a fresh install rather than upgrade from RC3, FYI.

[WylieE]

Here's more information. This keeps getting weirder.

We created a few more test orders and changed the shipping to willcall and payment via purchase order. This took the UPS online tools and payment gateway out of the mix. These orders worked fine. We then paid via credit card and used willcall as the shipping method. The order worked fine. When we used the UPS online tools, the order came out blank again. This would point to the AbleCommerce UPS Online tools integration.

I created another test for a customer address in Switzerland, shipping via UPS. Upon checkout, we received this error:
[[ConLib:ReceiptPage]] d:\Able Commerce\ConLib\ReceiptPage.ascx.cs(140): error CS0029: Cannot implicitly convert type 'string' to 'bool'

The order never made it to the credit card processing. Here's the error from the log file:
ERROR 2008-07-15 10:03:17,903 1828718ms Object a - Unhandled exception during checkout.
System.Data.SqlClient.SqlException: String or binary data would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteScalar(DbCommand command)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteScalar(DbCommand command)
at CommerceBuilder.Orders.OrderShipment.Save()
at CommerceBuilder.Orders.Basket.Checkout(CheckoutRequest checkoutRequest, Boolean validate, Boolean triggerEvents)

[WylieE]

Here's more information. This keeps getting weirder. .......................................

I created another test for a customer address in Switzerland, shipping via UPS. Upon checkout, we received this error:
[[ConLib:ReceiptPage]] d:\Able Commerce\ConLib\ReceiptPage.ascx.cs(140): error CS0029: Cannot implicitly convert type 'string' to 'bool'

The order never made it to the credit card processing. Here's the error from the log file:
ERROR 2008-07-15 10:03:17,903 1828718ms Object a - Unhandled exception during checkout.
System.Data.SqlClient.SqlException: String or binary data would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteScalar(DbCommand command)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteScalar(DbCommand command)
at CommerceBuilder.Orders.OrderShipment.Save()
at CommerceBuilder.Orders.Basket.Checkout(CheckoutRequest checkoutRequest, Boolean validate, Boolean triggerEvents)

The above error was cause by our programmer. It has been fixed.


We are still getting the "String or binary data would be truncated. The statement has been terminated." error any time an international order is placed and shipping uses an online tool. We get a blank order, no payment, no nothing. If we were live with Able, this would affect roughly 30% of our business.

I can create a flat rate shipping method and process international orders consistently. However, when I switch to shipping via an UPS online tools, the order crashes.

Here's the error message from our most recent test:
ERROR 2008-07-16 09:37:36,099 772910ms Object a - Unhandled exception during checkout.
System.Data.SqlClient.SqlException: String or binary data would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteScalar(DbCommand command)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteScalar(DbCommand command)
at CommerceBuilder.Orders.OrderShipment.Save()
at CommerceBuilder.Orders.Basket.Checkout(CheckoutRequest checkoutRequest, Boolean validate, Boolean triggerEvents)