Tag: example

SQLite Truncate a Table

May 24, 2017 /

The syntax for truncating a table in SQLite differs from that of SQL or mySQL.  Truncation means that all records are deleted but the table structure is left in place.  In addition, the record counter is reset to zero.

To truncate a table in SQLite, the following two commands are used:

Delete from TableName;
DELETE FROM SQLITE_SEQUENCE WHERE name='TableName';

SQL Summing daily totals

May 18, 2017 /

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)

Replacing a string in each user’s profile

May 18, 2017 /

This routine replaces a string in the text file user.js.  The file is located in each user’s profile which also contains a random string as part of the path name.

Imports System.Net
Imports System.IO

Public Class Form1

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
 Dim strPath As String = "c:\users"
 Dim arrFolders() As String = System.IO.Directory.GetDirectories(strPath)
 Dim MozPath As String

'----------------------------------------------------------------
 ' get list of profiles
 '----------------------------------------------------------------
 For Each arrFolder As String In arrFolders
 MozPath = arrFolder & "\AppData\Roaming\Mozilla\Firefox\Profiles\"

If My.Computer.FileSystem.DirectoryExists(MozPath) = True Then
 Dim arrGUIDpath() As String = System.IO.Directory.GetDirectories(MozPath)

For Each arrGUIDfolder As String In arrGUIDpath
 If My.Computer.FileSystem.FileExists(arrGUIDfolder & "\user.js") = True Then
 Console.WriteLine("notepad " & arrGUIDfolder & "\user.js")

'----------------------------------------------------------------
 ' 1. rename the user.js file
 ' 2. replace the proxy defintion string tinto user.js
 '----------------------------------------------------------------
 Try
 My.Computer.FileSystem.DeleteFile(arrGUIDfolder & "\user2.js")
 Catch ex As Exception
 Application.DoEvents()
 End Try

My.Computer.FileSystem.CopyFile(arrGUIDfolder & "\user.js", arrGUIDfolder & "\user2.js")

My.Computer.FileSystem.WriteAllText(arrGUIDfolder & "\user.js", _
 My.Computer.FileSystem.ReadAllText(arrGUIDfolder & "\user2.js").Replace("user_pref(""network.proxy.type"", 4);", _
 "user_pref(""network.proxy.type"", 0);"), False)
 End If

'----------------------------------------------------------------
 ' clean up to avoid confusion in the future
 '----------------------------------------------------------------
 Application.DoEvents()

Try
 My.Computer.FileSystem.DeleteFile(arrGUIDfolder & "\user2.js")
 Catch ex As Exception
 Application.DoEvents()
 End Try
 Next
 End If

Next
 End
 End Sub

End Class

SQL Insert if not exists

April 4, 2016 /

This example shows how to insert a record in an SQL table only if it does not currently exist.

if not exists
 (SELECT * FROM TEST_Table
 WHERE Center = '8882'
 AND Drive = 'K' AND Path = '\\DEV2\WORKGROUP')
 INSERT INTO TEST_Table (Center, Drive, Path, Comment)
 VALUES ('8882','K','\\DEV2\WORKGROUP','Stage Layout')

SQLite syntax:

INSERT OR REPLACE INTO topics (tag, topic) 
SELECT 'GEN:03', 'THE FALL'
WHERE NOT EXISTS 
 (SELECT * FROM topics WHERE tag = 'GEN:03' AND topic = 'THE FALL');

Authenticate User to Security Group

July 27, 2015 /

Imports System.Security.Principal

Public Class Form1
Public id As WindowsIdentity = WindowsIdentity.GetCurrent()
Public User As WindowsPrincipal = New WindowsPrincipal(id)

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Dim flg As Boolean = User.IsInRole(“Domain\Security Group”)

Select Case flg
Case True
MsgBox(“I am a member of the group”)
Case False
MsgBox(“Not a member”)
End Select

End
End Sub

End Class