Order Can't Write to Database Error

[gdelorey@mitcs.com]

Hello -

This is a new one for us - never seen it before. A customer called in and mentioned they were charged three times (which we verified via PayPal) however no record exists in the orders table or directly in the database. Upon looking at the Error Log, we see the following error (see below). She claims she simply was using her Amex (versus going off to PayPal). Does anyone have insight as to why this order would not write to the DB?

Thanks!
Greg

Code: Select all

An error occurred during the checkout process. UserId: 10059974 / {email ommitted} Customer: {customer name ommitted} / {email ommitted} / {phone number ommitted}
Exception: could not insert: [CommerceBuilder.Payments.Transaction][SQL: INSERT INTO ac_Transactions (TransactionTypeId, PaymentId, PaymentGatewayId, ProviderTransactionId, TransactionDate, Amount, TransactionStatusId, ResponseCode, ResponseMessage, AuthorizationCode, AVSResultCode, CVVResultCode, CAVResultCode, RemoteIP, Referrer, AdditionalData) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); select SCOPE_IDENTITY()] Stack Trace: at NHibernate.Id.Insert.AbstractReturningDelegate.PerformInsert(SqlCommandInfo insertSQL, ISessionImplementor session, IBinder binder) at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object[] fields, Object obj, ISessionImplementor session) at NHibernate.Action.EntityIdentityInsertAction.Execute() at NHibernate.Engine.ActionQueue.Execute(IExecutable executable) at NHibernate.Event.Default.AbstractSaveEventListener.PerformSaveOrReplicate(Object entity, EntityKey key, IEntityPersister persister, Boolean useIdentityColumn, Object anything, IEventSource source, Boolean requiresImmediateIdAccess) at NHibernate.Event.Default.AbstractSaveEventListener.SaveWithGeneratedId(Object entity, String entityName, Object anything, IEventSource source, Boolean requiresImmediateIdAccess) at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.SaveWithGeneratedOrRequestedId(SaveOrUpdateEvent event) at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.EntityIsTransient(SaveOrUpdateEvent event) at CommerceBuilder.DomainModel.SaveOrUpdateEventListener.PerformSaveOrUpdate(SaveOrUpdateEvent event) at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.OnSaveOrUpdate(SaveOrUpdateEvent event) at NHibernate.Impl.SessionImpl.FireSaveOrUpdate(SaveOrUpdateEvent event) at NHibernate.Impl.SessionImpl.SaveOrUpdate(String entityName, Object obj) at NHibernate.Engine.Cascade.CascadeToOne(Object parent, Object child, IType type, CascadeStyle style, Object anything, Boolean isCascadeDeleteEnabled) at NHibernate.Engine.Cascade.CascadeCollectionElements(Object parent, Object child, CollectionType collectionType, CascadeStyle style, IType elemType, Object anything, Boolean isCascadeDeleteEnabled) at NHibernate.Engine.Cascade.CascadeCollection(Object parent, Object child, CascadeStyle style, Object anything, CollectionType type) at NHibernate.Engine.Cascade.CascadeOn(IEntityPersister persister, Object parent, Object anything) at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.CascadeOnUpdate(SaveOrUpdateEvent event, IEntityPersister persister, Object entity) at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.PerformSaveOrUpdate(SaveOrUpdateEvent event) at CommerceBuilder.DomainModel.SaveOrUpdateEventListener.PerformSaveOrUpdate(SaveOrUpdateEvent event) at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.OnSaveOrUpdate(SaveOrUpdateEvent event) at NHibernate.Impl.SessionImpl.FireSaveOrUpdate(SaveOrUpdateEvent event) at NHibernate.Impl.SessionImpl.SaveOrUpdate(String entityName, Object obj) at NHibernate.Engine.Cascade.CascadeToOne(Object parent, Object child, IType type, CascadeStyle style, Object anything, Boolean isCascadeDeleteEnabled) at NHibernate.Engine.Cascade.CascadeCollectionElements(Object parent, Object child, CollectionType collectionType, CascadeStyle style, IType elemType, Object anything, Boolean isCascadeDeleteEnabled) at NHibernate.Engine.Cascade.CascadeCollection(Object parent, Object child, CascadeStyle style, Object anything, CollectionType type) at NHibernate.Engine.Cascade.CascadeOn(IEntityPersister persister, Object parent, Object anything) at NHibernate.Event.Default.AbstractFlushingEventListener.CascadeOnFlush(IEventSource session, IEntityPersister persister, Object key, Object anything) at NHibernate.Event.Default.AbstractFlushingEventListener.PrepareEntityFlushes(IEventSource session) at NHibernate.Event.Default.AbstractFlushingEventListener.FlushEverythingToExecutions(FlushEvent event) at NHibernate.Event.Default.DefaultFlushEventListener.OnFlush(FlushEvent event) at NHibernate.Impl.SessionImpl.Flush() at NHibernate.Transaction.AdoTransaction.Commit() at CommerceBuilder.Common.DatabaseSessionManager.CommitTransaction(Boolean ignoreNoTransactionError) at CommerceBuilder.Services.Checkout.CheckoutService.ExecuteCheckout(CheckoutRequest checkoutRequest, Boolean validateBasket, Boolean triggerEvents) Inner Exception: String or binary data would be truncated. The statement has been terminated. Inner Exception Stack Trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() 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, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) at NHibernate.Id.IdentityGenerator.InsertSelectDelegate.ExecuteAndExtract(IDbCommand insert, ISessionImplementor session) at NHibernate.Id.Insert.AbstractReturningDelegate.PerformInsert(SqlCommandInfo insertSQL, ISessionImplementor session, IBinder binder) 

[jguengerich]

Based on the inner exception ("Inner Exception: String or binary data would be truncated. The statement has been terminated."), most likely one of the string fields that it was trying to save to the ac_Transactions table was longer than the nvarchar field definition for that table. Unfortunately, probably the only way to figure out which field was causing the problem would be to turn on debug mode for the gateway or put your own logging code before the transaction is saved to the database (which may be in source code). Then after the error occurs, compare the logged field values with the table definition field lengths and see which fields have too many characters. Not sure how you would force the error though, other than having that specific customer use the same card again.

EDIT: If you have a dev site set up (using the payment gateway's test environment), you could try placing a test order there using the test AmEx number to see if it something that happens for all AmEx cards. You'd want to try to get the customer to tell you the exact steps they took when the error occurred. I don't use PayPal, so I can't give any specific help related to PayPal; in fact I'm confused how PayPal is involved if they were using their AmEx card instead of "going off to PayPal" but you "verified via PayPal" that they were charged multiple times.

[notsop]

We just started getting this error as well today. Same issue,customer using Amex. Payment gets authorized but it's nowhere in the orders table. Error log shows pretty much the exact same error novel. Please post if you have a solution. We're running gold R10. Thanks!

[jmestep]

This will sound like a completely off the wall explanation, but we kept running into that error using Authnet CIM on a site. The same kind of truncation message in the same table. Katie finally found the problem- the merchant name had a comma in it. So maybe something in that customer's name or address has a comma that would be causing it.

[notsop]

Thank You!

That was the problem.