Public Class employeeBrowser
Inherits System.Windows.Forms.Form
Create a new connection object.
Dim myConnection As New OleDb.OleDbConnection
declare data adapter
Dim daEmployeeBrowser As New OleDb.OleDbDataAdapter("SELECT employeeLName, employeeFName, employeeID, employeeTitle, employeeAddress, employeeHireDate, employeeCity FROM tblEmployee ORDER BY employeeLName", myConnection)
declare dataSet
Dim dsEmployee As New DataSet("dsEmployee")
#Region " Windows Form Designer generated code "
#End Region
Sub getFilePath()
here the program check if the file that assigns the path of the connection exist
if not then invoke a fileOpenDialog so the user can browse for the file and the resultant
path is used as the connection path.
check if the path file exist
Dim fileExist As Boolean
fileExist = System.IO.File.Exists("dbPath.dat")
if it doesnt then create one with the path that returns a fileOpenDialog
If fileExist = False Then
MessageBox.Show("This is the first time you open the program or the databse has been moved from its directory." & vbCr & _
"Please Use the next browser to find the database that you want to use", "Find Database", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
Dim openFileDialog1 As New OpenFileDialog
openFileDialog1.InitialDirectory = "c:\"
openFileDialog1.Filter = "Database (employeeBrowserDB.mdb)|employeeBrowserDB.mdb"
openFileDialog1.FilterIndex = 2
openFileDialog1.RestoreDirectory = True
If openFileDialog1.ShowDialog() = DialogResult.OK Then
Dim getFilePath As String
getFilePath = openFileDialog1.FileName()
Dim fileCreator As IO.Stream
fileCreator = IO.File.Create("dbPath.dat")
fileCreator.Close()
Dim fileWriter As New IO.StreamWriter("dbPath.dat")
fileWriter.Write(getFilePath)
fileWriter.Close()
Else
MessageBox.Show("The program could not find the path for your database", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
End
End If
End If
asign the path of the database connection
Dim dbPathReader As System.IO.StreamReader = New System.IO.StreamReader("dbPath.dat")
Dim dbPath As String
dbPath = dbPathReader.ReadLine()
dbPathReader.Close()
Dim path As String = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" & dbPath
myConnection.ConnectionString = path
End Sub
Sub PopulateDataSet()
Try
Open the connection
myConnection.Open()
Populate dataSet with the data adapter
dsEmployee.Clear()
daEmployeeBrowser.Fill(dsEmployee, "tblEmployee")
close the connection
myConnection.Close()
if there is any error show a warning
Catch
MessageBox.Show("The program could not find the database or the database is corrupt" & vbCr & _
"Please RESTART the program and then follow the steps to find the path of the databse." & vbCr & _
"If you find the database and this warning pops up again then the database is corrupt" & vbCr & _
"If this is the case, please contact your software vendor for further assistance.", _
"Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
this deletes the path file so the user can relink the database in case someone moved it
Kill("dbPath.dat")
End
End Try
End Sub
Private Sub employeeBrowser_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
get the file path
getFilePath()
Grab de populate function
PopulateDataSet()
bind to combo box and the text boxes
cboLName.DataSource = dsEmployee
cboLName.DisplayMember = "tblemployee.employeeLName"
cboLName.ValueMember = "tblEmployee.employeeLName"
txtFName.DataBindings.Add("Text", dsEmployee, "tblEmployee.employeeFName")
txtEmployeeID.DataBindings.Add("Text", dsEmployee, "tblEmployee.employeeID")
txtCity.DataBindings.Add("Text", dsEmployee, "tblEmployee.employeeCity")
txtAddress.DataBindings.Add("Text", dsEmployee, "tblEmployee.employeeAddress")
txtHireDate.DataBindings.Add("Text", dsEmployee, "tblEmployee.employeeHireDate")
txtTitle.DataBindings.Add("Text", dsEmployee, "tblEmployee.employeeTitle")
txtLName.DataBindings.Add("Text", dsEmployee, "tblEmployee.employeeLName")
End Sub
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
disable and enable controls as needed
btnEdit.Enabled = False
btnDelete.Enabled = False
lblSearch.Visible = False
btnAccept.Visible = True
btnCancel.Visible = True
cboLName.Visible = False
txtFName.ReadOnly = False
txtCity.ReadOnly = False
txtAddress.ReadOnly = False
txtTitle.ReadOnly = False
txtLName.ReadOnly = False
txtHireDate.ReadOnly = False
btnAdd.Enabled = False
txtFName.Focus()
adds a new row and sets the binding position to that row
Me.BindingContext(dsEmployee, "tblEmployee").AddNew()
gives the actual date and time as default value
txtHireDate.Text = Date.Now
auto number does not accept nulls so i give a default value
txtEmployeeID.Text = "0"
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Delete the current row from the DataSet.
Dim oRow As DataRow
Dim oTable As DataTable
Dim intResponse As Integer
intResponse = MessageBox.Show("Are you sure you want to delete the " & _
"current record from the DataSet", "Delete", MsgBoxStyle.YesNo, MessageBoxIcon.Question)
If they confirm they want to delete, then go ahead and remove
the record from the DataSet.
If intResponse = vbYes Then
oTable = dsEmployee.Tables("tblEmployee")
oRow = oTable.Rows(BindingContext(dsEmployee, "tblEmployee").Position)
If Not oRow.RowState = DataRowState.Deleted Then
oRow.Delete()
MovePrevious()
completes the editing proces
Me.BindingContext(dsEmployee, "tblEmployee").EndCurrentEdit()
gives the delete function
daCommands()
we update the actualdatabase
Dim dsChanges As New DataSet
dsChanges = dsEmployee.GetChanges()
daEmployeeBrowser.Update(dsEmployee, "tblEmployee")
MessageBox.Show("Record Has Been Deleted.")
refills the dataset
PopulateDataSet()
End If
End If
cboLName.Focus()
End Sub
Private Sub btnAccept_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAccept.Click
validate the fields to make sure all of them are filled
If txtFName.Text = "" Or txtLName.Text = "" Or txtCity.Text = "" Or txtAddress.Text = "" Or txtTitle.Text = "" Or txtHireDate.Text = "" Then
MessageBox.Show("All fields have to be filled", "Empty Fields", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
completes the editing proces
Me.BindingContext(dsEmployee, "tblEmployee").EndCurrentEdit()
integer to count the number of records updated
Dim RecordsUpdated As Integer
if something have changed since last update
If dsEmployee.HasChanges Then
Update any customer table changes.
Dim dsChanges As New DataSet
dsChanges = dsEmployee.GetChanges()
daCommands()
RecordsUpdated = daEmployeeBrowser.Update(dsEmployee, "tblEmployee")
refill the dataset
PopulateDataSet()
return controls to browse function
btnAdd.Enabled = True
btnDelete.Enabled = True
lblSearch.Visible = True
btnAccept.Visible = False
btnCancel.Visible = False
cboLName.Visible = True
txtFName.ReadOnly = True
txtCity.ReadOnly = True
txtAddress.ReadOnly = True
txtHireDate.ReadOnly = True
txtTitle.ReadOnly = True
txtLName.ReadOnly = True
btnEdit.Enabled = True
MessageBox.Show(RecordsUpdated.ToString & " Employee record(s) updated.")
Else
MessageBox.Show("There are no changed records to update.")
End If
End If
cboLName.Focus()
End Sub
End Class
ill try to gain better habits.
