Display Visitor’s Country Currency Using ASP and MS-SQL Database | IP2Location

Display Visitor’s Country Currency Using ASP and MS-SQL Database | IP2Location.

 

CREATE DATABASE ip2location;
USE ip2location;

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

INSERT INTO ip_country VALUES (0, 2130706431,'US','UNITED STATES');
INSERT INTO ip_country VALUES (2130706432, 4294967295,'JP','JAPAN');

CREATE TABLE [dbo].[country] (
	[iso] [nvarchar] (2),
	[country_name][nvarchar] (100),
	[capital_name] [nvarchar] (100),
	[tld] [nvarchar] (3),
	[currency_code] [nvarchar] (3),
	[currency_name] [nvarchar] (50)
) ON [PRIMARY]
GO
<%
  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")
 < 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")

' SQL query to lookup currency code
  mySQL2 = "SELECT currency_code FROM country WHERE tld='" & countryName & "'"

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

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

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

  If CountryName = "JP" Then
  ' Visitor is from Japan
  ' Show price in YEN
  Response.Write "Price: " & currency_code & " 120.00"
  Else
  ' Visitor is not from Japan
  ' Show price in USD
  Response.Write "Price: " & currency_code & " 1.00"
  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
%>
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s