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

Step 1: Start the SQL Server Management Studio. Run the following SQL commands.

CREATE DATABASE ip2location;
USE ip2location;

Step 2: Create ‘ip_country’ table

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

Step 3. Import the data into the table.

BULK INSERT [ip2location].[dbo].[ip_country]
    FROM 'C:\[path to your CSV file]\IPCountry.CSV'
    WITH
    (
        FORMATFILE = 'C:\[path to your DB1.FMT file]\DB1.FMT'
    )
GO

NOTE: You will need to copy the FMT code below and save it as a file named DB1.FMT on your computer.

10.0
5
1 SQLCHAR 0 1 “\”” 0 first_double_quote Latin1_General_CI_AI
2 SQLCHAR 0 20 “\”,\”” 1 ip_from “”
3 SQLCHAR 0 20 “\”,\”” 2 ip_to “”
4 SQLCHAR 0 2 “\”,\”” 3 country_code Latin1_General_CI_AI
5 SQLCHAR 0 64 “\”\r\n” 4 country_name Latin1_General_CI_AI

Run the below SQL command to create a clustered index on the ip_to field.

CREATE UNIQUE CLUSTERED INDEX [ip_to] ON [ip2location].[dbo].[ip_country]
(
    [ip_to] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

The full version of IP-Country database is available for subscription at $49/year at https://ip2location.com/databases/db1-ip-country.

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")
  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 TOP 1 * FROM [ip2location].[dbo].[ip_country] WHERE ip_to >=" & IPno & " ORDER BY 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
%>