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