Display Visitor’s Local Time Using VB.NET and MySQL Database

In this tutorial, we demonstrate you on how to display visitor’s local time based on their IP address using VB.NET programming languages and IP2Location MySQL database. In this tutorial, we use the IP2Location LITE database to lookup country of origin from the visitor’s IP address. Free databases are available for download at IP2Location LITE database.

Below are the steps to set up the database for both IPv4 and IPv6 data and the sample codes

Step 1: Download IP2Location LITE database, unzip the file follow the instruction in order to create database table. Please refer to DB11 LITE for further information.

Step 2: Download the demo project at Display visitors local time using VB.NET and include into your VB.NET project.

Retrieving country and timezone data from MySQL Database

	Protected Function Get_Data(ByVal query As String)
	Dim result As String
	result = ""
	'Database connection string, replace lower case with actual MySQL configuration
	Using Con As New MySqlConnection("Database=database;Server=server;User ID=username;Password=password;ignore prepare=false")
	Con.Open()
	Using Com As New MySqlCommand(query, Con)
        Com.CommandType = Data.CommandType.Text
        Using RDR = Com.ExecuteReader()
       If RDR.HasRows Then
          RDR.Read()
       'Assigning data retrieve from table into a string
          result = RDR.Item("time_zone").ToString() & "," & RDR.Item("country_code")
        End If
	End Using
	End Using
	End Using
	Return result
	End Function
 

Converting IP address to IP number

	Protected Function ip_to_number(ByVal ip_addr As String)
	Dim ip_block() As String
	Dim ip_num As Long
  	 ip_block = Split(ip_addr, ".")
	 ip_num = (ip_block(0) * (256 ^ 3)) + (ip_block(1) * (256 ^ 2)) + (ip_block(2) * 256) + ip_block(3)

	Return ip_num
	End Function

Retrieving UTC time

	Protected Function utc_hour()
	Dim utc_h As Integer
	utc_h = TimeOfDay.Hour - 8
	Return utc_h
	End Function

Retrieving client time

	Protected Function client_hour(ByVal time_zone As String)
	Dim utc_h As Integer
		utc_h = utc_hour()
		Dim time() As String
		time = Split(time_zone, ":")
		Dim client_h As Integer
		client_h = utc_h + time(0)

	If client_h >= 24 Then
		client_h = client_h - 24
	ElseIf client_h < 0 Then
		client_h = client_h + 24
	End If

	Return client_h
	End Function

Step 1: Download IP2Location LITE database, unzip the file follow the instruction in order to create database table. Please refer to DB11 LITE for further information.

Step 2: Download the demo project at Display visitors local time using VB.NET and include into your VB.NET project.

Retrieving country and timezone data from MySQL Database

	Protected Function Get_Data(ByVal query As String)
		Dim result As String
			result = ""
	'Database connection string, replace lower case with actual MySQL configuration
		Using Con As New MySqlConnection("Database=database;Server=server;User ID=username;Password=password;ignore prepare=false")
		Con.Open()
		Using Com As New MySqlCommand(query, Con)
		Com.CommandType = Data.CommandType.Text
		Using RDR = Com.ExecuteReader()
			If RDR.HasRows Then
				RDR.Read()
				'Assigning data retrieve from table into a string
				result = RDR.Item("time_zone").ToString() & "," & RDR.Item("country_code")
			End If
		End Using
		End Using
		End Using
		Return result
	End Function

Converting IP address to IP number

	Protected Function ip_to_number(ByVal ip_addr As String)
		Dim address As System.Net.IPAddress
		Dim ipnum As System.Numerics.BigInteger

		If System.Net.IPAddress.TryParse(ip_addr, address) Then
			Dim addrBytes() As Byte = address.GetAddressBytes()

			If System.BitConverter.IsLittleEndian Then
				Dim byteList As New System.Collections.Generic.List(Of Byte)(addrBytes)
				byteList.Reverse()
				addrBytes = byteList.ToArray()
			End If

			If addrBytes.Length > 8 Then
				'IPv6
				ipnum = System.BitConverter.ToUInt64(addrBytes, 8)
				ipnum <<= 64
				ipnum += System.BitConverter.ToUInt64(addrBytes, 0)
			Else
				'IPv4
				ipnum = System.BitConverter.ToUInt32(addrBytes, 0)
			End If
		End If
		Return ipnum
	End Function

Retrieving UTC time

	Protected Function utc_hour()
		Dim utc_h As Integer
		utc_h = TimeOfDay.Hour - 8
		Return utc_h
	End Function

Retrieving client time

	Protected Function client_hour(ByVal time_zone As String)
		Dim utc_h As Integer
		utc_h = utc_hour()
		Dim time() As String
		time = Split(time_zone, ":")
		Dim client_h As Integer
		client_h = utc_h + time(0)

		If client_h >= 24 Then
			client_h = client_h - 24
		ElseIf client_h < 0 Then
			client_h = client_h + 24
		End If

		Return client_h
	End Function

Was this article helpful?

Related Articles