Redirect Web Visitors By Country Using ASP and MS-SQL Database

There are times when it is useful to redirect a visitor to different default web page based on the visitor's country of origin. One practical usage is to redirect visitor to web page with the language recognized by the visitor. This article shows you how to build up such a system using ASP (server side scripting language) and MS-SQL (IP address to country lookup database).

Let us take a simple case study. Company XYZ is multi-national company with major customers from United States and Japan. The company official website is developed in both English and Japanese languages. The default page is in English language and visitor can switch to Japanese by changing the default language option. There exists a potential risk when a Japanese visitor does not understand English and he could not navigate the web site.

So let us developed a simple solution to help Company XYZ redirecting all Internet traffic from country Japan to the Japanese language site. Meanwhile it drives the traffic from the rest of the world to the corporate web site in English.

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 originate 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 "ip_country" 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 'ip_country' table

CREATE TABLE [dbo].[ip_country] (
	[ip_from] [float] NOT NULL,
	[ip_to] [float] NOT NULL,
	[country_code] [nvarchar] (2),
	[country_name] [nvarchar] (64)
	) ON [PRIMARY]
GO

Step 3. Insert dummy records to the database.

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

The full version of IP-Country database is available for subscription at $49/year from http://www.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. Download world country information at here. Create 'country' table

CREATE TABLE [dbo].[country] (
	[iso] [nvarchar] (2),
	[country_name][nvarchar] (100),
	[capital_name] [nvarchar] (100),
	[tld] [nvarchar] (3),
	[currency_code] [nvarchar] (3),
	[currency_name] [nvarchar] (50)
) ON [PRIMARY]
GO

Sample Codes:

<%
  Dim conn, myDSN, mySQL, rs

  ' 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"

  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")

  ' close and free connections
  rs.close
  conn.close
  set rs = nothing
  Set conn = nothing

  If CountryName = "JP" Then
  ' Visitor is from Japan
  ' Redirect the URL to Google Japanese site
  Response.Redirect "http://www.google.co.jp"
  Else
  ' Visitor is not from Japan
  ' Redirect the URL to Google international site
  Response.Redirect "http://www.google.com"
  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
%>