AbleCommerce Shopping Cart Software

Posted: **Fri May 15, 2009 6:35 am**

Hello... We just started getting this Google Checkout error yesterday, and were wondering if anyone could shed some light as to why...
Here is the response from Google in our integration panel...

265163950479301
Time of occurrence: May 15, 2009 12:42:46 AM EDT
Error: We encountered an error trying to access your server at https://www.poolgeek.com/Checkout/Googl ... tener.ashx -- the error we got is Send failed with code: 500. Response body was: <html> <head> <title>The UPDATE statement conflicted with the FOREIGN KEY constraint "ac_Countries_ac_Addresses_FK1". The conflict occurred in database "AbleCommerce", table "dbo.ac_Countries", column 'CountryCode'.<br>The statement has been terminated.</title> <style> body {font-family:"Verdana";font-weight:normal;font-size: .7em;color:black;} p {font-family:"Verdana";font-weight:normal;color:black;margin-top: -5px} b {font-family:"Verdana";font-weight:bold;color:black;margin-top: -5px} H1 { font-family:"Verdana";font-weight:normal;font-size:18pt;color:red } H2 { font-family:"Verdana";font-weight:normal;font-size:14pt;color:maroon } pre {font-family:"Lucida Console";font-size: .9em} .marker {font-weight: bold; color: black;text-decoration: none;} .version {color: gray;} .error {margin-bottom: 10px;} .expandable { text-decoration:underline; font-weight:bold; color:navy; cursor:hand; } </style> </head> <body bgcolor="white"> <span><H1>Server Error in '/' Application.<hr width=100% size=1 color=silver></H1> <h2> <i>The UPDATE statement conflicted with the FOREIGN KEY constraint "ac_Countries_ac_Addresses_FK1". The conflict occurred in database "AbleCommerce", table "dbo.ac_Countries", column 'CountryCode'.<br>The statement has been terminated.</i> </h2></span> <font face="Arial, Helvetica, Geneva, SunSans-Regular, sans-serif "> <b> Description: </b>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. <br><br> <b> Exception Details: </b>System.Data.SqlClient.SqlException: The UPDATE statement conflicted with the FOREIGN KEY constraint "ac_Countries_ac_Addresses_FK1". The conflict occurred in database "AbleCommerce", table "dbo.ac_Countries", column 'CountryCode'.<br>The statement has been terminated.<br><br> <b>Source Error:</b> <br><br> <table width=100% bgcolor="#ffffcc"> <tr> <td> <code><pre> Line 39: NewOrderNotification N1 = (NewOrderNotification)EncodeHelper.Deserialize(RequestXml, typeof(NewOrderNotification)); Line 40: NewOrderHandler nhandler = new NewOrderHandler(N1); <font color=red>Line 41: nhandler.Process(); </font>Line 42: break; Line 43: case "risk-information-notification":</pre></code> </td> </tr> </table> <br> <b> Source File: </b> d:\Websites\PoolGeekUpgrade\Checkout\Google\NotificationListener.ashx<b>    Line: </b> 41 <br><br> <b>Stack Trace:</b> <br><br> <table width=100% bgcolor="#ffffcc"> <tr> <td> <code><pre> [SqlException (0x80131904): The UPDATE statement conflicted with the FOREIGN KEY constraint "ac_Countries_ac_Addresses_FK1". The conflict occurred in database "AbleCommerce", table "dbo.ac_Countries", column 'CountryCode'. The statement has been terminated.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +196 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2408 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +147 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1095 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +413 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +130 Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DbCommand command) +131 Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DbCommand command) +131 CommerceBuilder.Data.Database.ExecuteNonQuery(DbCommand command) +245 CommerceBuilder.Users.Address.Save() +2144 CommerceBuilder.Common.PersistentCollection`1.Save() +67 CommerceBuilder.Users.User.SaveChildren() +59 CommerceBuilder.Users.User.BaseSave() +5172 CommerceBuilder.Users.User.Save() +824 CommerceBuilder.Payments.Providers.GoogleCheckout.AC.NewOrderHandler.Process() +721 NotificationListner.ProcessRequest(HttpContext context) in d:\Websites\PoolGeekUpgrade\Checkout\Google\NotificationListener.ashx:41 System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +456 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +65 </pre></code> </td> </tr> </table> <br> <hr width=100% size=1 color=silver> <b>Version Information:</b> Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433 </font> </body> </html>

...and here is the XML response:

Code: Select all

 <?xml version="1.0" encoding="UTF-8"?>
<new-order-notification xmlns="http://checkout.google.com/schema/2" serial-number="265163950479301-00001-7">
  <timestamp>2009-05-15T01:45:07.197Z</timestamp>
  <shopping-cart>
    <items>
      <item>
        <item-name>SunHeater InGround Solar Heater</item-name>
        <item-description>SunHeater InGround Solar Heater is a great choice for any inground pool, heating the water naturally with sun exposure instead of using costly traditional heaters. Its simple design routes water from your pool through a series of tubes that pick up the heat generated by the sun and returns it to ...</item-description>
        <quantity>4</quantity>
        <unit-price currency="USD">221.74</unit-price>
        <merchant-private-item-data>
          
          
          <basketItemId>43101</basketItemId>
          
          
          <productId>3829</productId>
          
          
          <orderItemType>Product</orderItemType>
          
          
          <shippable>Yes</shippable>
          
          
          <taxCodeId>1</taxCodeId>
          
          
          <weight>0</weight>
          
          
          <wrapStyleId>0</wrapStyleId>
          
          
          <optionList>
          </optionList>
          
          
          <giftMessage>
          </giftMessage>
          
          
          <lineMessage>
          </lineMessage>
          
          
          <lastModifiedDate>5/14/2009 8:31:54 PM</lastModifiedDate>
          
          
          <orderBy>1</orderBy>
          
          
          <parentItemId>258423</parentItemId>
          
          
          <sku>SHIGSH</sku>
          
          
          <wishlistItemId>0</wishlistItemId>
          
          
          <basketItemKitProducts>
            
            
            <kitProductId>15213</kitProductId>
            
          
          </basketItemKitProducts>
          
        
        </merchant-private-item-data>
      </item>
    </items>
    <merchant-private-data>
      
      
      <BasketId>43101</BasketId>
      
      
      <BasketContentHash>D4DAF0A862BE890BF830A23377446786</BasketContentHash>
      
    
    </merchant-private-data>
    <cart-expiration>
      <good-until-date>2009-05-15T02:01:54.166Z</good-until-date>
    </cart-expiration>
  </shopping-cart>
  <order-adjustment>
    <merchant-calculation-successful>true</merchant-calculation-successful>
    <merchant-codes />
    <total-tax currency="USD">0.0</total-tax>
    <shipping>
      <merchant-calculated-shipping-adjustment>
        <shipping-name>Flat Rate Shipping</shipping-name>
        <shipping-cost currency="USD">0.0</shipping-cost>
      </merchant-calculated-shipping-adjustment>
    </shipping>
    <adjustment-total currency="USD">0.0</adjustment-total>
  </order-adjustment>
  <buyer-id>268526393959508</buyer-id>
  <google-order-number>265163950479301</google-order-number>
  <buyer-shipping-address>
    <company-name></company-name>
    <contact-name>Buyers Name</contact-name>
    <email>xxxxxxxxx@gmail.com</email>
    <phone></phone>
    <fax></fax>
    <address1>Buyers Address</address1>
    <address2></address2>
    <country-code>CA</country-code>
    <city>Midland</city>
    <region>ON</region>
    <postal-code>L4R 4A6</postal-code>
  </buyer-shipping-address>
  <buyer-billing-address>
    <company-name></company-name>
    <contact-name>Buyers Name</contact-name>
    <email>xxxxxxxs@gmail.com</email>
    <phone>xxx xxx-xxxx</phone>
    <fax></fax>
    <address1>Buyers Address</address1>
    <address2></address2>
    <country-code>CA</country-code>
    <city>Midland</city>
    <region>ON</region>
    <postal-code>L4R 4A6</postal-code>
  </buyer-billing-address>
  <buyer-marketing-preferences>
    <email-allowed>true</email-allowed>
  </buyer-marketing-preferences>
  <order-total currency="USD">886.96</order-total>
  <fulfillment-order-state>NEW</fulfillment-order-state>
  <financial-order-state>REVIEWING</financial-order-state>
</new-order-notification>

Any ideas why we are recieving this? Thanks for any help you can give us with this.

Scott

Posted: **Thu May 21, 2009 8:52 am**

Can you check if country code 'CA' exists in your ac_Countries table?

Posted: **Thu May 21, 2009 9:01 am**

No, it does not exist...
We only want to accept orders from the US, and only the continental states (no alaska and hawaii). We have a similar problem when people from Hawaii and Alaska order using either paypal or google checkout. The order will go through (due to the fact that hawaii and alaska are still listed as states in the system), but, we would like to somehow let people know that we only take orders from continental us.
How can I rectify this discrepency? Shouldn't the system be able to tell Google that an invalid shipping destination was selected?
Thanks again for the help.

Scott

Posted: **Thu May 21, 2009 10:36 am**

System should be able to tell GC that invalid shipping destination is selected... but the billing address may not necessarily be considered invalid because of this.... also at least the countries should be present in the database even if not supported. You would certainly not want to discourage a customer from CA who wants to get a product shipped to his/her friend/relative in US.

Posted: **Fri May 22, 2009 7:33 am**

I agree about the allowing people to purchase from all billing areas, but, unfortunately, my warehouse guy informs me that it is also allowing orders to go through with shipping addresses that we have not allowed in able commerce. It's easy to verify this, as it's impossible to choose Alaska or Hawaii in the Able Commerce store, but, if I change my Google account to an Alaskan address, I can still place an order with GC in our store.
Any ideas?
Thanks again,

Scott

Posted: **Tue Jan 05, 2010 1:47 am**

Just wanted to reopen this thread, because I've still not been able to figure out a solution to my problem.
The problem is that whenever someone orders from outside of the US (or uses a credit card from outside of US), our system is throwing an error to GC when they try to send to the listener. It is because we don't have the other country codes in our system. But, everytime I try to add the countries in, in order to be able to use them to collect the info from GC, they are then available during the checkout process.
Can anyone tell me the proper way to add a country, so that GC will not fail, but also so that customers can not select these countries while they are checking out?
Thanks so much for the help.

Scott

Posted: **Tue Jan 05, 2010 6:33 am**

One way could be to use a custom InitializeCountries() in the one page checkout

Code: Select all

private void InitializeCountries()
    {
        _Countries = CountryDataSource.LoadForStore("Name");
        //FIND STORE COUNTRY AND COPY TO FIRST POSITION
        string storeCountry = Token.Instance.Store.DefaultWarehouse.CountryCode;
        if (storeCountry.Length == 0) storeCountry = "US";
        int index = _Countries.IndexOf(storeCountry);
        if (index > -1)
        {
            Country breakItem = new Country(storeCountry);
            breakItem.Name = "----------";
            _Countries.Insert(0, breakItem);
            _Countries.Insert(0, _Countries[index + 1]);
            if (storeCountry == "US")
            {
                index = _Countries.IndexOf("CA");
                if (index > -1) _Countries.Insert(1, _Countries[index]);
            }
            else if (storeCountry == "CA")
            {
                index = _Countries.IndexOf("US");
                if (index > -1) _Countries.Insert(1, _Countries[index]);
            }
        }
    }

Maybe use CountryDataSource.LoadForCriteria() or make a custom list of countries.

AbleCommerce Shopping Cart Software

Just started getting this Google Checkout Error

Just started getting this Google Checkout Error

Re: Just started getting this Google Checkout Error

Re: Just started getting this Google Checkout Error

Re: Just started getting this Google Checkout Error

Re: Just started getting this Google Checkout Error

Re: Just started getting this Google Checkout Error

Re: Just started getting this Google Checkout Error