Wednesday, November 18, 2009

Niche Advertising by Country using ASP and MS-SQL Database

Online advertisements are a big revenue earner for most websites. So it is no wonder that most of them employ ways to detect their web visitor’s country so that the advertisements can be targeted to its niche audience. By doing so, the probability of a sale from the online advertisement increases tremendously as the targeted audience would most likely find that advertisement to be relevant and maybe even helpful.

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

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

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

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

If CountryName = "JP" Then
' Visitor is from Japan
' Show advertisement from Japan
response.write "<img src=""Japan.jpg"" border=""0"" height=""200"" width=""100"">"
' Visitor is not from Japan
' Show other advertisement
response.write "<img src=""US.jpg"" border=""0"" height=""200"" width=""100"">"
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

IP2Location also carries a wide variety of databases which are more advanced and has more types of data which could also be used to further narrow down your target audience. You can try out the online demo at and see for yourself what you are missing.