0 HOW TO USE DATABASES CONNECTIVITY IN VB.NET

HOW TO USE DATABASES CONNECTIVITY IN VB.NET

Public Class Form1
    Dim con As New OleDb.OleDbConnection
    Dim pos As Integer
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String
 

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        pos = 0
        TextBox1.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(0)
        TextBox2.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(1)
        TextBox3.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(2)
        TextBox4.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(3)


    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        pos = ds.Tables("sagarbook").Rows.Count - 1

        TextBox1.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(0)
        TextBox2.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(1)
        TextBox3.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(2)
        TextBox4.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(3)

    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        If pos = 0 Then
            MsgBox("you are at first record")
        Else
            pos = pos - 1
            TextBox1.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(0)
            TextBox2.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(1)
            TextBox3.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(2)
            TextBox4.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(3)

        End If
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        con.ConnectionString = "PROVIDER=Microsoft.jet.OLEDB.4.0;Data Source = C:\addressbook.mdb"
        con.Open()
        sql = "select * FROM tblcontacts"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "SAGARBOOK")
        MsgBox("DATABASE IS NOW OPEN")
        pos = 0
        TextBox1.Text = ds.Tables("SAGARBOOK").Rows(0).Item(0)
        TextBox2.Text = ds.Tables("SAGARBOOK").Rows(0).Item(1)
        TextBox3.Text = ds.Tables("SAGARBOOK").Rows(0).Item(2)
        TextBox4.Text = ds.Tables("SAGARBOOK").Rows(0).Item(3)
        con.Close()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        If pos = ds.Tables("SAGARBOOK").Rows.Count - 1 Then
            MsgBox("you are at last record")
        Else
            pos = pos + 1
            TextBox1.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(0)
            TextBox2.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(1)
            TextBox3.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(2)
            TextBox4.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(3)

        End If
    End Sub

    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
        If pos >= 0 And pos <= ds.Tables("sagarbook").Rows.Count Then
            If MessageBox.Show("do you really want to delete this record?", "delete", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
                MsgBox("operation cancelled")
                Exit Sub
            Else
                Dim cb As New OleDb.OleDbCommandBuilder(da)
                ds.Tables("sagarbook").Rows(pos).Delete()
                pos = 0
                TextBox1.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(0)
                TextBox2.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(1)
                TextBox3.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(2)
                TextBox4.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(3)
                da.Update(ds, "sagarbook")
            End If
        End If




    End Sub

    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        ds.Tables("SAGARBOOK").Rows(pos).Item(0) = TextBox1.Text
        ds.Tables("SAGARBOOK").Rows(pos).Item(1) = TextBox2.Text
        ds.Tables("SAGARBOOK").Rows(pos).Item(2) = TextBox3.Text
        ds.Tables("SAGARBOOK").Rows(pos).Item(3) = TextBox4.Text
        con.Open()
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        da.Update(ds, "SAGARBOOK")
        MsgBox("data successfully updated")
        con.close()
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        If pos >= 0 Then
            Dim cb As New OleDb.OleDbCommandBuilder(da)
            Dim dsrow As DataRow
            dsrow = ds.Tables("SAGARBOOK").NewRow()
            dsrow.Item(0) = TextBox1.Text
            dsrow.Item(1) = TextBox2.Text
            dsrow.Item(2) = TextBox3.Text
            dsrow.Item(3) = TextBox4.Text
            ds.Tables("SAGARBOOK").Rows.Add(dsrow)
            da.Update(ds, "SAGARBOOK")
            MsgBox("new record added to the database")
            Button5.Enabled = True
            Button6.Enabled = False
            Button7.Enabled = True
            Button8.Enabled = True
        End If
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Button5.Enabled = False
        Button6.Enabled = True
        Button7.Enabled = False
        Button8.Enabled = False
        TextBox1.Clear()
        TextBox2.Clear()
        TextBox3.Clear()
        TextBox4.Clear()

    End Sub

    Private Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button9.Click
        Button5.Enabled = True
        Button6.Enabled = False
        Button7.Enabled = True
        Button8.Enabled = True
        pos = 0
        TextBox1.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(0)
        TextBox2.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(1)
        TextBox3.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(2)
        TextBox4.Text = ds.Tables("SAGARBOOK").Rows(pos).Item(3)

    End Sub
End Class