Tim

Footprints in the snow of a warped mind

Tim

Where to find me

Flickr Icon  Twitter Icon  Linked In Icon  FaceBook Icon  Windows Live Alerts Butterfly  RSS 2.0 

The Site Doctor is Hiring!

Enjoy what you read here?
Think you can do better?
Join our team and get paid
up-to £35,000.
Apply now.

Tag Cloud

AJAX (4) Analysis (3) ASP (6) ASP.Net (59) Error Reporting (4) Web Service (2) WSDL (1) Atlas (2) Azure (1) Born In The Barn (1) Business (94) Business Start-up Advice (36) Client (17) Expanding Your Business (24) Recruitment (1) C# (25) Canoeing (4) Canoe Racing (5) Cheshire Ring Race (5) Racing (2) Training (4) Christmas (1) CIMA (1) Cisco (1) 7970G (1) CMS (1) Code Management (1) Cohorts (4) Commerce4Umbraco (1) Content (1) Content Management (1) Content Management System (1) CSS (4) dasBlog (5) DDD (2) DDDSW (1) Design (12) Icons (1) Development (27) Domain Names (1) eCommerce (12) Email (1) Employment (2) Festive Sparkle (1) General (39) Christmas (6) Fun and Games (11) Internet (22) Random (46) RX-8 (8) Git (2) Google (1) Google AdWords (1) Google Analytics (1) Hacking (1) Helpful Script (3) Home Cinema (2) Hosting (2) HTML (3) IIS (11) iPhone (1) JavaScript (5) jQuery (2) LINQPad (1) Marketing (6) Email (1) Multipack (1) MVC (3) Networking (3) Nintendo (1) Nuget (1) OS Commerce (1) Payment (1) Photography (1) PHP (1) Plugin (1) PowerShell (3) Presentation (1) Press Release (1) Productivity (3) Random Thought (1) Script (2) Security (2) SEO (6) Server Maintenance (7) Server Management (12) Social Media (2) Social Networking (3) Experiment (1) Software (11) Office (5) Visual Studio (14) Windows (5) Vista (1) Source Control (2) SQL (11) SQL Server (19) Starting Something New (2) Statistics (2) Stored Procedure (1) Sublime Text 2 (1) SVN (1) TeaCommerce (1) Testing (2) The Cloud (1) The Site Doctor (139) Turnover Challenge (1) Twitter (3) uCommerce (17) Umbraco (36) 2009 (1) 2011 (1) Useful Script (4) Virtual Machine (1) Web Development (72) WebDD (33) Wii (1) Windows Azure (1) XSLT (1)

Blog Archive

Search

<April 2014>
SunMonTueWedThuFriSat
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910

Recent Comments

Blog Archive

Various Links

Google+

Blogs I Read

[Feed] Google Blog
Official Google Webmaster Central Blog
[Feed] Matt Cutts
Gadgets, Google, and SEO
[Feed] Ol' Deano's Blog
My mate Dean's blog on my space, equally as random as mine but not off on as much of a tangent!
[Feed] Sam's Blog
Sam is one of my younger brothers studying Product Design and Manufacture at Loughborough, this is his blog :) Enjoy!

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

newtelligence dasBlog 2.2.8279.16125

Send mail to the author(s) Email Me (Tim Gaunt)

© 2014 Tim Gaunt.

Sign In

# Tuesday, April 15, 2014

Illegal characters in path in MVC view

Tuesday, April 15, 2014 2:10:37 PM (GMT Daylight Time, UTC+01:00)

I initially thought that this was an issue with Umbraco's implementation on MVC but it turns out that if you have an IEnumerable property on your view model then you may get an error similar to the below.

I've done a little searching around in the past but I didn't find a great deal to help. It turns out that the issue and fix is actually rather simple. Instead of using IEnumerable, use ICollection (or IList) and that's it, problem solved!

[ArgumentException: Illegal characters in path.]
   System.IO.Path.Combine(String path1, String path2) +14355737
   System.Web.Compilation.DiskBuildResultCache.GetBuildResult(String cacheKey, VirtualPath virtualPath, Int64 hashCode, Boolean ensureIsUpToDate) +41
   System.Web.Compilation.BuildManager.GetBuildResultFromCacheInternal(String cacheKey, Boolean keyFromVPP, VirtualPath virtualPath, Int64 hashCode, Boolean ensureIsUpToDate) +253
   System.Web.Compilation.BuildManager.GetVPathBuildResultFromCacheInternal(VirtualPath virtualPath, Boolean ensureIsUpToDate) +132
   System.Web.Compilation.BuildManager.GetVPathBuildResultInternal(VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile, Boolean throwIfNotFound, Boolean ensureIsUpToDate) +91
   System.Web.Compilation.BuildManager.GetVPathBuildResultWithNoAssert(HttpContext context, VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile, Boolean throwIfNotFound, Boolean ensureIsUpToDate) +203
   System.Web.Compilation.BuildManager.GetObjectFactory(String virtualPath, Boolean throwIfNotFound) +180
   System.Web.Mvc.BuildManagerWrapper.System.Web.Mvc.IBuildManager.FileExists(String virtualPath) +18
   Microsoft.Web.Mvc.ViewEngineFixWorker`1.FileExists(ControllerContext controllerContext, String virtualPath) +23
   System.Web.WebPages.DefaultDisplayMode.GetDisplayInfo(HttpContextBase httpContext, String virtualPath, Func`2 virtualPathExists) +55
   System.Linq.WhereSelectListIterator`2.MoveNext() +245
   System.Linq.Enumerable.FirstOrDefault(IEnumerable`1 source, Func`2 predicate) +215
   System.Web.WebPages.DisplayModeProvider.GetDisplayInfoForVirtualPath(String virtualPath, HttpContextBase httpContext, Func`2 virtualPathExists, IDisplayMode currentDisplayMode) +27
   Microsoft.Web.Mvc.ViewEngineFixWorker`1.GetPathFromGeneralName(ControllerContext controllerContext, List`1 locations, String name, String controllerName, String areaName, String cacheKey, String[]& searchedLocations) +450
   Microsoft.Web.Mvc.ViewEngineFixWorker`1.GetPath(ControllerContext controllerContext, String[] locations, String[] areaLocations, String locationsPropertyName, String name, String controllerName, String cacheKeyPrefix, Boolean useCache, String[]& searchedLocations) +990
   Microsoft.Web.Mvc.ViewEngineFixWorker`1.FindPartialView(ControllerContext controllerContext, String partialViewName, Boolean useCache) +180
   System.Web.Mvc.ViewEngineCollection.Find(Func`2 lookup, Boolean trackSearchedPaths) +177
   System.Web.Mvc.Html.TemplateHelpers.ExecuteTemplate(HtmlHelper html, ViewDataDictionary viewData, String templateName, DataBoundControlMode mode, GetViewNamesDelegate getViewNames, GetDefaultActionsDelegate getDefaultActions) +660
   System.Web.Mvc.Html.TemplateHelpers.TemplateHelper(HtmlHelper html, ModelMetadata metadata, String htmlFieldName, String templateName, DataBoundControlMode mode, Object additionalViewData, ExecuteTemplateDelegate executeTemplate) +1371
   System.Web.Mvc.Html.TemplateHelpers.TemplateHelper(HtmlHelper html, ModelMetadata metadata, String htmlFieldName, String templateName, DataBoundControlMode mode, Object additionalViewData) +107
   System.Web.Mvc.Html.TemplateHelpers.TemplateFor(HtmlHelper`1 html, Expression`1 expression, String templateName, String htmlFieldName, DataBoundControlMode mode, Object additionalViewData) +155
   System.Web.Mvc.Html.EditorExtensions.EditorFor(HtmlHelper`1 html, Expression`1 expression) +95
   ASP._Page_Views_Category_ProductsInCategory_cshtml.Execute() in \...\Views\Category\ProductsInCategory.cshtml:3
   System.Web.WebPages.WebPageBase.ExecutePageHierarchy() +280
   System.Web.Mvc.WebViewPage.ExecutePageHierarchy() +126
   System.Web.WebPages.WebPageBase.ExecutePageHierarchy(WebPageContext pageContext, TextWriter writer, WebPageRenderingBase startPage) +181
   System.Web.Mvc.ViewResultBase.ExecuteResult(ControllerContext context) +378
   System.Web.Mvc.<>c__DisplayClass1a.<InvokeActionResultWithFilters>b__17() +33
   System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilter(IResultFilter filter, ResultExecutingContext preContext, Func`1 continuation) +853420
   System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilter(IResultFilter filter, ResultExecutingContext preContext, Func`1 continuation) +853420
   System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilter(IResultFilter filter, ResultExecutingContext preContext, Func`1 continuation) +853420
   System.Web.Mvc.ControllerActionInvoker.InvokeActionResultWithFilters(ControllerContext controllerContext, IList`1 filters, ActionResult actionResult) +265
   System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +853472
   System.Web.Mvc.<>c__DisplayClass1d.<BeginExecuteCore>b__19() +40
   System.Web.Mvc.Async.<>c__DisplayClass1.<MakeVoidDelegate>b__0() +15
   System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +65
   System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +15
   System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +51
   System.Web.Mvc.<>c__DisplayClass8.<BeginProcessRequest>b__3(IAsyncResult asyncResult) +42
   System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +15
   System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +51
   System.Web.Mvc.<>c__DisplayClass4.<Wrap>b__3() +15
   System.Web.Mvc.ServerExecuteHttpHandlerWrapper.Wrap(Func`1 func) +41
   System.Web.HttpServerUtility.ExecuteInternal(IHttpHandler handler, TextWriter writer, Boolean preserveForm, Boolean setPreviousPage, VirtualPath path, VirtualPath filePath, String physPath, Exception error, String queryStringOverride) +1411
 

Don't forget to follow me on Twitter.

Illegal characters in path in MVC view
Useful Links:  #  digg it!  del.icio.us  Technorati  email it!  Post CommentsComments [0]  Trackback LinkTrackback
CategoriesTags: C# | Development | MVC
# Friday, March 21, 2014

How to: Delete duplicate products from a category in uCommerce

Friday, March 21, 2014 10:25:29 AM (GMT Standard Time, UTC+00:00)

I was looking into an issue with one of our sites that couldn't delete a product from a category in uCommerce today and noticed that there were a lot of duplicate product-category relationships in the table.

Deleting them is fairly simple so I thought I'd share the code in case you want to clear it down as well.

BEGIN TRAN

-- Check for any duplicates
SELECT
	d.*
FROM (
	SELECT 
		cr.CategoryId, cr.ProductId, cr.CategoryProductRelationId,
		ROW_NUMBER() OVER (PARTITION BY cr.CategoryId, cr.ProductId ORDER BY cr.CategoryProductRelationId) AS Position
	FROM [dbo].[uCommerce_CategoryProductRelation] cr
) AS d
WHERE Position != 1

-- Delete the duplicates
DELETE FROM uCommerce_CategoryProductRelation 
WHERE CategoryProductRelationId IN (
	SELECT
		d.CategoryProductRelationId
	FROM (
		 SELECT 
			cr.CategoryProductRelationId,
			ROW_NUMBER() OVER (PARTITION BY cr.CategoryId, cr.ProductId ORDER BY cr.CategoryProductRelationId) AS Position
		 FROM [dbo].[uCommerce_CategoryProductRelation] cr
	) AS d
	WHERE Position != 1
)

-- Double check the duplicates have been deleted
SELECT
	d.*
FROM (
	SELECT 
		cr.CategoryProductRelationId,
		ROW_NUMBER() OVER (PARTITION BY cr.CategoryId, cr.ProductId ORDER BY cr.CategoryProductRelationId) AS Position
	FROM [dbo].[uCommerce_CategoryProductRelation] cr
) AS d
WHERE Position != 1

ROLLBACK TRAN
 

Don't forget to follow me on Twitter.

# Tuesday, January 28, 2014

Error when publishing Umbraco page - Object reference not set to an instance of an object

Tuesday, January 28, 2014 10:33:38 AM (GMT Standard Time, UTC+00:00)

umbracologo[1]This is one of those irritating errors that you come across every now and again and can take hours to diagnose.

If you've seen something like the error at the end of this post, then check that you don't have any document types which have an alias with a non-alphanumeric character in them. If you do, remove it and all should be good.

Got lots of Umbraco Document Types and not enough time? Here's a quick script that will help find any Document Types that may be causing you trouble:

SELECT * 
FROM [dbo].[cmsContentType] ct 
WHERE ct.alias LIKE '%[^a-zA-Z0-9]%'

Why does this happen?

Umbraco uses the "Alias" field of the Document Types to create the XML version of the site's content (you can find this in /app_data/umbraco.config) and XML cannot use anything but alphanumeric characters for it's node name (there are a couple of others that it allows but for safety it's best not to).

When you hit the "Save and Publish" button, Umbraco tries to create the XML cache, gets to your special document type and fails.

The reason it doesn't matter whether you're actually using the Document Type in the site or not is because Umbraco outputs a list of all Document Types at the start of the XML.

 

Object reference not set to an instance of an object.

Description: 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. 

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error: 
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace: 
[NullReferenceException: Object reference not set to an instance of an object.]
   umbraco.content.ValidateSchema(String docTypeAlias, XmlDocument xmlDoc) +51
      umbraco.content.AppendDocumentXml(Int32 id, Int32 level, Int32 parentId, XmlNode docXml, XmlDocument xmlContentCopy) +120
      umbraco.content.PublishNodeDo(Document d, XmlDocument xmlContentCopy, Boolean updateSitemapProvider) +217
      umbraco.content.UpdateDocumentCache(Document d) +407
      umbraco.content.UpdateDocumentCache(Int32 pageId) +75
      umbraco.library.UpdateDocumentCache(Int32 DocumentId) +150
      umbraco.cms.presentation.editContent.Publish(Object sender, EventArgs e) +462
      System.EventHandler.Invoke(Object sender, EventArgs e) +0
      umbraco.controls.ContentControl.savePublish(Object Sender, ImageClickEventArgs e) +96
      System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +115
      System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +120
      System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
      System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
      System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
      System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563

 

Don't forget to follow me on Twitter.

# Wednesday, January 08, 2014

How to price your products

Wednesday, January 08, 2014 12:50:14 PM (GMT Standard Time, UTC+00:00)

price-tag-01I blogged a few years ago about how to price your time I thought I should blog about how to price a physical product.

Recently, I've been involved in setting up a new company selling all sorts of Christmas decorations, artificial trees and more called Festive Sparkle. Part of setting up any business is setting a price for the items you plan to sell.

Pricing a physical product is a little different to how you would price your time but there are similar factors that you have to take into account. At the very least you should factor  the following into your calculations:

  • [COST] The original cost of the item
  • [BUYTAX] Any taxes incurred for buying the product
  • [STORAGE] The cost of storing the item before it's sold (whether this is a garage, shop, warehouse etc)
  • [BREAK] A margin for breakages, returns etc
  • [MARGIN] A margin to make some money
  • [WIGGLE] A margin to allow for discounts
  • [SELLTAX] Any taxes incurred for selling the product

If your products (and market) allow for it then I would ensure your margins allow for at least a 15% discount. Personally I would include that in the [WIGGLE] if you can as you ideally need to sell your items and make some money!

This then gives you a nice formula to start off with:

Actual Cost = ([COST]+ [BUYTAX]) + [STORAGE]

Minimum Sell Price = [Actual Cost] + [BREAK] + ([MARGIN] + [WIGGLE]) + [SELLTAX]

Depending on your situation, the storage cost may not be that easy to calculate. In an ideal world you'd sell all your stock but realistically you are likely to have some stock left so if it doesn't have a shelf life (i.e. eggs, bread etc) so you can break it down: firstly into an annual cost and then into a per item charge.

Calculate the total cost of your storage for your items for a year. If your cost is directly attributable to all your stock, be pessimistic and calculate it on having 50% of your stock for an entire year (so if you had 100 items, each costing £1 a year to store, your rough storage cost will be: ((100 / 2) * 1) = £50). This is a very rough calculation but it should give you a baseline to start with. Once you have the total cost of annual storage, spread that cost all your products - so your £100 of stock will cost £150 over the year.

Breakages are often forgotten about but it's an actual cost that needs to be factored in. Depending on your product, you may not get breakages or accept returns but I would still factor in at least a 1% return rate to be safe.

You also want to give thought to the cost of shipping, if you're thinking about offering free shipping on the items then you will need to factor that in as well.

To help, I've put together a little spreadsheet you can use. You can download my product pricing spreadsheet here.

I hope that helps someone, if I've forgotten something else, let me know.

Finally, if you're selling items that are the same as ones sold by others e.g. branded goods then you obviously need to give consideration to the price that your competitors are using, if your prices are way off then perhaps you need to think of another product to sell?

A quick example

So, if I bought 100 items at £1 each, the cost of storage was 1p per item per day, I needed to factor in a 2% breakages/return rate, 15% discount and a 10% margin with tax at 20%, the numbers would look like this:

Item storage cost: ((100 / 2) * (£0.01 * 365) = £1.83
Actual Cost: (£1.00 + £0.20) + £1.83 = £3.03
Cost Of All Breakages: £3.03 * (100 * 0.02) = £6.05
Cost inc Breakages: £3.03 + (£6.05 / 100) = £3.09
Cost inc Margin: £3.09 + (£3.09 * 0.10) = £3.39
Cost inc Wiggle: £3.39 + (£3.39 * 0.15) = £3.90
Minimum Sell Price: £3.90 * 1.20 = £4.68

So for an item that you purchased for £1.00, you'd need to sell it for £4.68 but this is a slightly simplistic as it doesn't factor in the other costs to sell the item (all of which you would need to factor in).

 

Don't forget to follow me on Twitter.

# Monday, December 09, 2013

Don’t dwell - get over it and move on

Monday, December 09, 2013 9:33:12 AM (GMT Standard Time, UTC+00:00)

One thing that always amazes me in business is the tendency people have to dwell on things and then miss opportunities that are out there.

If you spot an opportunity then weigh up the pros and cons of the opportunity, what it will cost and the expected outcome. If it still looks like a good idea and the costs weigh up then you should do it.

Just as equally, when something goes wrong, review what happened, analyse what you could have done about it and then work out a way forward.

Obviously there are exceptions to the rule but by remaining proactive and reactive to situations you will ensure that you're more likely to survive.

In short, don't over think things.
 

Don't forget to follow me on Twitter.

# Wednesday, November 27, 2013

Developing with Umbraco and Git (or any other source control system)

Wednesday, November 27, 2013 11:52:20 PM (GMT Standard Time, UTC+00:00)

I've had a couple of people ask me recently how we use source control (in our case Git but this should work with Mercurial) with Umbraco and within a team. This is more of a discussion post and I'm interested to learn more how you've solved it so leave a comment!

In short -we only store those files which are modified from the default install in our source control system.

By only storing the modified files in source control it allows you to quickly see which files you need to update should you want to update. It also gives you clarity as your repo isn't clogged full of lots of files.

If you run your sites locally then the best thing to do is have your IIS folder outside your Git directory and copy the files as you change them into the IIS folder you can use something like robocopy to do that.

So to summarise, our "general" repo structure looks like this:

  • App_Code
  • App_Data
  • App_Start
  • config
    • umbracoSettings.config
  • css
  • img
    • macroScripts
    • Scripts
    • Views
    • web.config

As development continues, if you change any of the standard files, add them to your repo.

Let me know what you do.

 

Don't forget to follow me on Twitter.

# Thursday, November 21, 2013

Delay sending an email until the next working day

Thursday, November 21, 2013 9:43:55 PM (GMT Standard Time, UTC+00:00)

I know there are a fair few people that have automatic delays on their email to avoid sending emails too quickly but for some time now I've wanted to restrict emails so they're only send during office hours -to avoid getting into email conversations on the weekend.

I was looking around and I came across this VBA script to delay emails which was a good start but it had a few flaws to it.

Outlook being Outlook, it's a little tricky to install a VBA script so it's probably easiest to check Google for that side of things but here's an adapted version of the script which will delay sending emails until 9am the next working day.

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)

    On Error GoTo ErrHand               ' Error Handling
    
    Dim objMailItem As MailItem         ' Object to hold mail item
    Dim SendDate As String              ' The date to send delayed mail
    Dim SendTime As String              ' The time to send delayed mail
    Dim DelayMailAfter As Integer       ' Latest hour mail is sent live
    Dim DelayMailBefore As Integer      ' Earliest hour to send mail live
    Dim DelayForDays As Integer         ' The number of days to delay the email for
    Dim MailIsDelayed As Boolean        ' Set if the mail will be delayed
    Dim NoDeferredDelivery As String    ' Value if deferred delivery is disabled
    
    SendTime = " 09:00:00"              ' Time to deliver delayed mail (6AM)
    DelayMailBefore = 9                 ' Delay mail sent before 5:00AM
    DelayMailAfter = 17                 ' Delay mail sent after 8:59PM
    DelayForDays = 0                    ' Default to sending emails the same day
    MailIsDelayed = True                ' We assume it's delayed by default
    NoDeferredDelivery = "1/1/4501"     ' Magic number Outlook uses for "delay mail box isn't checked"
    
    Set objMailItem = Item ' Outlook.ActiveInspector.CurrentItem
    
    ' Check and make sure current item is an unsent message
    If objMailItem.Sent = True Then
        Err.Raise 9000
    End If
        
    If Weekday(Date, vbMonday) = 6 Then ' Today is Saturday, delay mail two days
        DelayForDays = 2
    ElseIf Weekday(Date, vbMonday) = 7 Then ' Today is Sunday, delay mail one day
        DelayForDays = 1
    Else ' Currently a weekday
        If DatePart("h", Now) < DelayMailBefore Then ' It's early morning - delay it
            DelayForDays = 0
        ElseIf DatePart("h", Now) > DelayMailAfter Then ' It's late night - delay it until tomorrow morning
            If Weekday(Date, vbMonday) = 5 Then ' Today is Friday, delay mail until Monday
                DelayForDays = 3
            Else
                DelayForDays = 1
            End If
        Else
            MailIsDelayed = False
        End If
    End If
    
    If MailIsDelayed And objMailItem.DeferredDeliveryTime = NoDeferredDelivery Then
        Dim NewSendDate As Date
        NewSendDate = (Date + DelayForDays) & SendTime
        
        ans = MsgBox("Out of hours - do you want to delay mail until " & NewSendDate & "?", vbYesNo, "Delay out of hours mail?")
        
        If ans = vbYes Then
            objMailItem.DeferredDeliveryTime = NewSendDate
        Else
            objMailItem.DeferredDeliveryTime = NoDeferredDelivery
        End If
    End If

    Exit Sub

ErrHand:
    ' Handle well-known errors with message
    ' Other errors, just tell the user
    If Err.Number = 13 Then
        ' No current item or current item isn't a mail message
        ' MsgBox "Future delivery can only be set on mail items", vbOKOnly, "Not a mail item"
        End Sub
    ElseIf Err.Number = 9000 Then
        ' The active message has already been sent
        MsgBox "Please run this macro from an unsent mail item", vbOKOnly, "Not an unsent mail item"
    Else
        MsgBox "An error has occured on line " & Erl & _
                ", with a description: " & Err.Description & _
                ", and an error number " & Err.Number
    End If
        
End Sub
 

Don't forget to follow me on Twitter.

# Friday, August 16, 2013

Add every country to uCommerce

Friday, August 16, 2013 4:56:41 PM (GMT Daylight Time, UTC+01:00)

Something I learnt the other day was that uCommerce uses the culture code you specify for a country to determine the two/three digit ISO code when sending the order to the payment gateway.

This is fine if you know all the various countries -but I didn't so here's a SQL script that will create them all for you. It assumes that you have the default set of countries that are installed with uCommerce and haven't add any yourself. If you have, you may need to alter a few more of the INSERT statements to be UPDATE.

I've had to make some assumptions on which culture code is the main one e.g az-Latn-AZ but have gone for Latin each time. I suspect that this list is also missing a few -if you spot some let me know.

Remember; use at your own risk!

-- Remove any existing country/shipping joins
DELETE FROM uCommerce_ShippingMethodCountry

-- Insert the new countries (and update those that already exist)

SET IDENTITY_INSERT dbo.uCommerce_Country ON

INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (1, 'Afghanistan','ps-AF' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (2, 'Albania','sq-AL' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (3, 'Algeria','ar-DZ' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (4, 'Argentina','es-AR' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (5, 'Armenia','hy-AM' ,'0') 
UPDATE uCommerce_Country SET Name = 'Australia', Culture = 'en-AU' , Deleted='0' WHERE CountryId = 6 
UPDATE uCommerce_Country SET Name = 'Austria', Culture = 'de-AT' , Deleted='0' WHERE CountryId = 7 
UPDATE uCommerce_Country SET Name = 'Azerbaijan', Culture = 'az-Cyrl-AZ' , Deleted='0' WHERE CountryId = 8 
UPDATE uCommerce_Country SET Name = 'Bahrain', Culture = 'ar-BH' , Deleted='0' WHERE CountryId = 9 
UPDATE uCommerce_Country SET Name = 'Bangladesh', Culture = 'bn-BD' , Deleted='0' WHERE CountryId = 10 
UPDATE uCommerce_Country SET Name = 'Belarus', Culture = 'be-BY' , Deleted='0' WHERE CountryId = 11 
UPDATE uCommerce_Country SET Name = 'Belgium', Culture = 'nl-BE' , Deleted='0' WHERE CountryId = 12 
UPDATE uCommerce_Country SET Name = 'Belize', Culture = 'en-BZ' , Deleted='0' WHERE CountryId = 13 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (14, 'Bolivarian Republic of Venezuela','es-VE' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (15, 'Bolivia','es-BO' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (16, 'Bosnia and Herzegovina','hr-BA' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (17, 'Botswana','tn-BW' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (18, 'Brazil','pt-BR' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (19, 'Brunei Darussalam','ms-BN' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (20, 'Bulgaria','bg-BG' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (21, 'Cambodia','km-KH' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (22, 'Canada','en-CA' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (23, 'Caribbean','en-029' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (24, 'Chile','es-CL' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (25, 'China','zh-CN' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (26, 'Colombia','es-CO' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (27, 'Costa Rica','es-CR' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (28, 'Croatia','hr-HR' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (29, 'Czech Republic','cs-CZ' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (30, 'Denmark','da-DK' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (31, 'Dominican Republic','es-DO' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (32, 'Ecuador','es-EC' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (33, 'Egypt','ar-EG' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (34, 'El Salvador','es-SV' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (35, 'Eritrea','ti-ER' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (36, 'Estonia','et-EE' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (37, 'Ethiopia','ti-ET' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (38, 'Faroe Islands','fo-FO' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (39, 'Finland','fi-FI' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (40, 'France','fr-FR' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (41, 'Georgia','ka-GE' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (42, 'Germany','de-DE' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (43, 'Greece','el-GR' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (44, 'Greenland','kl-GL' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (45, 'Guatemala','es-GT' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (46, 'Honduras','es-HN' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (47, 'Hong Kong SAR','zh-HK' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (48, 'Hungary','hu-HU' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (49, 'Iceland','is-IS' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (50, 'India','hi-IN' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (51, 'Indonesia','id-ID' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (52, 'Iran','fa-IR' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (53, 'Iraq','ar-IQ' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (54, 'Ireland','en-IE' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (55, 'Israel','he-IL' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (56, 'Italy','it-IT' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (57, 'Jamaica','en-JM' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (58, 'Japan','ja-JP' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (59, 'Jordan','ar-JO' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (60, 'Kazakhstan','kk-KZ' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (61, 'Kenya','sw-KE' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (62, 'Korea','ko-KR' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (63, 'Kuwait','ar-KW' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (64, 'Kyrgyzstan','ky-KG' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (65, 'Lao PDR','lo-LA' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (66, 'Latvia','lv-LV' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (67, 'Lebanon','ar-LB' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (68, 'Libya','ar-LY' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (69, 'Liechtenstein','de-LI' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (70, 'Lithuania','lt-LT' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (71, 'Luxembourg','lb-LU' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (72, 'Macao SAR','zh-MO' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (73, 'Macedonia (Former Yugoslav Republic of Macedonia)','mk-MK' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (74, 'Malaysia','ms-MY' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (75, 'Maldives','dv-MV' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (76, 'Malta','mt-MT' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (77, 'Mexico','es-MX' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (78, 'Mongolia','mn-MN' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (79, 'Montenegro','sr-Latn-ME' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (80, 'Morocco','ar-MA' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (81, 'Nepal','ne-NP' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (82, 'Netherlands','nl-NL' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (83, 'New Zealand','en-NZ' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (84, 'Nicaragua','es-NI' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (85, 'Nigeria','ig-NG' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (86, 'Norway','nb-NO' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (87, 'Oman','ar-OM' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (88, 'Pakistan','ur-PK' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (89, 'Panama','es-PA' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (90, 'Paraguay','es-PY' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (91, 'Peru','es-PE' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (92, 'Philippines','fil-PH' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (93, 'Poland','pl-PL' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (94, 'Portugal','pt-PT' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (95, 'Principality of Monaco','fr-MC' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (96, 'Puerto Rico','es-PR' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (97, 'Qatar','ar-QA' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (98, 'Romania','ro-RO' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (99, 'Russia','ru-RU' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (100, 'Rwanda','rw-RW' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (101, 'Saudi Arabia','ar-SA' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (102, 'Senegal','wo-SN' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (103, 'Serbia','sr-Latn-RS' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (104, 'Serbia and Montenegro (Former)','sr-Latn-CS' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (105, 'Singapore','zh-SG' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (106, 'Slovakia','sk-SK' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (107, 'Slovenia','sl-SI' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (108, 'South Africa','tn-ZA' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (109, 'Spain','es-ES' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (110, 'Sri Lanka','si-LK' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (111, 'Sweden','se-SE' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (112, 'Switzerland','fr-CH' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (113, 'Syria','syr-SY' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (114, 'Taiwan','zh-TW' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (115, 'Tajikistan','tg-Cyrl-TJ' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (116, 'Thailand','th-TH' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (117, 'Trinidad and Tobago','en-TT' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (118, 'Tunisia','ar-TN' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (119, 'Turkey','tr-TR' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (120, 'Turkmenistan','tk-TM' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (121, 'U.A.E.','ar-AE' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (122, 'Ukraine','uk-UA' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (123, 'United Kingdom','en-GB' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (124, 'United States','en-US' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (125, 'Uruguay','es-UY' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (126, 'Uzbekistan','uz-Latn-UZ' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (127, 'Vietnam','vi-VN' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (128, 'Yemen','ar-YE' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (129, 'Zimbabwe','en-ZW' ,'0') 

SET IDENTITY_INSERT dbo.uCommerce_Country OFF

Enable every country for every shipping method

If you want to enable all shipping methods for all countries you can use this script:

INSERT INTO uCommerce_ShippingMethodCountry 
SELECT csm.ShippingMethodId, cc.CountryId 
FROM uCommerce_Country cc, uCommerce_ShippingMethod csm

Enable every country for every payment method

Finally, if you want to enable all payment methods for all countries you can use this script:

INSERT INTO uCommerce_PaymentMethodCountry
SELECT pm.PaymentMethodId, cc.CountryId 
FROM uCommerce_Country cc, uCommerce_PaymentMethod AS pm

Hope it helps you

 

Don't forget to follow me on Twitter.

Add every country to uCommerce
Useful Links:  #  digg it!  del.icio.us  Technorati  email it!  Post CommentsComments [0]  Trackback LinkTrackback
CategoriesTags: SQL | uCommerce | Useful Script