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.

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://www.ip2location.com/databases/db1-ip-country.

Sample Code:

<select name="country">
<%
 Dim conn, myDNS, mySQL, rs
 
 ' SQL database connection. NOTE: Replace servername, usernameandpasswordtoyourownvalues. Setconn=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
 CountryCode = rs("country_code")
 
 ' close and free connections
 rs.close
 conn.close
 set rs = nothing
 Set conn = nothing
 
 ' function to convert IP address to IP number
 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
 
countries = "AF|AFGHANISTAN#AL|ALBANIA#DZ|ALGERIA#AS|AMERICAN SAMOA#AD|ANDORRA#AO|ANGOLA#AI|ANGUILLA#AQ|ANTARCTICA#AG|ANTIGUA AND BARBUDA#AR|ARGENTINA#AM|ARMENIA#AW|ARUBA#AP|ASIA PACIFIC#AU|AUSTRALIA#AT|AUSTRIA#AZ|AZERBAIJAN#BS|BAHAMAS#BH|BAHRAIN#BD|BANGLADESH#BB|BARBADOS#BY|BELARUS#BE|BELGIUM#BZ|BELIZE#BJ|BENIN#BM|BERMUDA#BT|BHUTAN#BO|BOLIVIA#BA|BOSNIA AND HERZEGOWINA#BW|BOTSWANA#BV|BOUVET ISLAND#BR|BRAZIL#IO|BRITISH INDIAN OCEAN TERRITORY#BN|BRUNEI DARUSSALAM#BG|BULGARIA#BF|BURKINA FASO#BI|BURUNDI#KH|CAMBODIA#CM|CAMEROON#CA|CANADA#CV|CAPE VERDE#KY|CAYMAN ISLANDS#CF|CENTRAL AFRICAN REPUBLIC#TD|CHAD#CL|CHILE#CN|CHINA#CX|CHRISTMAS ISLAND#CC|COCOS (KEELING) ISLANDS#CO|COLOMBIA#KM|COMOROS#CG|CONGO#CD|CONGO, THE DEMOCRATIC REPUBLIC OF THE#CK|COOK ISLANDS#CR|COSTA RICA#CI|COTE D'IVOIRE#HR|CROATIA#CU|CUBA#CY|CYPRUS#CZ|CZECH REPUBLIC#CS|CZECHOSLOVAKIA (FORMER)#DK|DENMARK#DJ|DJIBOUTI#DM|DOMINICA#DO|DOMINICAN REPUBLIC#TP|EAST TIMOR#EC|ECUADOR#EG|EGYPT#SV|EL SALVADOR#GQ|EQUATORIAL GUINEA#ER|ERITREA#EE|ESTONIA#ET|ETHIOPIA#EU|EUROPEAN UNION#FK|FALKLAND ISLANDS (MALVINAS)#FO|FAROE ISLANDS#FJ|FIJI#FI|FINLAND#FR|FRANCE#FX|FRANCE, METROPOLITAN#GF|FRENCH GUIANA#PF|FRENCH POLYNESIA#TF|FRENCH SOUTHERN TERRITORIES#GA|GABON#GM|GAMBIA#GE|GEORGIA#DE|GERMANY#GH|GHANA#GI|GIBRALTAR#GB|GREAT BRITAIN#GR|GREECE#GL|GREENLAND#GD|GRENADA#GP|GUADELOUPE#GU|GUAM#GT|GUATEMALA#GN|GUINEA#GW|GUINEA-BISSAU#GY|GUYANA#HT|HAITI#HM|HEARD ISLAND AND MCDONALD ISLANDS#VA|HOLY SEE (VATICAN CITY STATE)#HN|HONDURAS#HK|HONG KONG#HU|HUNGARY#IS|ICELAND#IN|INDIA#ID|INDONESIA#IR|IRAN, ISLAMIC REPUBLIC OF#IQ|IRAQ#IE|IRELAND#IL|ISRAEL#IT|ITALY#JM|JAMAICA#JP|JAPAN#JO|JORDAN#KZ|KAZAKSTAN#KE|KENYA#KI|KIRIBATI#KP|KOREA, DEMOCRATIC PEOPLE'S REPUBLIC OF#KR|KOREA, REPUBLIC OF#KW|KUWAIT#KG|KYRGYZSTAN#LA|LAO PEOPLE'S DEMOCRATIC REPUBLIC#LV|LATVIA#LB|LEBANON#LS|LESOTHO#LR|LIBERIA#LY|LIBYAN ARAB JAMAHIRIYA#LI|LIECHTENSTEIN#LT|LITHUANIA#LU|LUXEMBOURG#MO|MACAU#MK|MACEDONIA, THE FORMER YUGOSLAV REPUBLIC OF#MG|MADAGASCAR#MW|MALAWI#MY|MALAYSIA#MV|MALDIVES#ML|MALI#MT|MALTA#MH|MARSHALL ISLANDS#MQ|MARTINIQUE#MR|MAURITANIA#MU|MAURITIUS#YT|MAYOTTE#MX|MEXICO#FM|MICRONESIA, FEDERATED STATES OF#MD|MOLDOVA, REPUBLIC OF#MC|MONACO#MN|MONGOLIA#MS|MONTSERRAT#MA|MOROCCO#MZ|MOZAMBIQUE#MM|MYANMAR#NA|NAMIBIA#NR|NAURU#NP|NEPAL#NL|NETHERLANDS#AN|NETHERLANDS ANTILLES#NC|NEW CALEDONIA#NZ|NEW ZEALAND#NI|NICARAGUA#NE|NIGER#NG|NIGERIA#NU|NIUE#NF|NORFOLK ISLAND#MP|NORTHERN MARIANA ISLANDS#NO|NORWAY#OM|OMAN#PK|PAKISTAN#PW|PALAU#PS|PALESTINIAN TERRITORY, OCCUPIED#PA|PANAMA#PG|PAPUA NEW GUINEA#PY|PARAGUAY#PE|PERU#PH|PHILIPPINES#PN|PITCAIRN#PL|POLAND#PT|PORTUGAL#PR|PUERTO RICO#QA|QATAR#RE|REUNION#RO|ROMANIA#SU|RUSSIAN FEDERATION#RW|RWANDA#SH|SAINT HELENA#KN|SAINT KITTS AND NEVIS#LC|SAINT LUCIA#PM|SAINT PIERRE AND MIQUELON#VC|SAINT VINCENT AND THE GRENADINES#WS|SAMOA#SM|SAN MARINO#ST|SAO TOME AND PRINCIPE#SA|SAUDI ARABIA#SN|SENEGAL#YU|SERBIA AND MONTENEGRO#SC|SEYCHELLES#SL|SIERRA LEONE#SG|SINGAPORE#SK|SLOVAKIA#SI|SLOVENIA#SB|SOLOMON ISLANDS#SO|SOMALIA#ZA|SOUTH AFRICA#GS|SOUTH GEORGIA AND THE SOUTH SANDWICH ISLANDS#ES|SPAIN#LK|SRI LANKA#SD|SUDAN#SR|SURINAME#SJ|SVALBARD AND JAN MAYEN#SZ|SWAZILAND#SE|SWEDEN#CH|SWITZERLAND#SY|SYRIAN ARAB REPUBLIC#TW|TAIWAN, PROVINCE OF CHINA#TJ|TAJIKISTAN#TZ|TANZANIA, UNITED REPUBLIC OF#TH|THAILAND#TG|TOGO#TK|TOKELAU#TO|TONGA#TT|TRINIDAD AND TOBAGO#TN|TUNISIA#TR|TURKEY#TM|TURKMENISTAN#TC|TURKS AND CAICOS ISLANDS#TV|TUVALU#UG|UGANDA#UA|UKRAINE#AE|UNITED ARAB EMIRATES#UK|UNITED KINGDOM#US|UNITED STATES#UM|UNITED STATES MINOR OUTLYING ISLANDS#UY|URUGUAY#UZ|UZBEKISTAN#VU|VANUATU#VE|VENEZUELA#VN|VIET NAM#VG|VIRGIN ISLANDS, BRITISH#VI|VIRGIN ISLANDS, U.S.#WF|WALLIS AND FUTUNA#EH|WESTERN SAHARA#YE|YEMEN#ZM|ZAMBIA#ZW|ZIMBABWE"
 
countrylist = "</select>" & vbcrlf
 
arr1 = split(countries, "#")
 
for each x in arr1
    arr2 = split(x, "|")
    countrylist = countrylist & "<option value="" "="" &="" arr2(0)="" """"="" if="" countrycode="arr2(0)" then="" countrylist="countrylist" selected"="" end="">" & arr2(1) & "</option>" & vbcrlf
next
 
countrylist = countrylist & vbcrlf & ""
 
response.write countrylist
 
%>