Find Last Character of Given String using VB.NET

======================This is the Code
Dim str As String = "abcd"
        Dim ss As String = str(str.Length - 1)
===========================================
output :d
=============if we want 'c' of given string then we use-
Dim str As String = "abcd"
        Dim ss As String = str(str.Length - 2)
==================================
output :c
======================================

Tick Rows In Datagridview Value

 Private Sub DataGridView2_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView2.CellClick
        If e.RowIndex < 0 Then Exit Sub
        If e.ColumnIndex < 0 Then Exit Sub
        If DataGridView2.Columns(e.ColumnIndex).Name = "Select" Then
            If DataGridView2.Rows(e.RowIndex).Cells("Select").Value = "P" Then
                DataGridView2.Rows(e.RowIndex).Cells("Select").Value = ""
            Else
                DataGridView2.Rows(e.RowIndex).Cells("Select").Style.Font = New Font("Wingdings 2", 10, FontStyle.Bold)
                DataGridView2.Rows(e.RowIndex).Cells("Select").Value = "P"
            End If
        End If
    End Sub

Filter Value By KeyWord Matching

  Private Sub filterbykeyword()
        If cmbstate.Text = "" Then Exit Sub
        If DataGridView3.Rows.Count = 0 Then Exit Sub
        For i As Integer = 1 To DataGridView3.Rows.Count - 1
            If DataGridView3.Rows(i).Cells(1).Style.BackColor = Color.White Then
                For j As Integer = LBound(newcolegeprin) To UBound(newcolegeprin)
                    If Trim(UCase(DataGridView3.Rows(i).Cells(1).Value)) Like "*" & Trim(UCase(newcolegeprin(j))) & "*" And newcolegeprin(j) <> "" Then
                        If newexactmatchingdata(Trim(UCase(DataGridView3.Rows(i).Cells(1).Value)), (j + 1)) = True Then
                            DataGridView3.Rows(i).Cells(2).Value = "4"
                            DataGridView3.Rows(i).Cells(1).Style.BackColor = Color.Cyan
                            Exit For
                        Else
                            Exit For
                        End If
                    End If
                Next
            End If
        Next

    End Sub
=================
 Function newexactmatchingdata(ByVal wrdsting As String, ByVal arrno As Integer)
        newexactmatchingdata = False
        Dim kkkk As Integer
        Dim nn As Integer
        Dim mk As Integer
        Dim newwrd() As String = Split(Trim(wrdsting), " ")
        kkkk = UBound(newcolegeprin)
        Dim match() As Boolean
        ReDim match(Val(kkkk))
        Dim abc As Boolean
        For mk = 0 To UBound(newcolegeprin)
            If Trim(UCase(newcolegeprin(mk))) <> "" Then
                For nn = 0 To UBound(newwrd)
                    abc = False
                    If Trim(UCase(newwrd(nn))) = Trim(UCase(newcolegeprin(mk))) Then
                        abc = True
                        Exit For
                    End If
                Next
                If abc = True Then
                    match(mk) = True
                Else
                    match(mk) = False
                End If
            End If
        Next
        For mk = 0 To UBound(match)
            If match(mk) = False Then
                Exit Function
            End If
        Next
        newexactmatchingdata = True
    End Function

Filter Value By Key Matching

  Private Sub FilterExactName()
        For i = 0 To DataGridView3.Rows.Count - 1
            If Trim(UCase(DataGridView3.Rows(i).Cells(1).Value)) Like Trim(UCase(txtcollegename.Text)) Then
                DataGridView3.Rows(i).Cells("sort").Value = "5"
                DataGridView3.Rows(i).Cells(1).Style.BackColor = Color.Lime
            End If
        Next
    End Sub

Use SoundEX Function (Retrieve Sound Matching Data from Sql Server)

   Dim newcolegeprin() As String
  Private Sub cmdloaddata()
  Me.Cursor = Cursors.WaitCursor
        Dim data As DataTable = objdb.getdata("select director_name,no_of_occurances,sno,statecode from frequency where  statecode='" & cmbstate.Tag & "' order by no_of_occurances desc  ", objdb.localconnection)
        If data.Rows.Count > 0 Then

            For i As Integer = 0 To data.Rows.Count - 1
                DataGridView3.Rows.Add()
                DataGridView3.Rows(i).Cells("principal_name").Style.BackColor = Color.White
                DataGridView3.Rows(i).Cells("principal_name").Value = data.Rows(i).Item("director_name")
                DataGridView3.Rows(i).Cells("Frequncy").Value = data.Rows(i).Item("no_of_occurances")
                DataGridView3.Rows(i).Cells("sr_no").Value = data.Rows(i).Item("sno")
                DataGridView3.Rows(i).Cells("statecode").Value = data.Rows(i).Item("statecode")
            Next
        End If

        Dim dt As DataTable = objdb.getdata("select  college_name,principalname,college_code from tmphistory where state_code='" & cmbstate.Tag & "'order by college_name", objdb.localconnection)
        If dt.Rows.Count > 0 Then
            For i As Integer = 0 To dt.Rows.Count - 1
                DataGridView1.Rows.Add()
                DataGridView1.Rows(i).Cells("Principal_name").Value = dt.Rows(i).Item("principalname")
                DataGridView1.Rows(i).Cells("college_name").Value = dt.Rows(i).Item("college_name")
                DataGridView1.Rows(i).Cells("collegecode").Value = dt.Rows(i).Item("college_code")
            Next

        End If
        Dim j As Integer = 1

        txtcollegename.Text = DataGridView3.CurrentRow.Cells(1).Value
        txtcollegename.Tag = DataGridView3.CurrentRow.Cells(0).Value
        Dim chh() As String = Split(txtcollegename.Text, Space(1))
        Dim z As Integer = 0
        For i = LBound(chh) To UBound(chh)
            DataGridView2.Rows.Add()
            DataGridView2.Rows(z).Cells("Keyword").Value = Trim(chh(i))

            DataGridView2.Rows(z).Cells("Select").Style.Font = New Font("Wingdings 2", 10, FontStyle.Bold)
            DataGridView2.Rows(z).Cells("Select").Value = "P"
            z = z + 1
        Next
        DataGridView2.Rows.Add()
        DataGridView2.Rows(z).Cells("Keyword").Value = txtcollegename.Text
        Dim ubndarry As Integer
        ReDim newcolegeprin(0)
        newcolegeprin(0) = ""
        ubndarry = 0
        For i = 0 To DataGridView2.Rows.Count - 1
            If DataGridView2.Rows(i).Cells("Select").Value = "P" And DataGridView2.Rows(i).Cells("select").Value <> "" Then
                ubndarry = Val(ubndarry) + 1
            End If
        Next
        ReDim newcolegeprin(Val(ubndarry) - 1)

        j = 0

        For i = 0 To DataGridView2.Rows.Count - 1
            If DataGridView2.Rows(i).Cells("Select").Value = "P" And DataGridView2.Rows(i).Cells("keyword").Value <> "" Then

                newcolegeprin(j) = Trim(UCase(DataGridView2.Rows(i).Cells("keyword").Value))
                j = j + 1
            End If
        Next
        If j <> 0 Then
            FilterExactName()
            filterbykeyword()
            sound_fun()
            DataGridView3.Sort(DataGridView3.Columns(2), System.ComponentModel.ListSortDirection.Descending)

        End If
        DataGridView3.ClearSelection()
        Me.Cursor = Cursors.Default
End sub
===========================
  Private Sub sound_fun()
        Dim subkey As String = ""
        For i = LBound(newcolegeprin) To UBound(newcolegeprin)
            If UBound(newcolegeprin) And Len(newcolegeprin(i)) > 1 Then
                subkey = subkey & "" & Trim(newcolegeprin(i))
            End If
        Next
        soundpatterndata(subkey)
    End Sub
===============================================
    Sub soundpatterndata(ByVal subkey As String)
        Dim abc As Boolean
        For i = 1 To DataGridView3.Rows.Count - 1
            If DataGridView3.Rows(i).Cells("Principal_Name").Style.BackColor = Color.White Then
                abc = False
                If getsound(Trim(UCase(DataGridView3.Rows(i).Cells(1).Value))) = getsound(Trim(UCase(subkey))) Then
                    abc = True
                End If
                If abc = True Then
                    DataGridView3.Rows(i).Cells(2).Value = "3"
                    DataGridView3.Rows(i).Cells(1).Style.BackColor = Color.Yellow
                End If
            End If
        Next
    End Sub
======================
    Private Function getsound(ByVal mystring As String) As String
        getsound = ""
        Dim dt As DataTable = objdb.getdata(" select soundex('" & Replace(mystring, "'", " ''") & "') as sound", objdb.localconnection)
        If dt.Rows.Count > 0 Then
            getsound = dt.Rows(0).Item("sound")
        End If
    End Function

Fill ComboBox using Sub Query(Two Table Based On Condition)

  Private Sub showstate()
        Dim dt As DataTable = objdb.getdata("select distinct college_new_state_code from AffidavitData where college_new_state_code is not null and college_name_removesc is not null order by college_new_state_code asc ", objdb.localconnection)
        If dt.Rows.Count > 0 Then
            For i As Integer = 0 To dt.Rows.Count - 1
                Dim dt1 As DataTable = objdb.getdata("select distinct state from state_master  where state_code='" & dt.Rows(i).Item("college_new_state_code") & "' and state is not null", objdb.localconnection)
                If IsDBNull(dt1.Rows(0).Item("state")) = False Then
                    cmbstate.Items.Add(dt1.Rows(0).Item("state"))
                Else
                    i = i - 1
                End If
            Next
        End If

Dynamically Design Data Grid View

 Public Sub disgnGrd()
        DataGridView1.Columns.Clear()
        DataGridView1.Rows.Clear()
        DataGridView1.ColumnHeadersDefaultCellStyle.ForeColor = Color.Blue
        DataGridView1.Columns.Add("state", "State")
        DataGridView1.Columns.Add("Total_collegeInaffidavit", "Total Colleges In Affidavit")
        DataGridView1.Columns.Add("Totalcollegeinmaster", "Total Colleges in Master")
        DataGridView1.Columns.Add("totalaffidavit", "Total Affidavits")
 DataGridView3.Columns("Frequncy").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        '***SET DATAGRIDVIEW COLUMNS WIDTH***
        DataGridView1.Columns("state").Width = 170
        DataGridView1.Columns("Total_collegeInaffidavit").Width = 108
        DataGridView1.Columns("Totalcollegeinmaster").Width = 108
        DataGridView1.Columns("totalaffidavit").Width = 108
    End Sub

Do not Allow Sorting in Data Grid View

Private Sub gridshot_ColumnAdded(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewColumnEventArgs) Handles gridshot.ColumnAdded
        gridshot.Columns.Item(e.Column.Index).SortMode = DataGridViewColumnSortMode.NotSortable
    End Sub

click first column content in datagridview vb.net

If gridshot.Columns(e.ColumnIndex).Name = "Select" Then

Mobile Number Validation

Private Sub TxtNo_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TxtNo.KeyPress
        If Asc(e.KeyChar) <> 8 Then
            If Asc(e.KeyChar) < 48 Or Asc(e.KeyChar) > 57 Or TxtNo.Text.Length > 9 Then
                e.Handled = True
            End If
        End If
    End Sub

Create Excel File in vb.net

 Imports System.Data.OleDb
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.IO

 Private Sub btnexcelreport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnexcelreport.Click
        Dim proc As System.Diagnostics.Process
        For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
            proc.Kill()
        Next
        Dim xlapp As Microsoft.Office.Interop.Excel.Application
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim xlworkbook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlworksheet As Microsoft.Office.Interop.Excel.Worksheet
        If DataGridView1.Rows.Count = 0 Then Exit Sub
        If DataGridView1.Rows.Count >= 1 Then
            Dim path = "d:\Affidavit Data Report(State-Wise)"
            Dim dr As New DirectoryInfo(path)
            If Not dr.Exists Then
                Directory.CreateDirectory(path)
            End If
            Dim finalpath As String = path & "\" & "Statewise Report.xls"
            xlapp = New Microsoft.Office.Interop.Excel.Application
            xlworkbook = xlapp.Workbooks.Add(misValue)
            'xlworksheet = xlworkbook.Sheets("sheet1")
            xlworkbook = xlapp.Workbooks.Add(misValue)
            xlworksheet = xlworkbook.Worksheets(1)
            xlworksheet.Cells(1, 1).Font.Size = 20
            xlworksheet.Cells(1, 1).Font.Bold = True
            '  xlworksheet.Cells(1, 1).Font.Color = Color.Red
            xlworksheet.Cells(1, 1) = "State Wize Affidavit Data ;"
            ' xlworksheet.Cells(1, 1).Font.Color = Color.Red
            xlworksheet.Cells(2, 1) = "State"
            xlworksheet.Cells(2, 2) = "Total Collges In Affidavit"
            xlworksheet.Cells(2, 3) = "Total Colleges In Master"
            xlworksheet.Cells(2, 4) = "Total Affidavits"
            xlworksheet.Rows(2).Font.Bold = True
            xlworksheet.Rows(2).Font.Size = 15
            ' xlworksheet.Rows(2).Font.Color = Color.Blue
            xlworksheet.Rows(2).WrapText = True
            xlworksheet.Columns(1).ColumnWidth = 23
            xlworksheet.Columns(2).ColumnWidth = 17
            xlworksheet.Columns(3).ColumnWidth = 17
            xlworksheet.Columns(4).ColumnWidth = 17
            Dim j As Integer = 3
            For i = 0 To DataGridView1.RowCount - 1
                xlworksheet.Cells(j, 1) = DataGridView1.Rows(i).Cells(0).Value.ToString()
                xlworksheet.Cells(j, 1).WrapText = True
                xlworksheet.Cells(j, 2) = DataGridView1.Rows(i).Cells(1).Value.ToString()
                xlworksheet.Cells(j, 3) = DataGridView1.Rows(i).Cells(2).Value.ToString()
                xlworksheet.Cells(j, 4) = DataGridView1.Rows(i).Cells(3).Value.ToString()
                j = j + 1
            Next
            xlworksheet.Rows(j - 1).Font.Bold = True
            xlworksheet.Rows(j - 1).Font.Size = 11
            ' xlworksheet.Rows(j - 1).Font.Color = Color.Blue
            xlworksheet.Rows(2).AutoFilter()
            xlworksheet.Rows(3).Activate()
            xlworksheet.Rows(2).WrapText = True
            xlworksheet.PageSetup.PrintGridlines = True
            xlapp.ActiveWindow.FreezePanes = True
            xlworksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
            Dim fileinfo As New DirectoryInfo(path)
            For Each ff In fileinfo.GetFiles()
                If ff.Exists Then
                    ff.Delete()
                End If
            Next
            xlworkbook.SaveAs(finalpath)
            Dim proc1 As System.Diagnostics.Process
            For Each proc1 In System.Diagnostics.Process.GetProcessesByName("EXCEL")
                proc1.Kill()
            Next
            Dim res As MsgBoxResult
            res = MsgBox("Process Completed.Would you like to Open File?", MsgBoxStyle.YesNo)
            If res = MsgBoxResult.Yes Then
                Process.Start(finalpath)
            End If
        End If
    End Sub

Get Day of Week in SQL 2005/2008

select  DATENAME(dw,comtype) AS OrderDay from tblcom_type where s_no=6
   --Result  Friday

Do not Allow Sorting in Data Grid View

Private Sub gridshot_ColumnAdded(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewColumnEventArgs) Handles gridshot.ColumnAdded
        gridshot.Columns.Item(e.Column.Index).SortMode = DataGridViewColumnSortMode.NotSortable
    End Sub