Author: joe

VB.NET Module for SQL Database

February 13, 2018 /

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

Identify all user keys in HKEY_USERS

January 5, 2018 /

This example shows how to enumerate the user keys in HKEY_USERS

Public Sub OutputRegKey(Key As RegistryKey)
Imports Microsoft.Win32

Public Class Form1
  Public arrUsers(100) As String, arrUsersMax As Integer = 0

  Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    Dim i As Integer
    Dim txt As String = ""

    '-------------------------------------------------------------------
    ' identify the HKEY_Users
    '-------------------------------------------------------------------
    arrUsers(0) = ".DEFAULT"

    Dim regkey As RegistryKey = Registry.Users.OpenSubKey("")
    EnumerateHkeyUsers(regkey)
    regkey.Close()

    For i = 0 To arrUsersMax
      txt += arrUsers(i) & vbCrLf
    Next

    MsgBox(txt)
    End
  End Sub

  Sub EnumerateHkeyUsers(ByVal rkey As RegistryKey)
    Dim names As String() = rkey.GetSubKeyNames()
    Dim txt As String = ""

    For Each subkey In names
      txt = subkey.ToString

      If Len(txt) > 10 And InStr(LCase(txt), "_classes") = 0 Then
        arrUsersMax = arrUsersMax + 1
        arrUsers(arrUsersMax) = txt
      End If
    Next
  End Sub

End Class

SQL Left Function

September 27, 2017 /

The LEFT function allows you to extract a substring from a string, starting from the left-most character.

LEFT( string, number_of_characters )

Example:

SELECT LEFT('TechOnTheNet.com', 4);
Result: 'Tech'

SQL Delete Duplicate Records

September 19, 2017 /

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

Read Values From ListBox and Reference a 2-Dimensional Array

July 20, 2017 /

This example shows how to read entries from a ListBox and reference an array which contains numeric values corresponding to the selection.  This is useful if you want to do logic AND statements with the selection from the ListBox.

I used this method to assign numeric tags to the ListBox items.  This allowed me to assign a unique value to each combination of selections made.

Public Class Form1
 Dim arrTag(2, 5) As String, arrTagMax As Integer

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
 LoadArray()
 End Sub

Private Sub ListBox1_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles lstACLGroups.SelectedIndexChanged
 Dim txt As String = ""
 Dim acc As Integer = 0

For i = 0 To lstACLGroups.SelectedItems.Count - 1
 txt += lstACLGroups.SelectedItems(i).ToString & vbCrLf

For j = 1 To 5
 If lstACLGroups.SelectedItems(i).ToString = arrTag(2, j) Then
 acc = acc + CInt(arrTag(1, j))
 End If
 Next
 Next

 TextBox1.Text = txt
 TextBox2.Text = acc.ToString
 End Sub

Sub LoadArray()
 Dim i As Integer

arrTag(1, 1) = 1
arrTag(2, 1) = "1 apple"

arrTag(1, 2) = 2
arrTag(2, 2) = "2 banana"

arrTag(1, 3) = 4
arrTag(2, 3) = "4 coconut"

arrTag(1, 4) = 8
arrTag(2, 4) = "8 dog"

arrTag(1, 5) = 16
arrTag(2, 5) = "16 elephant"

lstACLGroups.Items.Clear()
 For i = 1 To 5
   lstACLGroups.Items.Add(arrTag(2, i))
 Next

End Sub
End Class