Tag: tutorial

AutoTab to Next Field

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

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 Try

Return 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)