Tuesday, November 18, 2008

sql2000 delete rows having duplicate (identical) records

Suppose, we have identical records like :-

name age
***********
RAM 30
RAM 30

we have to keep one entry each for ram:30  and ram:40 and delete duplicate entries:-

set rowcount 1 
(this will remove top 1 row)
select * from temp where name = 'ram'
delete temp where name = 'ram'
set rowcount 0

Friday, November 7, 2008

String manipulations

        Dim strSample As String = "Left Center Right"

        'left
        strSample = strSample.Substring(0, 4)

        'center
        strSample = strSample.Substring(5, 6)

        'right
        strSample = strSample.Substring(12)


        'split
        Dim SplitString As String = "Cat, Dog, Hen, Zebra"
        Dim SplitArr() As String
        Dim i As Integer

        SplitArr = SplitString.Split(","c)

        For i = 0 To SplitArr.Length - 1
            console.writeline(SplitArr(i))
        Next

        'Join
        Dim JoinArr(2) As String
        Dim JoinString As String
        JoinArr(0) = "Give"
        JoinArr(1) = "Take"
        JoinArr(2) = "Apply"
        JoinString = String.Join(",", JoinArr)
        Console.WriteLine(JoinString)

        'insert
        Dim strTemp As String = "This is My Stng"
        strSample = strTemp.Insert(13, "ri")
        Console.WriteLine(strSample)    'OUTPUT: "This is My String".

        'Remove
        Dim RemoveString As String = "This is my Sam@o@o@ple"
        'strSample = "This is my Sample"
        strSample = RemoveString.Remove(14, 5)
        Console.WriteLine(strSample)

        'replace
        Dim ReplaceString = "This is My String"
        ' strSample = "This is Another String"
        strSample = ReplaceString.Replace("My", "Another")
        Console.WriteLine(strSample)

        'Trim Spaces
        Dim SpaceString As String = "        This string will have the spaces removed        "
        Console.WriteLine(SpaceString)
        Dim TrimmedString As String
        TrimmedString = Trim(SpaceString)
        Console.WriteLine(TrimmedString)

        'Trim Other
        Dim HashString As String = "#####Testing!######"
        TrimmedString = HashString.Trim("#")
        Console.WriteLine(TrimmedString)

        'Left Trim ( TrimStart)
        Dim SpaceString1 As String = "        This string will have the leading spaces removed        "
        Console.WriteLine(SpaceString)
        TrimmedString = SpaceString1.TrimStart(" ")
        Console.WriteLine(TrimmedString)

        'Right Trim ( TrimEnd)
        Dim SpaceString2 As String = "        This string will have the trailing spaces removed        "
        Console.WriteLine(SpaceString)
        TrimmedString = SpaceString.TrimEnd(" ")
        Console.WriteLine(TrimmedString)

        'Pad Left
        Dim MainString As String = "This will be padded on the left"
        Dim PaddedString As String

        PaddedString = MainString.PadLeft(36, "@") '36 = new length of string with 5 new chars
        Console.WriteLine(PaddedString)

        'Pad Right
        Dim MainString1 As String = "This will be padded on the right"
        PaddedString = MainString1.PadRight(36, "@") '36 = new length of string with 5 new chars
        Console.WriteLine(PaddedString)

        'UPPER CASE
        Dim MixedString As String = "UpPeR oR LoWeR cAsE"
        Dim UPPERString As String
        ' UPPERString = "UPPER OR LOWER CASE"
        UPPERString = MixedString.ToUpper
        Console.WriteLine(UPPERString)

        'lower case
        Dim MixedString1 As String = "UpPeR oR LoWeR cAsE"
        Dim lowerString As String
        'lowerString = "upper or lower case"
        lowerString = MixedString1.ToLower
        Console.WriteLine(lowerString)

        'TitleCase
        Dim TitleString As String = "UPPER lower Title"
        TitleString = TitleString.ToLower()  ' mainly because of UPPER not changing to title
        TitleString = System.Globalization.CultureInfo.InstalledUICulture.TextInfo.ToTitleCase(TitleString)
        Console.WriteLine(TitleString)

        'Format
        Dim UnFormattedDateTime As Date = #1/27/2001 5:04:23 PM#
        Dim strFormattedString As String
        ' Returns current system time in the system-defined long time format.
        strFormattedString = Format(Now(), "Long Time")
        ' Returns current system date in the system-defined long date format.
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(Now(), "Long Date")
        Console.WriteLine(strFormattedString)
        ' Also returns current system date in the system-defined long date 
        ' format, using the single letter code for the format.
        strFormattedString = Format(Now(), "D")
        Console.WriteLine(strFormattedString)
        ' Returns the value of UnFormattedDateTime in user-defined date/time formats.
        strFormattedString = Format(UnFormattedDateTime, "h:m:s")   ' Returns "5:4:23".
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(UnFormattedDateTime, "hh:mm:ss tt")   ' Returns "05:04:23 PM".
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(UnFormattedDateTime, "dddd, MMM d yyyy")   ' Returns "Saturday,
        ' Jan 27 2001".
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(UnFormattedDateTime, "HH:mm:ss")   ' Returns "17:04:23"
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(23)   ' Returns "23".
        Console.WriteLine(strFormattedString)
        ' User-defined numeric formats.
        strFormattedString = Format(5459.4, "##,##0.00")   ' Returns "5,459.40".
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(334.9, "###0.00")   ' Returns "334.90".
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(5, "0.00%")   ' Returns "500.00%".
        Console.WriteLine(strFormattedString)

        'Concat
        Dim ConcatString As String
        Dim aString As String = "A"
        Dim bString As String = "B"
        Dim cString As String = "C"
        Dim dString As String = "D"
        ' ConcatString = "ABCD"
        ConcatString = String.Concat(aString, bString, cString, dString)
        Console.WriteLine(ConcatString)

        'IndexOf
        Dim MainString2 As String = "ABCDE"
        Dim Result As Integer
        Result = MainString2.IndexOf("D")  ' result = 3
        Console.WriteLine("D is Character number : " & Result.ToString)

        'Chars
        Dim MainString3 As String = "ABCDE"
        Dim ResultChar As Char
        ResultChar = MainString3.Chars(3) ' resultChar = "D"
        Console.WriteLine("Character 3 is : " & ResultChar.ToString)

        'LastIndexOf
        Dim MainString4 = "A\B\C\D\E\F\G\H\I\J"
        Result = MainString.LastIndexOf("\")
        Console.WriteLine("Last occurence of ' \ ' is at Position : " & Result.ToString())

        'Compare
        Dim FirstString As String = "Alphabetical"
        Dim SecondString As String = "Order"
        Dim ThirdString As String = "Order"
        Dim FourthString As String = "Alphabetical"
        Dim Result1 As Integer
        Dim Result2 As Integer
        Result1 = String.Compare(FirstString, SecondString)
        Console.WriteLine("Result = " & Result1.ToString & Environment.NewLine & "First String Is Smaller Than Second")
        Result2 = String.Compare(ThirdString, FourthString)
        Console.WriteLine("Result = " & Result2.ToString & Environment.NewLine & "Third String Is Greater Than Fourth")

        'Length
        Dim MainString5 As String = "String To Test The Length"
        Dim Result3 As Integer = MainString.Length
        Console.WriteLine(Result.ToString & " Characters")

        'Copy
        Dim EmptyString As String
        Dim CopyString As String = "This Will Be Copied Into An Empty String"
        EmptyString = String.Copy(CopyString)
        Console.WriteLine(EmptyString)

String manipulations

        Dim strSample As String = "Left Center Right"

        'left
        strSample = strSample.Substring(0, 4)

        'center
        strSample = strSample.Substring(5, 6)

        'right
        strSample = strSample.Substring(12)


        'split
        Dim SplitString As String = "Cat, Dog, Hen, Zebra"
        Dim SplitArr() As String
        Dim i As Integer

        SplitArr = SplitString.Split(","c)

        For i = 0 To SplitArr.Length - 1
            console.writeline(SplitArr(i))
        Next

        'Join
        Dim JoinArr(2) As String
        Dim JoinString As String
        JoinArr(0) = "Give"
        JoinArr(1) = "Take"
        JoinArr(2) = "Apply"
        JoinString = String.Join(",", JoinArr)
        Console.WriteLine(JoinString)

        'insert
        Dim strTemp As String = "This is My Stng"
        strSample = strTemp.Insert(13, "ri")
        Console.WriteLine(strSample)    'OUTPUT: "This is My String".

        'Remove
        Dim RemoveString As String = "This is my Sam@o@o@ple"
        'strSample = "This is my Sample"
        strSample = RemoveString.Remove(14, 5)
        Console.WriteLine(strSample)

        'replace
        Dim ReplaceString = "This is My String"
        ' strSample = "This is Another String"
        strSample = ReplaceString.Replace("My", "Another")
        Console.WriteLine(strSample)

        'Trim Spaces
        Dim SpaceString As String = "        This string will have the spaces removed        "
        Console.WriteLine(SpaceString)
        Dim TrimmedString As String
        TrimmedString = Trim(SpaceString)
        Console.WriteLine(TrimmedString)

        'Trim Other
        Dim HashString As String = "#####Testing!######"
        TrimmedString = HashString.Trim("#")
        Console.WriteLine(TrimmedString)

        'Left Trim ( TrimStart)
        Dim SpaceString1 As String = "        This string will have the leading spaces removed        "
        Console.WriteLine(SpaceString)
        TrimmedString = SpaceString1.TrimStart(" ")
        Console.WriteLine(TrimmedString)

        'Right Trim ( TrimEnd)
        Dim SpaceString2 As String = "        This string will have the trailing spaces removed        "
        Console.WriteLine(SpaceString)
        TrimmedString = SpaceString.TrimEnd(" ")
        Console.WriteLine(TrimmedString)

        'Pad Left
        Dim MainString As String = "This will be padded on the left"
        Dim PaddedString As String

        PaddedString = MainString.PadLeft(36, "@") '36 = new length of string with 5 new chars
        Console.WriteLine(PaddedString)

        'Pad Right
        Dim MainString1 As String = "This will be padded on the right"
        PaddedString = MainString1.PadRight(36, "@") '36 = new length of string with 5 new chars
        Console.WriteLine(PaddedString)

        'UPPER CASE
        Dim MixedString As String = "UpPeR oR LoWeR cAsE"
        Dim UPPERString As String
        ' UPPERString = "UPPER OR LOWER CASE"
        UPPERString = MixedString.ToUpper
        Console.WriteLine(UPPERString)

        'lower case
        Dim MixedString1 As String = "UpPeR oR LoWeR cAsE"
        Dim lowerString As String
        'lowerString = "upper or lower case"
        lowerString = MixedString1.ToLower
        Console.WriteLine(lowerString)

        'TitleCase
        Dim TitleString As String = "UPPER lower Title"
        TitleString = TitleString.ToLower()  ' mainly because of UPPER not changing to title
        TitleString = System.Globalization.CultureInfo.InstalledUICulture.TextInfo.ToTitleCase(TitleString)
        Console.WriteLine(TitleString)

        'Format
        Dim UnFormattedDateTime As Date = #1/27/2001 5:04:23 PM#
        Dim strFormattedString As String
        ' Returns current system time in the system-defined long time format.
        strFormattedString = Format(Now(), "Long Time")
        ' Returns current system date in the system-defined long date format.
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(Now(), "Long Date")
        Console.WriteLine(strFormattedString)
        ' Also returns current system date in the system-defined long date 
        ' format, using the single letter code for the format.
        strFormattedString = Format(Now(), "D")
        Console.WriteLine(strFormattedString)
        ' Returns the value of UnFormattedDateTime in user-defined date/time formats.
        strFormattedString = Format(UnFormattedDateTime, "h:m:s")   ' Returns "5:4:23".
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(UnFormattedDateTime, "hh:mm:ss tt")   ' Returns "05:04:23 PM".
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(UnFormattedDateTime, "dddd, MMM d yyyy")   ' Returns "Saturday,
        ' Jan 27 2001".
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(UnFormattedDateTime, "HH:mm:ss")   ' Returns "17:04:23"
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(23)   ' Returns "23".
        Console.WriteLine(strFormattedString)
        ' User-defined numeric formats.
        strFormattedString = Format(5459.4, "##,##0.00")   ' Returns "5,459.40".
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(334.9, "###0.00")   ' Returns "334.90".
        Console.WriteLine(strFormattedString)
        strFormattedString = Format(5, "0.00%")   ' Returns "500.00%".
        Console.WriteLine(strFormattedString)

        'Concat
        Dim ConcatString As String
        Dim aString As String = "A"
        Dim bString As String = "B"
        Dim cString As String = "C"
        Dim dString As String = "D"
        ' ConcatString = "ABCD"
        ConcatString = String.Concat(aString, bString, cString, dString)
        Console.WriteLine(ConcatString)

        'IndexOf
        Dim MainString2 As String = "ABCDE"
        Dim Result As Integer
        Result = MainString2.IndexOf("D")  ' result = 3
        Console.WriteLine("D is Character number : " & Result.ToString)

        'Chars
        Dim MainString3 As String = "ABCDE"
        Dim ResultChar As Char
        ResultChar = MainString3.Chars(3) ' resultChar = "D"
        Console.WriteLine("Character 3 is : " & ResultChar.ToString)

        'LastIndexOf
        Dim MainString4 = "A\B\C\D\E\F\G\H\I\J"
        Result = MainString.LastIndexOf("\")
        Console.WriteLine("Last occurence of ' \ ' is at Position : " & Result.ToString())

        'Compare
        Dim FirstString As String = "Alphabetical"
        Dim SecondString As String = "Order"
        Dim ThirdString As String = "Order"
        Dim FourthString As String = "Alphabetical"
        Dim Result1 As Integer
        Dim Result2 As Integer
        Result1 = String.Compare(FirstString, SecondString)
        Console.WriteLine("Result = " & Result1.ToString & Environment.NewLine & "First String Is Smaller Than Second")
        Result2 = String.Compare(ThirdString, FourthString)
        Console.WriteLine("Result = " & Result2.ToString & Environment.NewLine & "Third String Is Greater Than Fourth")

        'Length
        Dim MainString5 As String = "String To Test The Length"
        Dim Result3 As Integer = MainString.Length
        Console.WriteLine(Result.ToString & " Characters")

        'Copy
        Dim EmptyString As String
        Dim CopyString As String = "This Will Be Copied Into An Empty String"
        EmptyString = String.Copy(CopyString)
        Console.WriteLine(EmptyString)

Monday, November 3, 2008

Handling Keystrokes in DataGridView

Private Sub dgv_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles dgv.EditingControlShowing
 
If Me.dgv.CurrentCell.ColumnIndex = 0 And Not e.Control Is Nothing Then
    Dim tb As TextBox = CType(e.Control, TextBox)
    AddHandler tb.KeyDown, AddressOf TextBox_KeyDown
    AddHandler tb.KeyPress, AddressOf TextBox_KeyPress
End If

 
End Sub
 

Private Sub TextBox_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs)
 If  e.KeyCode = Keys.Space Then
     flag = True
 End If

End Sub
 
Private Sub TextBox_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
 e.Handled = flag
 flag = False

End Sub

Export SQL table to excel

Dim excel As New Excel.Application
        Dim wBook As Excel.Workbook
        Dim wSheet As Excel.Worksheet

        wBook = excel.Workbooks.Add()
        wSheet = wBook.ActiveSheet()

        Dim dt As DataGridView = TblJobMethodsDataGridView.DataSource.Tables(0)
        Dim dc As System.Data.DataColumn
        Dim dr As System.Data.DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0

        For Each dc In dt.Columns
            colIndex = colIndex + 1
            excel.Cells(1, colIndex) = dc.ColumnName
        Next

        For Each dr In dt.Rows
            rowIndex = rowIndex + 1
            colIndex = 0
            For Each dc In dt.Columns
                colIndex = colIndex + 1
                excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)

            Next
        Next

        wSheet.Columns.AutoFit()
        Dim strFileName As String = "D:\ss.xls"
        Dim blnFileOpen As Boolean = False
        Try
            Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
            fileTemp.Close()
        Catch ex As Exception
            blnFileOpen = False
        End Try

        If System.IO.File.Exists(strFileName) Then
            System.IO.File.Delete(strFileName)
        End If

        wBook.SaveAs(strFileName)
        excel.Workbooks.Open(strFileName)
        excel.Visible = True