working database for beginner.

This intended to beginner about database in vb.net.

Dataadapter with dataset - OLEDB Data Source

Code:

---

Imports System.Data.OleDb
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connetionString As String
        Dim connection As OleDbConnection
        Dim oledbAdapter As OleDbDataAdapter
        Dim ds As New DataSet
        Dim i As Integer
        connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Your mdb filename;"
        connection = New OleDbConnection(connetionString)
        Try
            connection.Open()
            oledbAdapter = New OleDbDataAdapter("Your SQL Statement Here")
            oledbAdapter.Fill(ds)
            oledbAdapter.Dispose()
            connection.Close()
            For i = 0 To ds.Tables(0).Rows.Count - 1
                MsgBox(ds.Tables(0).Rows(i).Item(0))
            Next
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
End Class

---

Dataadapter SelectCommand - OLEDB Data Source

Code:

---

Imports System.Data.OleDb
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connetionString As String
        Dim connection As OleDbConnection
        Dim oledbAdapter As New OleDbDataAdapter
        Dim ds As New DataSet
        Dim i As Integer
        connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Your mdb filename;"
        connection = New OleDbConnection(connetionString)
        Try
            connection.Open()
            oledbAdapter.SelectCommand = New OleDbCommand("Your SQL Statement Here", connection)
            oledbAdapter.Fill(ds)
            oledbAdapter.Dispose()
            connection.Close()
            For i = 0 To ds.Tables(0).Rows.Count - 1
                MsgBox(ds.Tables(0).Rows(i).Item(0))
            Next
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
End Class

---

Dataadapter InsertCommand - Sql Server

Code:

---

Imports System.Data.SqlClient
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connetionString As String
        Dim connection As SqlConnection
        Dim adapter As New SqlDataAdapter
        Dim sql As String
        connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
        connection = New SqlConnection(connetionString)
        sql = "insert into product (Product_id,Product_name,Product_price) values(7,'Product7',700)"
        Try
            connection.Open()
            adapter.InsertCommand = New SqlCommand(sql, connection)
            adapter.InsertCommand.ExecuteNonQuery()
            MsgBox("Row inserted !! ")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
End Class

---

Dataadapter UpdateCommand - OLEDB Data Source

Code:

---

Imports System.Data.OleDb
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connetionString As String
        Dim connection As OleDbConnection
        Dim oledbAdapter As New OleDbDataAdapter
        Dim sql As String
        connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Your mdb filename;"
        connection = New OleDbConnection(connetionString)
        sql = "update Users set Password = 'new password' where UserID = 'user1'"
        Try
            connection.Open()
            oledbAdapter.UpdateCommand = connection.CreateCommand
            oledbAdapter.UpdateCommand.CommandText = sql
            oledbAdapter.UpdateCommand.ExecuteNonQuery()
            MsgBox("Row(s) Updated !! ")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
End Class

---

Dataadapter DeleteCommand - Sql Server

Code:

---

Imports System.Data.SqlClient
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connetionString As String
        Dim connection As SqlConnection
        Dim adapter As New SqlDataAdapter
        Dim sql As String
        connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
        connection = New SqlConnection(connetionString)
        sql = "delete product where Product_name ='Product7'"
        Try
            connection.Open()
            adapter.DeleteCommand = connection.CreateCommand
            adapter.DeleteCommand.CommandText = sql
            adapter.DeleteCommand.ExecuteNonQuery()
            MsgBox("Row(s) deleted  !! ")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
End Class

---

Dataadapter with CommandBuilder - Sql Server

Code:

---

Imports System.Data.SqlClient
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connetionString As String
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim cmdBuilder As SqlCommandBuilder
        Dim ds As New DataSet
        Dim sql As String
        Dim i As Int32
        connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
        connection = New SqlConnection(connetionString)
        sql = "select * from Product"
        Try
            connection.Open()
            adapter = New SqlDataAdapter(sql, connection)
            cmdBuilder = New SqlCommandBuilder(adapter)
            adapter.Fill(ds)
            For i = 0 To ds.Tables(0).Rows.Count - 1
                ds.Tables(0).Rows(i).Item(2) = ds.Tables(0).Rows(i).Item(2) + 100
            Next
            adapter.Update(ds.Tables(0))
            connection.Close()
            MsgBox("Data updated ! ")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
End Class

---