Imports System.Data.OleDb
Public Class Form1
Inherits System.Windows.Forms.Form
Private GridStudentTableDisplay As DataTable
Private StudentConnection As OleDbConnection
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides 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
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents MainMenu1 As System.Windows.Forms.MainMenu
Friend WithEvents MenuItem1 As System.Windows.Forms.MenuItem
Friend WithEvents MenuItem2 As System.Windows.Forms.MenuItem
Friend WithEvents MenuItem3 As System.Windows.Forms.MenuItem
Friend WithEvents MenuItem4 As System.Windows.Forms.MenuItem
Friend WithEvents DataGridStudentList As System.Windows.Forms.DataGrid
Friend WithEvents Button1 As System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.MainMenu1 = New System.Windows.Forms.MainMenu
Me.MenuItem1 = New System.Windows.Forms.MenuItem
Me.MenuItem2 = New System.Windows.Forms.MenuItem
Me.MenuItem3 = New System.Windows.Forms.MenuItem
Me.MenuItem4 = New System.Windows.Forms.MenuItem
Me.DataGridStudentList = New System.Windows.Forms.DataGrid
Me.Button1 = New System.Windows.Forms.Button
CType(Me.DataGridStudentList, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'MainMenu1
'
Me.MainMenu1.MenuItems.AddRange(New System.Windows.Forms.MenuItem() {Me.MenuItem1})
'
'MenuItem1
'
Me.MenuItem1.Index = 0
Me.MenuItem1.MenuItems.AddRange(New System.Windows.Forms.MenuItem() {Me.MenuItem2, Me.MenuItem3, Me.MenuItem4})
Me.MenuItem1.Text = "Student Info"
'
'MenuItem2
'
Me.MenuItem2.Index = 0
Me.MenuItem2.Text = "Add a Student"
'
'MenuItem3
'
Me.MenuItem3.Index = 1
Me.MenuItem3.Text = "Delete a Student"
'
'MenuItem4
'
Me.MenuItem4.Index = 2
Me.MenuItem4.Text = "Show all Students"
'
'DataGridStudentList
'
Me.DataGridStudentList.DataMember = ""
Me.DataGridStudentList.HeaderForeColor = System.Drawing.SystemColors.ControlText
Me.DataGridStudentList.Location = New System.Drawing.Point(16, 8)
Me.DataGridStudentList.Name = "DataGridStudentList"
Me.DataGridStudentList.Size = New System.Drawing.Size(280, 152)
Me.DataGridStudentList.TabIndex = 0
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point(224, 176)
Me.Button1.Name = "Button1"
Me.Button1.TabIndex = 1
Me.Button1.Text = "Close"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(312, 217)
Me.Controls.Add(Me.Button1)
Me.Controls.Add(Me.DataGridStudentList)
Me.Menu = Me.MainMenu1
Me.Name = "Form1"
Me.Text = "Form1"
CType(Me.DataGridStudentList, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
End Sub
#End Region
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
GridStudentTableDisplay = New DataTable
GridStudentTableDisplay.Columns.Add("Student ID") 'setting the captions
GridStudentTableDisplay.Columns.Add("Student Name")
GridStudentTableDisplay.Columns.Add("Student Age")
DataGridStudentList.DataSource = GridStudentTableDisplay
DataGridStudentList.CaptionText = "Student List"
'creating the connectio and opening the database
StudentConnection = New OleDbConnection
'setting the connection string
StudentConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source="
'full path to the database
StudentConnection.ConnectionString += "F:SivaprasadVBDotNetDatabaseProgrammingStudentDatabaseExampleStudentDatabase.mdb"
Try
StudentConnection.Open() 'opening the connection
Catch ex As Exception
MessageBox.Show(ex.ToString()) 'shows the exception in the messagebox if occured
End Try
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.Close()
End Sub
Private Sub MenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem2.Click
Dim frmaddstudent As New FormAddStudent
frmaddstudent.SetDbConnection(StudentConnection) 'setting the connection for the new form
frmaddstudent.Show() 'displaying the form
End Sub
Private Sub MenuItem4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem4.Click
Try
Dim mySelectQuery As String = "SELECT * FROM STUDENT_TB"
Dim StudentCommand As New OleDbCommand(mySelectQuery, StudentConnection)
'creating new dataadapter and setting its command
Dim StudentDataAdapter As New OleDbDataAdapter
StudentDataAdapter.SelectCommand = StudentCommand
'creating the dataset and filling it
Dim StudentDataset As New DataSet
StudentDataAdapter.Fill(StudentDataset, "STUDENT_TB")
'displaying the data into the datagrid
DataGridStudentList.DataSource = StudentDataset.Tables("STUDENT_TB")
Catch ex As Exception
MessageBox.Show(ex.ToString()) 'showing the exceptions if any
End Try
End Sub
Private Sub MenuItem3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem3.Click
'creating the delete form
Dim frmdeletestudent As New FormDeleteStudent
'setting the database connection
frmdeletestudent.SetDbConnection(StudentConnection)
'showing the form
frmdeletestudent.Show()
End Sub
End Class
FormAddStudent.vb
Imports System.Data.OleDb
Public Class FormAddStudent
Inherits System.Windows.Forms.Form
Dim studentoledbConnection As OleDbConnection
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides 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
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents Button2 As System.Windows.Forms.Button
Friend WithEvents Button1 As System.Windows.Forms.Button
Friend WithEvents TextBoxAge As System.Windows.Forms.TextBox
Friend WithEvents TextBoxName As System.Windows.Forms.TextBox
Friend WithEvents Label2 As System.Windows.Forms.Label
Friend WithEvents Label1 As System.Windows.Forms.Label
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.Button2 = New System.Windows.Forms.Button
Me.Button1 = New System.Windows.Forms.Button
Me.TextBoxAge = New System.Windows.Forms.TextBox
Me.TextBoxName = New System.Windows.Forms.TextBox
Me.Label2 = New System.Windows.Forms.Label
Me.Label1 = New System.Windows.Forms.Label
Me.SuspendLayout()
'
'Button2
'
Me.Button2.Location = New System.Drawing.Point(192, 128)
Me.Button2.Name = "Button2"
Me.Button2.Size = New System.Drawing.Size(48, 23)
Me.Button2.TabIndex = 11
Me.Button2.Text = "Close"
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point(128, 128)
Me.Button1.Name = "Button1"
Me.Button1.Size = New System.Drawing.Size(56, 23)
Me.Button1.TabIndex = 10
Me.Button1.Text = "Add"
'
'TextBoxAge
'
Me.TextBoxAge.Location = New System.Drawing.Point(128, 80)
Me.TextBoxAge.Name = "TextBoxAge"
Me.TextBoxAge.Size = New System.Drawing.Size(112, 20)
Me.TextBoxAge.TabIndex = 9
Me.TextBoxAge.Text = ""
'
'TextBoxName
'
Me.TextBoxName.Location = New System.Drawing.Point(128, 32)
Me.TextBoxName.Name = "TextBoxName"
Me.TextBoxName.Size = New System.Drawing.Size(112, 20)
Me.TextBoxName.TabIndex = 8
Me.TextBoxName.Text = ""
'
'Label2
'
Me.Label2.Location = New System.Drawing.Point(48, 80)
Me.Label2.Name = "Label2"
Me.Label2.Size = New System.Drawing.Size(24, 23)
Me.Label2.TabIndex = 7
Me.Label2.Text = "Age"
'
'Label1
'
Me.Label1.Location = New System.Drawing.Point(48, 32)
Me.Label1.Name = "Label1"
Me.Label1.Size = New System.Drawing.Size(48, 23)
Me.Label1.TabIndex = 6
Me.Label1.Text = "Name"
'
'FormAddStudent
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(292, 182)
Me.Controls.Add(Me.Button2)
Me.Controls.Add(Me.Button1)
Me.Controls.Add(Me.TextBoxAge)
Me.Controls.Add(Me.TextBoxName)
Me.Controls.Add(Me.Label2)
Me.Controls.Add(Me.Label1)
Me.Name = "FormAddStudent"
Me.Text = "FormAddStudent"
Me.ResumeLayout(False)
End Sub
#End Region
Private Sub FormAddStudent_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub
Public Sub SetDbConnection(ByRef DBConnection As OleDbConnection)
studentoledbConnection = DBConnection
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Me.Close()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
Dim AddCommand As New OleDbCommand 'creating new oledb command
AddCommand.Connection = studentoledbConnection 'setting the connection
Dim strAddQuery As String
strAddQuery = "insert into STUDENT_TB(StudentName,StudentAge) values('" + TextBoxName.Text + "','" + TextBoxAge.Text + "')"
AddCommand.CommandText = strAddQuery 'setting the query
AddCommand.ExecuteNonQuery() 'executing the query
MessageBox.Show("Student Added...", "Student Database")
Catch ex As Exception
MessageBox.Show(ex.ToString()) 'showing the exception if any
End Try
End Sub
End Class
FormDeleteStudent.vb
Imports System.Data.OleDb
Public Class FormDeleteStudent
Inherits System.Windows.Forms.Form
Private studentoledbConnection As OleDbConnection
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides 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
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents LabelName As System.Windows.Forms.Label
Friend WithEvents Label1 As System.Windows.Forms.Label
Friend WithEvents Button2 As System.Windows.Forms.Button
Friend WithEvents ButtonDelete As System.Windows.Forms.Button
Friend WithEvents listBoxStudent As System.Windows.Forms.ListBox
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.LabelName = New System.Windows.Forms.Label
Me.Label1 = New System.Windows.Forms.Label
Me.Button2 = New System.Windows.Forms.Button
Me.ButtonDelete = New System.Windows.Forms.Button
Me.listBoxStudent = New System.Windows.Forms.ListBox
Me.SuspendLayout()
'
'LabelName
'
Me.LabelName.Location = New System.Drawing.Point(101, 136)
Me.LabelName.Name = "LabelName"
Me.LabelName.TabIndex = 9
'
'Label1
'
Me.Label1.Location = New System.Drawing.Point(21, 144)
Me.Label1.Name = "Label1"
Me.Label1.Size = New System.Drawing.Size(48, 16)
Me.Label1.TabIndex = 8
Me.Label1.Text = "Name"
'
'Button2
'
Me.Button2.Location = New System.Drawing.Point(197, 80)
Me.Button2.Name = "Button2"
Me.Button2.TabIndex = 7
Me.Button2.Text = "Close"
'
'ButtonDelete
'
Me.ButtonDelete.Location = New System.Drawing.Point(197, 32)
Me.ButtonDelete.Name = "ButtonDelete"
Me.ButtonDelete.TabIndex = 6
Me.ButtonDelete.Text = "Delete"
'
'listBoxStudent
'
Me.listBoxStudent.Location = New System.Drawing.Point(21, 24)
Me.listBoxStudent.Name = "listBoxStudent"
Me.listBoxStudent.Size = New System.Drawing.Size(128, 95)
Me.listBoxStudent.TabIndex = 5
'
'FormDeleteStudent
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(292, 190)
Me.Controls.Add(Me.LabelName)
Me.Controls.Add(Me.Label1)
Me.Controls.Add(Me.Button2)
Me.Controls.Add(Me.ButtonDelete)
Me.Controls.Add(Me.listBoxStudent)
Me.Name = "FormDeleteStudent"
Me.Text = "FormDeleteStudent"
Me.ResumeLayout(False)
End Sub
#End Region
Public Sub SetDbConnection(ByRef DBConnection As OleDbConnection)
studentoledbConnection = DBConnection 'setting the database connection
End Sub
Private Sub FormDeleteStudent_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
LoadStudentInfo()
End Sub
Public Sub LoadStudentInfo()
Try
Dim deleteCommand As New OleDbCommand
deleteCommand.Connection = studentoledbConnection
Dim strLoadQuery As String
strLoadQuery = "SELECT * FROM STUDENT_TB"
deleteCommand.CommandText = strLoadQuery
'creating the reader and executingit
Dim deleteReader As OleDbDataReader = deleteCommand.ExecuteReader()
listBoxStudent.Items.Clear()
'adding items into the listbox
While deleteReader.Read
listBoxStudent.Items.Add(deleteReader.GetValue(0))
End While
'closing the reader
deleteReader.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Me.Close()
End Sub
Private Sub listBoxStudent_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles listBoxStudent.SelectedIndexChanged
Try
Dim findCommand As New OleDbCommand 'creating new command
findCommand.Connection = studentoledbConnection
Dim strFindQuery As String
strFindQuery = "SELECT * FROM STUDENT_TB WHERE StudentID= " + CType(listBoxStudent.SelectedItem, String)
findCommand.CommandText = strFindQuery 'setting the query to execute with the datareader
Dim deleteReader As OleDbDataReader = findCommand.ExecuteReader
deleteReader.Read() 'reading from datareader
LabelName.Text = deleteReader.GetValue(1) 'setting the name into thelabel
deleteReader.Close() 'closing the reader
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
End Sub
Private Sub ButtonDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonDelete.Click
Try
Dim deleteCommand As New OleDbCommand
'setting the connection
deleteCommand.Connection = studentoledbConnection
Dim strDeleteQuery As String
strDeleteQuery = "DELETE FROM STUDENT_TB WHERE StudentID = " + CType(listBoxStudent.SelectedItem, String)
'setting the query
deleteCommand.CommandText = strDeleteQuery
'executing the reader
Dim deleteReader As OleDbDataReader = deleteCommand.ExecuteReader
'closing the reader
deleteReader.Close()
'reloading the student id into the listbox
LabelName.Text = ""
LoadStudentInfo()
MessageBox.Show("Student Deleted ...", "Student Database")
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
End Sub
End Class