Monday, November 16, 2009

Show Visitor's Country Currency Using ASP and MS-SQL 2008 Database

More and more companies are realizing the importance of doing business online as a way of improving their market share. With this realization comes the need to deal with customers from around the world and their various currencies. This is when being able to show the pricing on a website in the visitor’s local currency would make the online shopping experience more user-friendly; eliminating the usual headache of foreign currency conversion.

In this tutorial, we will use the IP2Location™IP-Country database to determine the country of origin for the web visitor just by checking the IP address. Instead of loading the full database with 50000+ records, we could simplify this tutorial with the assumption that only two different IP address ranges in the world. IP addresses of the range - originates from the United States. Meanwhile, IP addresses of the range - originates from Japan. Here we are creating a database called "IP2Location" with a table called "IPCountry" which contains our two IP address range records.

Step 1: Start Microsoft SQL Server Management Studio.

Now, login to your database instance and then click on the New Query button. Copy the 2 lines of SQL codes below into the query window and press Execute to run the codes. This will create the ‘IP2Location’ database and select it for use.

USE IP2Location

Step 2: Next, run the SQL statement below to create the ‘IPCountry’ table.

CREATE TABLE[dbo].[IPCountry] (
[ipFROM] [float] NOTNULL ,
[ipTO] [float] NOT NULL ,
[countrySHORT] [nvarchar] (2),
[countryLONG] [nvarchar] (64)

Step 3: Run the SQL codes below to insert dummy data into the database.

INSERT INTO IPCountry VALUES (0, 2130706431,'US','UNITED STATES');
INSERT INTO IPCountry VALUES (2130706432, 4294967295,'JP','JAPAN');

The full version of IP-Country database is available for subscription at $49/year from

Step 4: Run the SQL statement below to create the 'Countries' table

CREATE TABLE [dbo].[Countries] (
[TLD] [nvarchar] (3),
[Country][nvarchar] (100),
[FIPS104][nvarchar] (2),
[ISO2] [nvarchar] (2),
[IOS3] [nvarchar] (3),
[Capital] [nvarchar] (100),
[Region] [nvarchar] (100),
[Currency] [nvarchar] (50),
[CurrencyCode] [nvarchar] (3),
[Population] [int]

The country information is available for FREE at . It consists of the top level domain (TLD), ISO-3166, country, capital, region, currency, currency code and population. Download and load the data into your database. It's available in several formats such as Comma-delimited ASCII, Microsoft® Access & Microsoft® Excel.

In your ASP page, you will need to put in the codes below.


Dim conn, myDSN, mySQL, rs, mySQL2, rs2

' SQL database connection. NOTE: Replace servername, username and password to your own values.

Set conn = Server.CreateObject("ADODB.Connection")

myDSN="DRIVER={SQLServer};SERVER=servername;UID=username;PWD=password;DATABASE=IP2Location" myDNS

' retrieve visitor IP address and translate it to IP address number

IPno = Dot2LongIP(Request.ServerVariables("REMOTE_ADDR"))

' SQL query to lookup valid range of IP address


Set rs = Server.CreateObject("ADODB.Recordset") mySQL, conn

' assign country name for reference

countryName = rs("countrySHORT")

' SQL query to lookup currency code

mySQL2 = "SELECT CurrencyCode FROM Countries WHERE TLD='" & countryName & "'"

Set rs2 = Server.CreateObject("ADODB.Recordset") mySQL2, conn

' assign currency code for reference

currencyCode = rs("currencyCode")

' close and free connections




set rs = nothing

set rs2 = nothing

Set conn = nothing

If CountryName = "JP" Then ' Visitor is from Japan

' Show price in YEN

Response.Write "Price: " & currencyCode & " 120.00"

Else ' Visitor is not from Japan

' Show price in USD

Response.Write "Price: " & currencyCode & " 1.00"

End If

Function Dot2LongIP (ByVal DottedIP)

Dim i, pos

Dim PrevPos, num

If DottedIP = "" Then

Dot2LongIP = 0


For i = 1 To 4

pos = InStr(PrevPos + 1, DottedIP, ".", 1)

If i = 4 Then

pos = Len(DottedIP) + 1

End If

num = Int(Mid(DottedIP, PrevPos + 1, pos - PrevPos - 1))

PrevPos = pos

Dot2LongIP = ((num Mod 256) * (256 ^ (4 - i))) + Dot2LongIP


End If

End Function %>

What you have seen here today is just the tip of the iceberg of what you can do with the IP2Location™IP-Country database. You could even pair the code above with a forex service to get a real-time conversion rate for the currencies thus making your currency conversions even more up-to-date. Now that you have realized the power of the IP2Location™IP-Country database, you will benefit even more from the more advanced databases available from IP2Location. You can try out the online demo at and see for yourself what you are missing.