Private Sub txtIP3_TextChanged(sender As Object, e As EventArgs)
Handles txtIP3.TextChanged
If txtIP3.Text.Length = txtIP3.MaxLength _ Then SelectNextControl(ActiveControl, True, True, True, True)
End Sub
Tag: tutorial
VB.NET Module for SQL Database
Imports System.Data.SqlClient
Module modDatabase
Public conn As New SqlClient.SqlConnection
Public myReader As SqlDataReader
Public dsRecords As New DataSet
Public daRecords As New SqlDataAdapter
Public MaxRecords As Integer
Public Sub OpenDatabase()
CloseSQLReader()
CloseDatabase()conn.ConnectionString = “Server=.\sqlexpress;Database=DATABASE;Trusted_Connection=True;”
conn.Open()
End Sub
Public Sub CloseDatabase()
Try
conn.Close()
conn.Dispose()
Catch ex As Exception
Application.DoEvents()
End Try
End Sub
Public Sub launchBrowser(ByVal browser As String, ByVal url As String)
Try
Process.Start(browser, url)
Catch ex As Exception
Application.DoEvents()
‘MessageBox.Show(ex.Message)
End Try
End Sub
Public Sub CloseSQLReader()
Try
myReader.Close()
Catch ex As Exception
Application.DoEvents()
End Try
End Sub
Public Sub ReadSqlData(ByVal sqlSelect As String)
Dim myCommand As New SqlCommand(sqlSelect, conn)
myReader = myCommand.ExecuteReader()
End Sub
Public Function ExecuteSqlCommand(ByVal sqlSelect As String) As Boolean
Dim flg As Boolean = False
Dim myCommand As New SqlCommand(sqlSelect, conn)Try
flg = myCommand.ExecuteNonQuery
Catch
flg = False
End TryReturn flg
End Function
Public Sub HoldYourPants(ByVal Seconds As Integer)
System.Threading.Thread.Sleep(Seconds * 1000)
Application.DoEvents()
End Sub
End Module
SQL Delete Duplicate Records
This example shows how to remove duplicate uid records from a table named AD_Users
WITH CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY uid ORDER BY uid) AS Row, * FROM AD_Users
)
DELETE FROM CTE
WHERE ROW <> 1
SQL Encrypted Fields
This document shows step-by-step instructions for setting up encrypted fields in a SQL database table.
/* Create table and insert data in the table */
USE EncryptTest
GO
CREATE TABLE PasswordTable (SEQ INT, AdminPassword VARCHAR(50))
GO
INSERT INTO PasswordTable (SEQ, AdminPassword)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO
/* Check the content of the PasswordTable */
USE EncryptTest
GO
SELECT *
FROM PasswordTable
GO
/* Create Database Master Key */
USE EncryptTest
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'Password-1234'
GO
/* Create Encryption Certificate */
USE EncryptTest
GO
CREATE CERTIFICATE EncryptTestCert
WITH SUBJECT = 'Password-1234'
GO
/* Create Symmetric Key */
USE EncryptTest
GO
CREATE SYMMETRIC KEY PasswordTableKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE EncryptTestCert
GO
/* Encrypt Data using Key and Certificate
Add Columns which will hold the encrypted data in binary */
USE EncryptTest
GO
ALTER TABLE PasswordTable
ADD EncryptAdminPassword VARBINARY(256)
GO
/* Update binary column with encrypted data created by certificate & key */
USE EncryptTest
GO
OPEN SYMMETRIC KEY PasswordTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
UPDATE PasswordTable
SET EncryptAdminPassword = ENCRYPTBYKEY(KEY_GUID('PasswordTableKey'),AdminPassword)
GO
/* DROP original column which was encrypted to protect the data */
USE EncryptTest
GO
ALTER TABLE PasswordTable
DROP COLUMN AdminPassword
GO
/* Check the content of the PasswordTable */
USE EncryptTest
GO
SELECT *
FROM PasswordTable
GO
/* Decrypt the data of the AdminPassword */
USE EncryptTest
GO
OPEN SYMMETRIC KEY PasswordTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
SELECT SEQ, CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptAdminPassword)) AS DecryptAdminPassword, EncryptAdminPassword, RegStamp, DateStamp
FROM PasswordTable
GO
/* This is an example of adding a record with an encrypted field */
OPEN SYMMETRIC KEY PasswordTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
INSERT INTO PasswordTable
(EncryptAdminPassword, RegStamp, DateStamp)
VALUES(ENCRYPTBYKEY(KEY_GUID('PasswordTableKey'),'DogBreath91'),'test 2014.01','2014-01-15')
GO
SQL Summing daily totals
This example illustrates how to sum values collected daily into monthly categories.
SELECT DISTINCT CONVERT(varchar(4), YEAR(Date)) + '-' + RIGHT('00' + CAST(DATEPART(mm, Date) AS varchar(2)), 2) AS PERIOD, SUM(Steps) AS TotalSteps, SUM(Floors) AS TotalFloors, SUM(Distance_mi) AS TotalMiles, SUM(Activity) AS ActiveMinutes, SUM(Calories) AS TotalCalories FROM dbo.FitBit GROUP BY CONVERT(varchar(4), YEAR(Date)) + '-' + RIGHT('00' + CAST(DATEPART(mm, Date) AS varchar(2)), 2)