Thursday, January 20, 2011

OLEDB Coding for Techno Stuff Readers

Sorry Friends for being late but its beneficial to revise again...................
Specially Sandeep for YOU......: )

Create one database named emp and make one table emp which contains three coulmns like
id = datatype autonumeric
Name = TEXT
City= TEXT

First of all we have to imports the data and oledb in our coding section
Imports System.Data
Imports System.Data.OleDb

Then we have to declare connection object and dataset in global declaration area because we will need it in many private events like button's click event......

Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\emp.mdb") 

Dim ds As New DataSet 

Now place four textbox, eight buttons and one data-grid-view on design portion......

Now make one function which works for select query.........

Public Sub selectQuery()
        'We made one function for select query and will use it in every place where select query needed


        Dim adp As New OleDbDataAdapter("Select * from emp", cn)
        ds.Clear()
        adp.Fill(ds)


        DataGridView1.DataSource = ds.Tables(0)

    End Sub


First code for Inserting Data.......Double Click On Insert Button.........


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
       
        cn.Open()

        Dim cmd As New OleDbCommand("insert into emp (Name, City) values ('" &TextBox1.Text &"', '"& TextBox2.Text &"') ", cn)
        'we inserted data from textbox into emp table, we didnt included id because it's datatype is autonumeric

        cmd.ExecuteNonQuery()

        MsgBox("Data Inserted Successfully", MsgBoxStyle.Information, vbOK)

        selectQuery()

        DataGridView1.DataSource = ds.Tables(0)

        cn.Close()

    End Sub

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

        Dim cmd As New OleDbCommand("Delete * from emp where id = " & TextBox1.Text & " ", cn)
        cmd.ExecuteNonQuery()

        selectQuery()

        cn.Close()
    End Sub

 

Update Query...............

 Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        cn.Open()

        Dim cmd As New OleDbCommand("update emp set Name='" & TextBox2.Text & "' where id = " & TextBox1.Text & " ", cn)

        cmd.ExecuteNonQuery()

        selectQuery()


        cn.Close()

    End Sub



Search Button Coding......................

You can search from database by two way. We will see first method double click on search button.
and write below code.

  Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        cn.Open()

        Dim adp As New OleDbDataAdapter("Select * from emp where name='" & TextBox4.Text & "' ", cn)
        ds.Clear()
        adp.Fill(ds)

        DataGridView1.DataSource = ds.Tables(0)
        cn.Close()

    End Sub




Search with Text Changed Event.......
 
 Private Sub TextBox4_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox4.TextChanged
        cn.Open()

        Dim adp As New OleDbDataAdapter("Select * from emp where City like '" & TextBox4.Text & "%' ", cn)
        ds.Clear()
        adp.Fill(ds)

        DataGridView1.DataSource = ds.Tables(0)
        cn.Close()
    End Sub

We will see next, previous and last button coding in next article till then enjoy and read Technology Stuff at Present 2 Upcoming