ip2location facebbook  ip2location twitter  ip2location google+ ip2location github

Display Visitor's Country Currency Using ASP and MS-SQL Database

E-commerce businesses often have to deal with customers from around the globe. However, some customers may have difficulty to convert the currency displayed to their origin currency. It’s user friendly to show the pricing in a website in visitor origin currency. With the origin currency, visitors can navigate faster and feel more comfortable to make purchases.

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

CREATE DATABASE ip2location
GO

USE ip2location
GO

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]\IP-COUNTRY.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. The first line of the FMT code indicates the version of bcp. Please change the version according to your installed MS-SQL version.

SQL Server 201612.0
SQL Server 201412.0
SQL Server 201211.0
SQL Server 2008/2008R210.0
SQL Server 20059.0
SQL Server 20008.0
SQL Server 7.07.0
SQL Server 6.56.5

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

Step 4. Download world country information at here. Create 'country_currency' table

			CREATE TABLE [ip2location].[dbo].[country_currency] (
				[country_code] [nvarchar] (2),
				[currency_code] [nvarchar] (3),
				[currency_name] [nvarchar] (50)
			) ON [PRIMARY]
			GO

Step 5. Import the data into the table.

			BULK INSERT [ip2location].[dbo].[country_currency]
				FROM 'C:\[path to your CSV file]\IP2LOCATION-COUNTRY-INFORMATION.CSV'
				WITH
				(
					FORMATFILE = 'C:\[path to your country.FMT file]\country.FMT',
					FIRSTROW = 2
				)
			GO
			

NOTE: You will need to copy the FMT code below and save it as a file named country.FMT on your computer. The first line of the FMT code indicates the version of bcp. Please change the version according to your installed MS-SQL version.

SQL Server 201612.0
SQL Server 201412.0
SQL Server 201211.0
SQL Server 2008/2008R210.0
SQL Server 20059.0
SQL Server 20008.0
SQL Server 7.07.0
SQL Server 6.56.5

			10.0
			10
			1 SQLCHAR 0 1 "\"" 0 first_double_quote ""
			2 SQLCHAR 0 2 "\",\"" 1 country_code Latin1_General_CI_AI
			3 SQLCHAR 0 50 "\",\"" 0 capital ""
			4 SQLCHAR 0 50 "\",\"" 0 total_area ""
			5 SQLCHAR 0 50 "\",\"" 0 population ""
			6 SQLCHAR 0 3 "\",\"" 2 currency_code Latin1_General_CI_AI
			7 SQLCHAR 0 50 "\",\"" 3 currency_name Latin1_General_CI_AI
			8 SQLCHAR 0 2 "\",\"" 0 lang_code ""
			9 SQLCHAR 0 50 "\",\"" 0 lang_name ""
			10 SQLCHAR 0 2 "\"\r\n" 0 cctld ""
			

Sample Code:

<%
	Dim conn, myDSN, mySQL, rs, mySQL2, rs2

  ' 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 myDSN

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

' SQL query to lookup currency code
  mySQL2 = "SELECT currency_code FROM [ip2location].[dbo].[country_currency] WHERE country_code='" & countryCode & "'"

  Set rs2 = Server.CreateObject("ADODB.Recordset")
  rs2.open mySQL2, conn

' assign currency code for reference
  currency_code = rs2("currency_code")

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

  ' set the currency rate
  set currencyRate = Server.CreateObject("Scripting.Dictionary")
  currencyRate.Add "USD",1
  currencyRate.Add "JPY",119.91
  US_Price = 20.00
  Response.Write "Price : " & currency_code & " " & FormatNumber(currencyRate.Item(currency_code)* US_Price,2) 
  Response.Write " (Original. Price : USD " & FormatNumber(US_Price,2) & ")
" 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 %>

Do you like this article? Share it with others by clicking the social media buttons below. We will write more articles related to this topic.