Bringing Location to the Internet

Determine Web Visitors Country of Origin in the Drop Down List using ASP and MS-SQL Database

With the emergence of online technologies such as the Internet, people and businesses have increased their reliance and use of these mediums as an avenue for commerce as it can be more convenient. During the transaction online, there are times when it is important to preset the web visitor's country of origin, ZIP code, ISP and domain name at the drop down list to prevent fraud and to ease the complexity of registration task. This article shows you how by using ASP and MS-SQL Technology, it can be done.

Let us take a simple example of a user login from Canada and he needs to fill up a shopping cart. The form may be quite complex as some businesses need more information to prevent fraud. In this case, there are needs to preset certain info in the drop down list such as country of origin, ZIP code, ip and domain name of where the users login. As a result, the drop down list in this example will preset to Cananda, with the correct zip code and ip address.

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: