Tag: syntax

SQL Delete duplicate rows based on column

May 30, 2019 /

To remove duplicate rows, use a CTE and partition using the data you want to check for duplicates to assign a row_number against. Then remove any where the row_number > 1 (removing multiple duplicates)

WITH CTE AS
(SELECT ROW_NUMBER() OVER
(PARTITION BY computername ORDER BY ( SELECT 0)) RN
FROM temp_devicerefresh)
DELETE FROM CTE WHERE RN > 1

TrimEnd & Like in VB.NET

January 25, 2019 /

This example illustrates trimming the “!” character from the end of the string

 
    Dim str As String = "This is a string, with a comma!"
    str = str.TrimEnd(CChar("!"))
    MsgBox(str)

This example look for 4 numbers after 2 specific characters

 
    Dim phoneNum As String = "CT8964"
    If (phoneNum Like "CT####") Then
      MsgBox("Found it")
    Else
      MsgBox("Nope")
    End If

SQL replace string in a field

November 9, 2018 /

The example replaces the string height=”480″ with the string height=”320″.

UPDATE Blog
SET myPost = REPLACE(CAST(myPost AS varchar(MAX)),’height=”480″‘, ‘height=”320″‘)
FROM Blog
WHERE CHARINDEX(‘height=”480″‘,CAST(myPost as varchar(MAX)))>0
AND myKey=2469

AutoTab to Next Field

October 31, 2018 /

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

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