Display Advertisement by Country Using ASP and MsSQL Database

Online advertising is another way to promote company products. It’s very important to show the right advertisements to the right consumers to have an optimum respond. A company selling their products in Japan showing their advertisement to visitors from United States is totally ineffective. On the other hand, localized advertisements catch visitor attention and improve sales.

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

Sample Code:

<%
  Dim conn, myDSN, mySQL, rs

  ' SQL database connection. NOTE: Replace servername, username and password to your own values.
  Set conn = Server.CreateObject("ADODB.Connection")
 <br> 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 country_code FROM ip_country WHERE " & IPno & " BETWEEN ip_from AND ip_to"

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

  ' assign country name for reference
  countryName = rs("country_code")

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

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