Database Class in vb.net (db class) sql connecton class in vb.net with Mysql and sql database

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports System.Configuration
Imports MySql.Data.MySqlClient
Public Class db

    Public EmailExtraction As String = ConfigurationSettings.AppSettings("Eamilconnection")
    Public localconnection As String = ConfigurationSettings.AppSettings("connection")
    Public localconnection1 As String = ConfigurationSettings.AppSettings("connection1")
    Public amanconnection As String = ConfigurationSettings.AppSettings("amanconnection")
    Public anticonnection As String = ConfigurationSettings.AppSettings("anticonnection")
    Public Function execute(ByVal myqry As String, ByVal executeconnection As String) As Boolean  'insert into database
        On Error GoTo err
        If executeconnection.Contains("database = ugc") = True Then
            Dim appcon As SqlConnection = New SqlConnection(executeconnection)
            Dim qrycmd As SqlCommand = New SqlCommand
            appcon.Open()
            qrycmd.Connection = appcon
            qrycmd.CommandText = myqry
            qrycmd.CommandTimeout = 0
            qrycmd.ExecuteNonQuery()
            appcon.Close()
            Return True
        Else
            Dim MySQLConnection As MySqlConnection = New MySqlConnection(executeconnection)
            Dim MySqlCommand As MySqlCommand = New MySqlCommand
            MySQLConnection.Open()
            MySqlCommand.Connection = MySQLConnection
            MySqlCommand.CommandText = myqry
            MySqlCommand.CommandTimeout = 0
            MySqlCommand.ExecuteNonQuery()
            MySQLConnection.Close()
            Return True
        End If
err:
        Resume
    End Function
    Public Function getdata(ByVal myqry As String, ByVal executeconnection As String) As DataTable

        On Error GoTo err
        If executeconnection.Contains("database = ugc") = True Then

            Dim dt As DataTable = New DataTable
            Dim dataset As DataSet = New DataSet
            Dim appcon As SqlConnection = New SqlConnection(executeconnection)
            appcon.Open()
            Dim myadapter As SqlDataAdapter = New SqlDataAdapter
            myadapter.SelectCommand = New SqlCommand(myqry, appcon)
            myadapter.SelectCommand.CommandTimeout = 0
            myadapter.Fill(dataset)
            dt = dataset.Tables(0)
            appcon.Close()
            Return dt
            Exit Function

        Else

            Dim dtdatabase As DataTable = New DataTable()
            Dim dataset As DataSet = New DataSet()
            Dim MySQLConnection As MySqlConnection = New MySqlConnection(executeconnection)
            MySQLConnection.Open()
            Dim MyAdp As MySqlDataAdapter = New MySqlDataAdapter()
            MyAdp.SelectCommand = New MySqlCommand(myqry, MySQLConnection)
            MyAdp.SelectCommand.CommandTimeout = 0
            MyAdp.Fill(dataset)
            dtdatabase = dataset.Tables(0)
            MySQLConnection.Close()
            Return dtdatabase
        End If

err:
        Resume
    End Function
    Public Function executeodbc(ByVal myqry As String, ByVal executeconnection As String) As Boolean  'insert into database
        Dim appcon As OdbcConnection = New OdbcConnection(executeconnection)
        Dim qrycmd As OdbcCommand = New OdbcCommand
        appcon.Open()
        qrycmd.Connection = appcon
        qrycmd.CommandText = myqry
        qrycmd.CommandTimeout = 0
        qrycmd.ExecuteNonQuery()
        appcon.Close()
        Return True
    End Function
    Public Function getdataodbc(ByVal myqry As String, ByVal executeconnection As String) As DataTable
        Dim dt As DataTable = New DataTable
        Dim dataset As DataSet = New DataSet
        Dim appcon As OdbcConnection = New OdbcConnection(executeconnection)
        appcon.Open()
        Dim myadapter As OdbcDataAdapter = New OdbcDataAdapter
        myadapter.SelectCommand = New OdbcCommand(myqry, appcon)
        myadapter.SelectCommand.CommandTimeout = 0
        myadapter.Fill(dataset)
        dt = dataset.Tables(0)
        appcon.Close()
        Return dt
    End Function
End Class

To Remove Duplicate from mysql Table Query

DELETE e1 FROM EMPLOYEE e1, EMPLOYEE e2 WHERE e1.name = e2.name AND e1.id > e2.id;

Missing build configuration dropdown in Visual Studio 2008

Build configuration dropdown

9

My project is using Microsoft Visual Studio 2008 for development. For some strange reason the build configuration dropdown does not show up by default in the IDE.

I found the following procedure online and very helpful. It describes how to get this useful dropdown visible:

1. In Visual Studio 2008, click “Tools” -> “Options”
2. Under “Projects and Solutions” -> “General”, check “Show advanced build options”, and click “OK”
3. Right click anywhere in the blank space of the toolbar and click “Customize…”
4. In “Commands” tab, select “Build” in “Categories”.
5. Scroll the right listbox to the bottom and drag “Solution Configurations” to the toolbar

Opening A URL in vb.net

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        System.Diagnostics.Process.Start("http://nishant2u.blogspot.in/")
    End Sub

Email Validation function in Vb.net

  Public Function ValidateEmail(ByVal strEmail As String) As Boolean
        Dim strtmp As String, n As Long, sExt As String
        ValidateEmail = True

        If strEmail = "" Then
            ValidateEmail = False
            Exit Function
        ElseIf strEmail.ToLower.Contains("error") Then
            ValidateEmail = False
            Exit Function
        ElseIf strEmail.ToLower.Contains("eror") Then
            ValidateEmail = False
            Exit Function
        ElseIf strEmail.ToLower.Contains("websit") Then
            ValidateEmail = False
            Exit Function
        ElseIf strEmail.ToLower.Contains("webside") Then
            ValidateEmail = False
            Exit Function
        ElseIf strEmail.ToLower.Contains("webste") Then
            ValidateEmail = False
            Exit Function
        ElseIf strEmail.ToLower.Contains("webite") Then
            ValidateEmail = False
            Exit Function


        ElseIf strEmail.ToLower.Contains("wqebsite") Then
            ValidateEmail = False
            Exit Function


        ElseIf strEmail.ToLower.Contains("ebsite") Then
            ValidateEmail = False
            Exit Function

        ElseIf strEmail.ToLower.Contains("wesid") Then
            ValidateEmail = False
            Exit Function

        ElseIf strEmail.ToLower.Contains("wewbsite") Then
            ValidateEmail = False
            Exit Function

        ElseIf strEmail.ToLower.Contains("websitw") Then
            ValidateEmail = False
            Exit Function

        ElseIf strEmail.ToLower.Contains("websive") Then
            ValidateEmail = False
            Exit Function


        ElseIf strEmail.ToLower.Contains("websitw") Then
            ValidateEmail = False
            Exit Function
        ElseIf strEmail.ToLower.Contains("webit") Then
            ValidateEmail = False
            Exit Function
        ElseIf strEmail.ToLower.Contains("websi") Then
            ValidateEmail = False
            Exit Function


        ElseIf InStr(1, strEmail, "@") = 0 Then
            ValidateEmail = False
            Exit Function
        ElseIf InStr(1, strEmail, "@") = 1 Then
            ValidateEmail = False
            Exit Function
        ElseIf InStr(1, strEmail, "@") = Len(strEmail) Then
            ValidateEmail = False
            Exit Function
        ElseIf InStr(InStr(1, strEmail, "@") + 1, strEmail, "@") > 0 Then
            ValidateEmail = False
            Exit Function
        ElseIf InStr(1, strEmail, "@.") > 0 Then
            ValidateEmail = False
            Exit Function
        ElseIf Len(strEmail) < 6 Then
            ValidateEmail = False
            Exit Function
        End If
        sExt = ""
        If InStr(InStr(1, strEmail, "@"), strEmail, ".") <> 0 Then
            sExt = Right(strEmail, Len(strEmail) - InStr(InStr(1, strEmail, "@"), strEmail, "."))
        End If
        If sExt = "" Then
            ValidateEmail = False
            Exit Function
        End If

        strtmp = strEmail
        While InStr(1, strtmp, "@") <> 0
            n = 1
            strtmp = Right(strtmp, Len(strtmp) - InStr(1, strtmp, "@"))
        End While
        If n > 1 Then
            ValidateEmail = False
        End If
    End Function

String Sound Matching in Vb.net

Public Class SoundexComparison

        Public Shared Function GetSoundexCode(ByVal word As String) As String
            word = word.ToUpper()

            ' Keep the first character of the word.
            Dim SoundexCode As String = word.Substring(0, 1)

            Dim i As Integer
            For i = 1 To word.Length - 1

                ' Transform a single character.
                Dim Character As String = Transform(word.Substring(i, 1))

                ' Decide whether to append this character code,
                ' depending on the previous sound.
                Select Case word.Substring(i - 1, 1)
                    Case "H", "W"
                        ' Ignore
                    Case "A", "E", "I", "O", "U"
                        ' Characters separated by a vowel represent distinct
                        ' sounds, and should be encoded.
                        SoundexCode &= Character
                    Case Else
                        If SoundexCode.Length = 1 Then
                            ' We only have the first character, which is never
                            ' encoded. However, we need to check whether it is
                            ' the same phonetically as the next character.
                            If Transform(word.Substring(0, 1)) <> Character Then
                                SoundexCode &= Character
                            End If
                        Else
                            ' Only add if it does not represent a duplicated
                            ' sound.
                            If Transform(word.Substring(i - 1, 1)) <> _
                              Character Then
                                SoundexCode &= Character
                            End If
                        End If
                End Select

            Next

            ' A SoundEx code must be exactly 4 characters long.
            ' Pad it with zeroes in case the code is too short.
            SoundexCode = SoundexCode.PadRight(4, "0"c)

            ' Truncate the code if it is too long.
            Return SoundexCode.Substring(0, 4)
        End Function

        Public Shared Function Transform(ByVal character As String) As String
            ' Map the character to a SoundEx code.
            Select Case character
                Case "B", "F", "P", "V"
                    Return "1"
                Case "C", "G", "J", "K", "Q", "S", "X", "Z"
                    Return "2"
                Case "D", "T"
                    Return "3"
                Case "L"
                    Return "4"
                Case "M", "N"
                    Return "5"
                Case "R"
                    Return "6"
                Case Else
                    ' All other characters are ignored.
                    Return String.Empty
            End Select
        End Function
   
    End Class

Set Filter on datagridview in vb.net

 

 Dim filterlst As New List(Of String)
    Dim filterval As New List(Of String)
    Private Sub gridCollegedetails_CellContentDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles gridCollegedetails.CellContentDoubleClick
        If e.RowIndex < 0 Then Exit Sub
        If e.ColumnIndex < 0 Then Exit Sub
        Me.Cursor = Cursors.WaitCursor
        gridCollegedetails.EnableHeadersVisualStyles = False
        If filterlst.Contains(e.ColumnIndex) = False Then
            If filterlst.Count > 0 Then gridCollegedetails.Columns(CInt(filterlst.Item(filterlst.Count - 1))).HeaderCell.Style.ForeColor = Color.Blue
            filterlst.Add(e.ColumnIndex)
            filterval.Add(Trim(UCase(gridCollegedetails.Rows(e.RowIndex).Cells(e.ColumnIndex).Value)))
            gridCollegedetails.Columns(e.ColumnIndex).HeaderCell.Style.ForeColor = Color.Green
            For i As Integer = 0 To gridCollegedetails.Rows.Count - 1
                If gridCollegedetails.Rows(i).Visible = True Then
                    If Trim(UCase(gridCollegedetails.Rows(i).Cells(e.ColumnIndex).Value)) <> Trim(UCase(gridCollegedetails.Rows(e.RowIndex).Cells(e.ColumnIndex).Value)) And i <> e.RowIndex Then
                        gridCollegedetails.Rows(i).Visible = False
                    Else
                        gridCollegedetails.Rows(i).Visible = True
                    End If
                End If
            Next
        End If
        Me.Cursor = Cursors.Default
    End Sub

    Private Sub gridCollegedetails_ColumnHeaderMouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles gridCollegedetails.ColumnHeaderMouseClick
        Try
            If gridCollegedetails.Columns(e.ColumnIndex).HeaderCell.Style.ForeColor <> Color.Green Then Exit Sub
            Me.Cursor = Cursors.WaitCursor
            If e.ColumnIndex = CInt(filterlst.Item(filterlst.Count - 1)) Then
                Dim penindex As Integer
                If filterlst.Count = 1 Then
                    penindex = 1
                    For i As Integer = 0 To gridCollegedetails.Rows.Count - 1
                        If gridCollegedetails.Rows(i).Visible = False Then
                            gridCollegedetails.Rows(i).Visible = True
                        End If
                    Next
                    filterval.Remove(filterval.Item(filterlst.Count - 1))
                    filterlst.Remove(filterlst.Item(filterlst.Count - 1))
                    gridCollegedetails.Columns(e.ColumnIndex).HeaderCell.Style.ForeColor = gridCollegedetails.ColumnHeadersDefaultCellStyle.ForeColor
                    Me.Cursor = Cursors.Default
                    Exit Sub
                Else
                    penindex = CInt(filterlst.Item(filterlst.Count - 2))
                End If

                For i As Integer = 0 To gridCollegedetails.Rows.Count - 1
                    If gridCollegedetails.Rows(i).Visible = False Then

                        If Trim(UCase(gridCollegedetails.Rows(i).Cells(penindex).Value)) = filterval.Item(filterlst.Count - 2) Then
                            gridCollegedetails.Rows(i).Visible = True
                        End If
                    End If
                Next
                filterval.Remove(filterval.Item(filterlst.Count - 1))
                filterlst.Remove(filterlst.Item(filterlst.Count - 1))
                gridCollegedetails.Columns(e.ColumnIndex).HeaderCell.Style.ForeColor = gridCollegedetails.ColumnHeadersDefaultCellStyle.ForeColor
                If filterlst.Count > 0 Then gridCollegedetails.Columns(CInt(filterlst.Item(filterlst.Count - 1))).HeaderCell.Style.ForeColor = Color.Green
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Me.Cursor = Cursors.Default
    End Sub