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

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

Hexasoft Development Sdn. Bhd. (645996-K)
1-2-15 Mayang Mall Complex,
Jalan Mayang Pasir 1,
11950 Bandar Bayan Baru,
Penang, Malaysia.
{ sales@ip2location.com }

The number of companies moved their business online is increasing. Selling products to world wide is dealing with multi national customers and currencies. However, some customers may have difficulty to convert the currency displayed to their origin currency. It’s user friendly to show the pricing in a website in visitor origin currency. With the origin currency, visitors can navigate faster and feel comfortable to make comparison.

In this tutorial, we use the IP2Location™IP-Country database to lookup country of origin from the visitor's IP address. Instead of loading the full database with 50000+ records, we could simplify this tutorial with assumption only two different IP address ranges in the world. IP addresses 0.0.0.0 - 126.255.255.255 originates from United States. Meanwhile, IP addresses 127.0.0.0 - 255.255.255.255 originate from Japan. Here we are creating a database "IP2Location" with table "IPCountry" that consists of two IP address range records.

Step 1: Start SQL Query Analyzer. Create and connect to 'IP2Location' database
CREATE DATABASE IP2Location;
USE IP2Location;

Step 2: Create 'IPCountry' table
CREATE TABLE [dbo].[IPCountry] (
[ipFROM] [float] NOT NULL ,
[ipTO] [float] NOT NULL ,
[countrySHORT] [nvarchar] (2),
[countryLONG] [nvarchar] (64)
) ON [PRIMARY]
GO

Step 3. Insert dummy records to 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 http://ip2location.com. If you have the full version of IP2Location™IP-Country database, import process is easy done by using the Database Transformation Service (DTS) in MS-SQL.

Step 4. Create '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]
) ON [PRIMARY]
GO

The country information is FREE at http://www.ip2location.com/countryinformation.aspx. It consists of 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. The import process can be done by using the Database Transformation Service (DTS) in MS-SQL.

<%
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={SQL Server};SERVER=servername;UID=username;PWD=password;DATABASE=IP2Location"

conn.open 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
mySQL = "SELECT countrySHORT FROM IPCountry WHERE " & IPno & " BETWEEN ipFROM AND ipTO"

Set rs = Server.CreateObject("ADODB.Recordset")
rs.open 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")
rs2.open mySQL2, conn

' assign currency code for reference
currencyCode = rs("currencyCode")

' close and free connections
rs.close
rs2.close
conn.close
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
   Else
   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
   Next
   End If
End Function
%>

To obtain permission to reuse or republish this article, please write to sales@ip2location.com. Republication is welcome for no charge.