This source code shows you how to connect to an oracle database and do operations such as select, insert, update and delete.
Tools Used: Visual Studio.Net Beta 2, Oracle 8 database
How to use: Create a database table Emp with four data fields: EmpNo, EName, Sal, DeptNo and just try the code below!
Source Code:
Imports System
Imports System.Drawing
Imports System.Collections
Imports System.ComponentModel
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.OleDb
Namespace Employee
' <summary>
' Summary description for Form1.
' </summary>
Public Class FrmEmp
Inherits System.Windows.Forms.Form
Private label1 As System.Windows.Forms.Label
Private label2 As System.Windows.Forms.Label
Private label3 As System.Windows.Forms.Label
Private label4 As System.Windows.Forms.Label
Private TxtEmpNo As System.Windows.Forms.TextBox
Private TxtEName As System.Windows.Forms.TextBox
Private TxtESal As System.Windows.Forms.TextBox
Private TxtDeptNo As System.Windows.Forms.TextBox
Friend WithEvents BtnAddNew As System.Windows.Forms.Button
Friend WithEvents BtnSave As System.Windows.Forms.Button
Friend WithEvents BtnDelete As System.Windows.Forms.Button
Friend WithEvents BtnExit As System.Windows.Forms.Button
Private Conn As System.Data.OleDb.OleDbConnection
Private Comm As System.Data.OleDb.OleDbCommand
Private DataAdpt As System.Data.OleDb.OleDbDataAdapter
Private CBuild As System.Data.OleDb.OleDbCommandBuilder
Private DtRead As System.Data.OleDb.OleDbDataReader
Private DataSet1 As System.Data.DataSet
Private DataRow1 As System.Data.DataRow
Private DataTable1 As System.Data.DataTable
Private ConnStr As String
Private blnEdit As Boolean
Friend WithEvents BtnEdit As System.Windows.Forms.Button
' <summary>
' Required designer variable.
' </summary>
Private components As System.ComponentModel.Container = Nothing
Public Sub New()
' Required for Windows Form Designer support
'InitializeComponent()
ConnStr = "Provider=MSDAORA;DSN=oracle;"
Dim ID As User = system
Password = manager '
Conn = New OleDbConnection(ConnStr) '
Conn.Open()
DataAdpt = New OleDbDataAdapter("Select * from Emp", Conn)
CBuild = New OleDbCommandBuilder(DataAdpt)
DataSet1 = New DataSet("Emp")
DataTable1 = New DataTable("Emp")
DataAdpt.Fill(DataSet1, "Emp")
End Sub 'New '
' TODO: Add any constructor code after InitializeComponent call
' <summary>
' Clean up any resources being used.
'</summary>
Protected Overloads Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub 'Dispose
' the contents of this method with the code editor.
Sub InitializeComponent()
Me.label4 = New System.Windows.Forms.Label
Me.BtnExit = New System.Windows.Forms.Button
Me.label1 = New System.Windows.Forms.Label
Me.label2 = New System.Windows.Forms.Label
Me.label3 = New System.Windows.Forms.Label
Me.BtnEdit = New System.Windows.Forms.Button
Me.BtnSave = New System.Windows.Forms.Button
Me.BtnDelete = New System.Windows.Forms.Button
Me.TxtESal = New System.Windows.Forms.TextBox
Me.TxtEmpNo = New System.Windows.Forms.TextBox
Me.TxtEName = New System.Windows.Forms.TextBox
Me.BtnAddNew = New System.Windows.Forms.Button
Me.TxtDeptNo = New System.Windows.Forms.TextBox
Me.SuspendLayout()
' label4
Me.label4.AutoSize = True
Me.label4.Location = New System.Drawing.Point(104, 160)
Me.label4.Name = "label4"
Me.label4.Size = New System.Drawing.Size(59, 14)
Me.label4.TabIndex = 3
Me.label4.Text = "Dept No :"
' BtnExit
Me.BtnExit.Location = New System.Drawing.Point(280, 256)
Me.BtnExit.Name = "BtnExit"
Me.BtnExit.Size = New System.Drawing.Size(90, 32)
Me.BtnExit.TabIndex = 12
Me.BtnExit.Text = "E&xit"
AddHandler Me.BtnExit.Click, AddressOf Me.BtnExit_Click
' label1
Me.label1.AutoSize = True
Me.label1.Location = New System.Drawing.Point(104, 64)
Me.label1.Name = "label1"
Me.label1.Size = New System.Drawing.Size(58, 14)
Me.label1.TabIndex = 0
Me.label1.Text = "Emp No :"
' label2
Me.label2.AutoSize = True
Me.label2.Location = New System.Drawing.Point(104, 96)
Me.label2.Name = "label2"
Me.label2.Size = New System.Drawing.Size(75, 14)
Me.label2.TabIndex = 1
Me.label2.Text = "Emp Name :"
' label3
Me.label3.AutoSize = True
Me.label3.Location = New System.Drawing.Point(104, 128)
Me.label3.Name = "label3"
Me.label3.Size = New System.Drawing.Size(78, 14)
Me.label3.TabIndex = 2
Me.label3.Text = "Emp Salary :"
' BtnEdit
Me.BtnEdit.Location = New System.Drawing.Point(328, 208)
Me.BtnEdit.Name = "BtnEdit"
Me.BtnEdit.Size = New System.Drawing.Size(90, 32)
Me.BtnEdit.TabIndex = 10
Me.BtnEdit.Text = "Edit"
AddHandler Me.BtnEdit.Click, AddressOf Me.BtnEdit_Click
' BtnSave
Me.BtnSave.Location = New System.Drawing.Point(224, 208)
Me.BtnSave.Name = "BtnSave"
Me.BtnSave.Size = New System.Drawing.Size(90, 32)
Me.BtnSave.TabIndex = 9
Me.BtnSave.Text = "Save"
AddHandler Me.BtnSave.Click, AddressOf Me.BtnSave_Click
' BtnDelete
Me.BtnDelete.Location = New System.Drawing.Point(168, 256)
Me.BtnDelete.Name = "BtnDelete"
Me.BtnDelete.Size = New System.Drawing.Size(90, 32)
Me.BtnDelete.TabIndex = 11
Me.BtnDelete.Text = "Delete"
AddHandler Me.BtnDelete.Click, AddressOf Me.BtnDelete_Click
' TxtESal
Me.TxtESal.Location = New System.Drawing.Point(192, 128)
Me.TxtESal.Name = "TxtESal"
Me.TxtESal.Size = New System.Drawing.Size(112, 21)
Me.TxtESal.TabIndex = 6
Me.TxtESal.Text = ""
' TxtEmpNo
Me.TxtEmpNo.Location = New System.Drawing.Point(192, 64)
Me.TxtEmpNo.Name = "TxtEmpNo"
Me.TxtEmpNo.Size = New System.Drawing.Size(112, 21)
Me.TxtEmpNo.TabIndex = 4
Me.TxtEmpNo.Text = ""
' TxtEName
Me.TxtEName.Location = New System.Drawing.Point(192, 96)
Me.TxtEName.Name = "TxtEName"
Me.TxtEName.Size = New System.Drawing.Size(200, 21)
Me.TxtEName.TabIndex = 5
Me.TxtEName.Text = ""
' BtnAddNew
Me.BtnAddNew.Location = New System.Drawing.Point(120, 208)Me.BtnAddNew.Name = "BtnAddNew"
Me.BtnAddNew.Size = New System.Drawing.Size(90, 32)
Me.BtnAddNew.TabIndex = 8
Me.BtnAddNew.Text = "&Add New"
AddHandler Me.BtnAddNew.Click, AddressOf BtnAddNew_Click
' TxtDeptNo
Me.TxtDeptNo.Location = New System.Drawing.Point(192, 160)
Me.TxtDeptNo.Name = "TxtDeptNo"
Me.TxtDeptNo.Size = New System.Drawing.Size(112, 21)
Me.TxtDeptNo.TabIndex = 7
Me.TxtDeptNo.Text = ""
' FrmEmp
Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
Me.ClientSize = New System.Drawing.Size(576, 317)
Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.BtnExit, Me.BtnDelete, Me.BtnEdit, Me.BtnSave, Me.BtnAddNew, Me.TxtDeptNo, Me.TxtESal, Me.TxtEName, Me.TxtEmpNo, Me.label4, Me.label3, Me.label2, Me.label1})
Me.Font = New System.Drawing.Font("Microsoft Sans Serif", 9F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, System.Byte))
Me.Name = "FrmEmp"
Me.Text = "Employee Data Form"
AddHandler Me.Load, AddressOf Me.FrmEmp_Load
Me.ResumeLayout(False)
End If
' </summary>[STAThread]
Shared Sub Main() '
Application.Run(New FrmEmp)
End Sub 'Main
Private Sub FrmEmp_Load(ByVal sender As Object, ByVal e As System.EventArgs)End Sub 'FrmEmp_Load
Private Sub BtnExit_Click(ByVal sender As Object, ByVal e As System.EventArgs)Conn.Close()
Me.Close()
End Sub 'BtnExit_Click
Private Sub BtnAddNew_Click(ByVal sender As Object, ByVal e As System.EventArgs)
TxtEmpNo.Text = ""
TxtEName.Text = ""
TxtESal.Text = ""
TxtDeptNo.Text = ""
TxtEmpNo.Focus()
End Sub 'BtnAddNew_Click
Private Sub BtnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Try
If blnEdit = False Then
DataRow1 = DataSet1.Tables("Emp").NewRow()
DataRow1("EmpNo") = Int16.Parse(TxtEmpNo.Text)
DataRow1("EName") = TxtEName.Text
DataRow1("Sal") = [Double].Parse(TxtESal.Text)
DataRow1("DeptNo") = Int32.Parse(TxtDeptNo.Text)
DataSet1.Tables("Emp").Rows.Add(DataRow1)
DataAdpt.Update(DataSet1, "Emp")
MessageBox.Show("record saved!")
Else
Dim Str1 As String
Str1 = "Update Emp set EName='" + TxtEName.Text + "',Sal=" +
Double.Parse(TxtESal.Text) + ","
Str1 = Str1 + "DeptNo=" + Int16.Parse(TxtDeptNo.Text) + " Where EmpNo=" + Int16.Parse(TxtEmpNo.Text)
Comm = New OleDbCommand(Str1, Conn)
Comm.ExecuteNonQuery()
'drEdit.BeginEdit()
'drEdit("EName") = TxtEName.Text
drEdit("Sal") = double.Parse(TxtESal.Text);
drEdit("DeptNo") = Int16.Parse(TxtDeptNo.Text)
drEdit.EndEdit();
'DataAdpt.Update(DataSet1,"Emp")
MessageBox.Show("Record Modified!")
End If
Catch e1 As Exception
MessageBox.Show(e1.ToString())
End Try
End Sub 'BtnSave_Click
Private Sub BtnEdit_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim Str1 As String
Try
Str1 = "Select * from Emp Where EmpNo=" + Int16.Parse(TxtEmpNo.Text)
Comm = New OleDbCommand(Str1, Conn)
DtRead = Comm.ExecuteReader()
If DtRead.Read() Then
blnEdit = True
'drEdit = DataSet1.Tables["Emp"].Rows.Find(Int16.Parse
TxtEmpNo.Text));TxtEName.Text = DtRead["EName"].ToString();
TxtESal.Text = DtRead("Sal").ToString()
TxtDeptNo.Text = DtRead("DeptNo").ToString()
TxtEName.Focus()
Else
TxtEName.Text = ""
TxtESal.Text = ""
TxtDeptNo.Text = ""
MessageBox.Show("Record Not found!")
TxtEmpNo.Focus()
End If
DtRead.Close()
Catch e1 As Exception
MessageBox.Show(e1.ToString())
End Try
End Sub 'BtnEdit_Click
Private Sub BtnDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Try
Dim Str1 As Object
Dim Str2 As String
Str1 = MessageBox.Show("Do you want to delete the record", "Delete Box", System.Windows.Forms.MessageBoxButtons.YesNo, System.Windows.Forms.MessageBoxIcon.Warning)
'==DialogResult.Yes)if (Str1.ToString() == "Yes")
If (True) Then
Str2 = "Delete from Emp Where EmpNo=" + Int16.Parse(TxtEmpNo.Text)
Comm = New OleDbCommand(Str2, Conn)
Comm.ExecuteNonQuery()
MessageBox.Show("Record has been deleted!")
TxtEmpNo.Text = ""
TxtEName.Text = ""
TxtESal.Text = ""
TxtDeptNo.Text = ""
TxtEmpNo.Focus()
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub 'BtnDelete_Click
End namespaceend class