mysql connection strings in vb.net

imports
Option Explicit On
Imports MySql.Data.MySqlClient
Imports System.Data


connnection string
Public con As String = "Server=localhost;Database=dbstud;user id=root;password="

Check all textboxes in form if they if they are empty(vb.net)

Public Field_Check As New TextHandling

Public Class TextHandling
    Function Empty_Checks(ByRef frm As Form) As Boolean
        Dim Form_Object As Control
        iTerminate = False
        For Each Form_Object In frm.Controls
            If TypeOf Form_Object Is TextBox Or TypeOf Form_Object Is ComboBox Then
                If Form_Object.Text = "" Then
                    MsgBox(Form_Object.Name & " is empty. Please complete the field.", vbExclamation)
                    iTerminate = True
                    Form_Object.Focus()
                    Exit Function
                End If
            End If
        Next Form_Object
    End Function

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Field_Check.Empty_Checks(Me)
        If iTerminate = True Then
            iTerminate = False

            Exit Sub
        End If
    End Sub

Check all textboxes in form if they contain numbers only(vb.net)

Public Function Chk_If_Numeric(ByRef frm As Form) As Boolean
'the function check from the textboxes in a certain form'
Dim cControl As Control

Chk_If_Numeric = True  'set the variable to default value'
    For Each cControl In frm 'looping trough the form controls'
        If TypeOf cControl Is TextBox Then 'finding only textboxes'
            If Not IsNumeric(cControl.Text) Then 'check if contain only numbers'
                Chk_If_Numeric = False 'if one of them is not we return false'
            End If
        End If
    Next
End Function

Private Sub Command1_Click()
'a test button click '
    If Chk_If_Numeric(Form1) Then
        MsgBox "all numbers"
    Else
        MsgBox "not all are numeric"
    End If
End Sub

Generate Crystal Report from List View in vb.net

 Private Sub cmdReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdReport.Click
        'REPORT OBJECT
        Dim MyRpt As New CrystalReport1
        'DATASET, AND DATAROW OBJECTS NEEDED TO MAKE THE DATA SOURCE
        Dim row As DataRow = Nothing
        Dim DS As New DataSet

        'ADD A TABLE TO THE DATASET
        DS.Tables.Add("ListViewData")
        'ADD THE COLUMNS TO THE TABLE
        With DS.Tables(0).Columns
            .Add("Field1", Type.GetType("System.String"))
            .Add("Field2", Type.GetType("System.String"))
            .Add("Field3", Type.GetType("System.String"))
        End With

        'LOOP THE LISTVIEW AND ADD A ROW TO THE TABLE FOR EACH LISTVIEWITEM
        For Each LVI As ListViewItem In ListView1.Items
            row = DS.Tables(0).NewRow
            row(0) = LVI.SubItems(0).Text
            row(1) = LVI.SubItems(1).Text
            row(2) = LVI.SubItems(2).Text
            DS.Tables(0).Rows.Add(row)
        Next

        'SET THE REPORT SOURCE TO THE DATABASE
        MyRpt.SetDataSource(DS)

        'ASSIGN THE REPORT TO THE CRVIEWER CONTROL
        CRViewer.ReportSource = MyRpt

        'DISPOSE OF THE DATASET
        DS.Dispose()
        DS = Nothing
    End Sub

sample prog in vb.net using ms sql

Module1.vb codes
Option Explicit On
Imports System.Data.OleDb
Imports System.Data
Module Module1
    Public con As String = "Provider=SQLOLEDB;Data Source=315mis\wdc1;Initial Catalog=sample;User Id=sa;Password=P@ssw0rd;"
    Public cnSQL As New OleDbConnection
    Public cmSQL As New OleDbCommand
    Public strSQL As String
    Public objRead As OleDbDataReader
    Public currenttab As Integer
    Public ds As New DataSet
    Public lv As ListViewItem

   
    Public Sub connection_open()
        cnSQL = New OleDbConnection(con)
        cnSQL.Open()
    End Sub

    Public Sub connection_close()
        cnSQL.Close()
        cnSQL.Dispose()
    End Sub
    Public Sub SaveEntry(ByVal id As String, ByVal name As String, ByVal course As String)
        Try

            connection_open()
            strSQL = "insert into tbl_stud(id,name,course) values('" & id & "','" & name & "','" & course & "')"

            cmSQL = New OleDbCommand(strSQL, cnSQL)
            cmSQL.ExecuteNonQuery()
            cmSQL.Dispose()

            connection_close()

        Catch Exp As OleDbException
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")

        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try

    End Sub
    Public Sub updateEntry(ByVal id As String, ByVal name As String, ByVal course As String)
        Try

            connection_open()
            strSQL = "UPDATE tbl_stud SET name='" & name & "',course='" & course & "'where id='" & id & "'"

            cmSQL = New OleDbCommand(strSQL, cnSQL)
            cmSQL.ExecuteNonQuery()
            cmSQL.Dispose()

            connection_close()

        Catch Exp As OleDbException
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")

        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try

    End Sub
    Public Sub deleteEntry(ByVal id As String, ByVal name As String, ByVal course As String)
        Try

            connection_open()
            strSQL = "delete from tbl_stud where id='" & id & "'"

            cmSQL = New OleDbCommand(strSQL, cnSQL)
            cmSQL.ExecuteNonQuery()
            cmSQL.Dispose()

            connection_close()

        Catch Exp As OleDbException
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")

        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try

    End Sub
    Public Function Getid(ByVal tablename As String, ByVal fieldname As String, Optional ByVal cond As String = "") As Double
        Dim cmd2 As New OleDbCommand

        cnSQL = New OleDbConnection(con)
        cnSQL.Open()
        Try

            cmd2.Connection = cnSQL
            cmd2.CommandText = "select top 1 " & fieldname & " from " & tablename & IIf(cond <> "", " where " & cond, "") & " order by 1 desc"
            objRead = cmd2.ExecuteReader
            If objRead.Read = True Then
                Getid = objRead(0) + 1
            Else
                Getid = 1
            End If
            objRead.Close()
        Catch ex As Exception
            MsgBox(Err.Description)
        End Try
        cnSQL.Close()
    End Function
    Function AutoID(ByVal Tablename As String, ByVal Fieldname As String) As Int32
        cnSQL = New OleDbConnection(con)
        cnSQL.Open()
        Dim cmd1 As New OleDbCommand
        Dim dr1 As OleDbDataReader
        cmd1.Connection = cnSQL
        cmd1.CommandType = CommandType.Text
        cmd1.CommandText = "select top 1 " & Fieldname & " from " & Tablename & " Order by 1 Desc "
        dr1 = cmd1.ExecuteReader
        If dr1.Read = True Then
            AutoID = Microsoft.VisualBasic.Right(dr1(0), 4) + 1
        Else
            AutoID = 1
        End If
        dr1.Close()
        cnSQL.Close()
    End Function
End Module


Form1.vb codes


Option Explicit On
Imports System.Data.OleDb
Imports System.Data

Public Class Form1
    Dim myDA As New OleDbDataAdapter
    Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
        If btnsave.Text = "New" Then
            btnsave.Text = "Save"
            tab1()
            GroupBox1.Enabled = True
            txtname.Focus()
            Stud_autonum()
        ElseIf btnsave.Text = "Save" And txtname.Text <> "" And txtcourse.Text <> "" Then
            ' txtname.Text = txtname.Text & "-" & txtid.Text
            SaveEntry(txtid.Text, txtname.Text, txtcourse.Text)
            MsgBox("Student has been Successfully Added.", MsgBoxStyle.Information)
            btnsave.Text = "New"
            PopulateListView()
            clear()
            tab2()
            GroupBox1.Enabled = False
        ElseIf txtname.Text = "" Then
            MsgBox("Pleas Input Student Name.", MsgBoxStyle.Critical)
            txtname.Focus()
        ElseIf txtcourse.Text = "" Then
            MsgBox("Pleas Input Course.", MsgBoxStyle.Critical)
            txtcourse.Focus()
        End If

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        GroupBox1.Enabled = False
        currenttab = 0
        PopulateListView()
        ListView1.Items(0).Selected = True
        ListView1.Select()
        txtid.Enabled = False
        btnupdate.Enabled = False
        btndelete.Enabled = False
    End Sub
    Private Sub tab1()
        currenttab = TabControl1.SelectedIndex
        currenttab = currenttab + 1
        If (currenttab < TabControl1.TabCount) Then

            TabControl1.SelectedIndex = currenttab

        End If
    End Sub
    Private Sub tab2()
        currenttab = TabControl1.SelectedIndex
        currenttab = currenttab - 1
        If (currenttab > -1) Then

            TabControl1.SelectedIndex = currenttab

        End If
    End Sub
    Private Sub PopulateListView()
        ListView1.Items.Clear()
        connection_open()
        Try
            strSQL = "SELECT * FROM tbl_stud ORDER BY id Asc"
            cmSQL = New OleDbCommand(strSQL, cnSQL)
            objRead = cmSQL.ExecuteReader
            While objRead.Read
                lv = Me.ListView1.Items.Add(objRead("id") & "")
                lv.SubItems.Add(objRead("name") & "")
                lv.SubItems.Add(objRead("course") & "")
            End While
            objRead.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            '---------inserted code to count the number of columns
            ds = New DataSet
            myDA = New OleDbDataAdapter(strSQL, cnSQL)
            myDA.Fill(ds, "MyData")
            Dim kRow1 As Integer = ds.Tables("MyData").Rows.Count 'TOTAL ROW
            lblTotal.Text = "Total No. of Students : " + kRow1.ToString
            '------------------------end
            ListView1.Items.Clear()
            Dim i As Integer 'COLUMNS
            Dim iCol As Integer = ds.Tables("MyData").Columns.Count 'TOTAL COLUMN
            Dim k As Integer 'ROWS
            Dim kRow As Integer = ds.Tables("MyData").Rows.Count 'TOTAL ROW
            Dim ThisRow(4) As String '//=For each Loop of ROW

            For k = 0 To kRow - 1
                For i = 0 To iCol - 1
                    ThisRow(i) = ds.Tables("MyData").Rows(k).Item(i).ToString
                Next
                Dim lsvi As New ListViewItem(ThisRow, 2)

                '//=Start Alternate Color
                If k / 2 <> Int(k / 2) Then '//=Determine Alternate Row(odd or not)
                    lsvi.BackColor = Color.SkyBlue
                Else
                    lsvi.BackColor = Color.LightCyan


                End If
                '//=End Alternate Color
                ListView1.Items.Add(lsvi)
            Next
            connection_close()
        End Try
    End Sub
    Private Sub RefreshForm()
        ListView1.Items.Clear()
        Call searchListView()
    End Sub
    Private Sub searchListView()
        connection_open()
        Try
            strSQL = "SELECT * FROM tbl_stud where id like '" & txtsearch.Text & "%'"
            cmSQL = New OleDbCommand(strSQL, cnSQL)
            objRead = cmSQL.ExecuteReader
            While objRead.Read
                lv = Me.ListView1.Items.Add(objRead("id") & "")
                lv.SubItems.Add(objRead("name") & "")
                lv.SubItems.Add(objRead("course") & "")
            End While
            objRead.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            connection_close()
        End Try
    End Sub
    Private Sub ListView1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles ListView1.KeyPress
        If e.KeyChar = Chr(13) Then
            GroupBox1.Enabled = True
            btnupdate.Enabled = True
            btndelete.Enabled = True
            btnsave.Enabled = False
            tab1()
            With ListView1.SelectedItems(0)
                txtid.Text = .SubItems(0).Text
                txtname.Text = .SubItems(1).Text
                txtcourse.Text = .SubItems(2).Text
            End With
        End If
    End Sub
    Private Sub ListView1_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseClick
        GroupBox1.Enabled = True
        btnsave.Enabled = False
        tab1()
        ListView1_MouseDoubleClick(sender, e)
    End Sub

    Private Sub ListView1_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseDoubleClick
        GroupBox1.Enabled = True
        btnupdate.Enabled = True
        btndelete.Enabled = True
        btnsave.Enabled = False
        tab1()
        With ListView1.SelectedItems(0)
            txtid.Text = .SubItems(0).Text
            txtname.Text = .SubItems(1).Text
            txtcourse.Text = .SubItems(2).Text
        End With
    End Sub

    Private Sub txtsearch_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtsearch.KeyPress
        If e.KeyChar = Chr(13) Then
            ListView1.Items(0).Selected = True
            ListView1.Select()
        End If
    End Sub

    Private Sub txtsearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtsearch.TextChanged
        RefreshForm()
    End Sub

    Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click
        updateEntry(txtid.Text, txtname.Text, txtcourse.Text)
        MsgBox("Student has been Successfully Udated.", MsgBoxStyle.Information)
        PopulateListView()
        clear()
        tab2()
        GroupBox1.Enabled = False
        btnupdate.Enabled = False
        btndelete.Enabled = False
        btnsave.Enabled = True
    End Sub
    Private Sub clear()
        txtid.Text = ""
        txtname.Text = ""
        txtcourse.Text = ""
    End Sub

    Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
        deleteEntry(txtid.Text, txtname.Text, txtcourse.Text)
        MsgBox("Student has been Successfully Deleted.", MsgBoxStyle.Information)
        PopulateListView()
        clear()
        tab2()
        GroupBox1.Enabled = False
        btnupdate.Enabled = False
        btndelete.Enabled = False
        btnsave.Enabled = True
    End Sub
    Private Sub Stud_autonum()
        Dim year As String
        Dim studid As String
        year = Now.ToString("yyyy")
        studid = "" & Format(AutoID("tbl_stud", "id"), "0000")
        txtid.Text = year & studid
    End Sub

    Private Sub txtname_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtname.KeyPress
        If e.KeyChar = Chr(13) Then
            txtcourse.Focus()
        End If
    End Sub

    Private Sub btncancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncancel.Click
        clear()
        tab2()
        GroupBox1.Enabled = False
        btnsave.Enabled = True
        btnupdate.Enabled = False
        btndelete.Enabled = False
        btnsave.Text = "New"
        txtsearch.Text = ""
    End Sub
   
End Class

Save,Update and Delete records using functions

Declare to a class 

 

Public Sub SaveEntry(ByVal id As String, ByVal name As String, ByVal course As String)
        Try

            connection_open()
            strSQL = "insert into tbl_stud(id,name,course) values('" & id & "','" & name & "','" & course & "')"

            cmSQL = New OleDbCommand(strSQL, cnSQL)
            cmSQL.ExecuteNonQuery()
            cmSQL.Dispose()

            connection_close()

        Catch Exp As OleDbException
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")

        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try

    End Sub

    Public Sub updateEntry(ByVal id As String, ByVal name As String, ByVal course As String)
        Try

            connection_open()
            strSQL = "UPDATE tbl_stud SET name='" & name & "',course='" & course & "'where id='" & id & "'"

            cmSQL = New OleDbCommand(strSQL, cnSQL)
            cmSQL.ExecuteNonQuery()
            cmSQL.Dispose()

            connection_close()

        Catch Exp As OleDbException
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")

        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try

    End Sub

    Public Sub deleteEntry(ByVal id As String, ByVal name As String, ByVal course As String)
        Try

            connection_open()
            strSQL = "delete from tbl_stud where id='" & id & "'"

            cmSQL = New OleDbCommand(strSQL, cnSQL)
            cmSQL.ExecuteNonQuery()
            cmSQL.Dispose()

            connection_close()

        Catch Exp As OleDbException
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")

        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try

    End Sub

Declare to the form

 Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
        deleteEntry(txtid.Text, txtname.Text, txtcourse.Text)
        MsgBox("Student has been Successfully Deleted.", MsgBoxStyle.Information)
      End Sub

 Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click
        updateEntry(txtid.Text, txtname.Text, txtcourse.Text)
        MsgBox("Student has been Successfully Udated.", MsgBoxStyle.Information)
      
    End Sub

Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
   
            SaveEntry(txtid.Text, txtname.Text, txtcourse.Text)
            MsgBox("Student has been Successfully Added.", MsgBoxStyle.Information)
    

    End Sub

public declaration of connetion open and close to the database vb.net

Public con As String = "Provider=SQLOLEDB;Data Source=315mis\wdc1;Initial Catalog=sample;User Id=sa;Password=P@ssw0rd;"
    Public cnSQL As New OleDbConnection
    Public cmSQL As New OleDbCommand
    Public strSQL As String
    Public objRead As OleDbDataReader
    Public currenttab As Integer
    Public ds As New DataSet
    Public lv As ListViewItem

   
    Public Sub connection_open()
        cnSQL = New OleDbConnection(con)
        cnSQL.Open()
    End Sub

    Public Sub connection_close()
        cnSQL.Close()
        cnSQL.Dispose()
    End Sub