RegionCodes table of the transactionconfig database

Hi All,

I am a beginner in CS and am configuring Starter Site according to the needs of the project. I am after a more definite answer on this below mentioned question.

How would one go about configuring or adding countries in Starter Site for delivery addresses etc.

I have got an answer in one of my previous posts from Joe Wasson - MSFT, which is mentioned below:

This is exposed through the RegionCodeDataSet of Commerce Server. The data is contained in the RegionCodes table of the transactionconfig database. (I'm not sure if it is exposed through the Customer and Orders Manager UI, I don't have a box installed I can look at and no one is around

I could not find UI in Business User Application Managers to add a country, hence I did try to change the tables but it consists of the following fields:

Type, Code, Group, Name, Description, LastModified and Created.

Out of which few fields I can understand and know what data to put into but I cannot exactly make out what would go in type, code, group and name.

The trouble I fall across in the CS 2007 Starter Site is this. I add products to my shopping cart. Click on checkout and it would ask for my account log in (if I am not logged in). Once logged in it asks me to Enter or update shipping address information, which is fair enough. But in the Country dropdown box/Combo box there are no options at all. I want to populate these options by various countries.

Hope the above mentioned makes sense.

Many thanks.

Shriroop.

Answer this question

RegionCodes table of the transactionconfig database

jeRKer

Hi Joe,

I am trying to load State/Provice to dropdownlist when user selected the Country in the Starter Site. I used the RegionCodeDataSet object and get DataRow[] by call RegionCode.CountryCode.Row[selectedIndex].GetChildRows("States"). But I got in empty set.

Can you please tell us where is the RegionCodeDataSet Object (class), and GetChildRows(relation) where pass Relation, how can i get the Relation name or some other tips for it.

Thank you very much.
Cory P
Many thanks Colin, it does work and I have the countries all set up now.

Brilliant.

Cheers,
Shriroop.
Tonito01

Try this code:

public RegionCodeDataSet GetRegionCodes()

{

// New RegionCodeDataSet

RegionCodeDataSet dsRegions = new RegionCodeDataSet();

// Get the TypedDataSet from the OrderContext

dsRegions = CommerceContext.Current.OrderSystem.GetRegionCodes();

// Check to see if MSFT has already setup the relation for Beta and RC

if (dsRegions.Relations.Count < 1)

{

// Add a relation to each table in the Typed DataSet

dsRegions.Relations.Add("States", dsRegions.CountryCodes.Columns["Code"], dsRegions.StateCodes.Columns["CountryCode"]);

}

// Return the TypedDataSet

return dsRegions;

}

Let me know if you have any issues.

Jeff Lynch

MVP Windows Server System - Commerce Server

http://codebetter.com/blogs/jeff.lynch
Bert van Uitert
The SDK\SiteCreate\RegionData.sql script contains the out-of-the-box definitions for the region data. You can modify it as needed from there.

Personally I've found a number of gaps in ISO 3166-1 and 3166-2 regional data. Check www.statoids.com for a good blend of several sources of geographical information.

Cheers,
Colin
JimIngleton

Shriroop,

I do not believe region codes are part of the Sample Data Import wizard, I think they're just part of the packaged database.

If you want to unpackage just one part of a PUP, go through the Site Packager and select Unpack, then Custom. You should see eventually see a radio button that allows you to unpack specific resources to an existing site. I suppose you could just try unpacking the transactions config database. Not sure if this will work any better than the first time you unpacked this, though.
GoDaddy

Shriroop,

The RegionCodes table should've been populated correctly when you unPUPed the starter site or CSharpSite, as they are packaged in both of those files.

Based on looking at what I have on my image, I will attempt to explain what's in this database.

Type : 1 (Country) or 2 (State/Province).

Code: 2 or 3 letter code corresponding to country/state/province (such as "US" for United States, or "SK" for Saskatchewan).

Group: If type is 2, this needs to correspond to a country (Saskatchewan would need to map to "CA").

Name: Full name of country/state/province.

Description & Modified By: Not sure if this is needed

And then there are some timestamps, but I don't think you need to worry about this.
Alexander Ekzarov
Hi Joe,

I have followed your instructions and now I can view the country in the drop down box.

Perfect.

But again as you have mentioned - "The RegionCodes table should've been populated correctly when you unPUPed the starter site or CSharpSite, as they are packaged in both of those files." This has not happened at all. I did import the sample data and also the package unpacking did not have any errors that I can remember.

Would you be able to tell me if there is a way of populating the database again with these codes and having a list of those areas and the countries

Many thanks

Shriroop.
Alazekabi

Thank you very much Jeff. The code here-- dsRegions.Relations.Count < 1 is always true. So when i passed the relation "States" , I still got empty set.

Thank you in advance for further help.
molemenacer

This blog post shows the site code and helper code I used to do this. Please let me know if you have any other questions.

http://codebetter.com/blogs/jeff.lynch/archive/2006/07/12/143891.aspx

Jeff Lynch

MVP Windows Server System - Commerce Server

http://codebetter.com/blogs/jeff.lynch
RegionCodes table of the transactionconfig database

Answer this question