VB.Net and Access Database Learn VB.Net By Building a Simple Application

VB.Net and Access Database Premium Video Tutorials : Learn VB.Net By Building a Simple Application.

🎓 Master Visual Basic .NET and Access Database By Building the Point Of Sale System (POS).

📲 Enroll Now: https://bit.ly/2WcbRhX

🎓 Recommended For You : YouTube Playlist

✅ Learn VB .Net By Building a Simple Application.

» http://bit.ly/2N71wSp

[Screenshot]

[Preview Video]

[Source Code]

Option Explicit On
Option Strict On

Public Class Form1

Private oneTimeRefresh As Integer = 0

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'EmployeeDatabaseDataSet.Departments' table. You can move, or remove it, as needed.
Me.DepartmentsTableAdapter.Fill(Me.EmployeeDatabaseDataSet.Departments)
'TODO: This line of code loads data into the 'EmployeeDatabaseDataSet.JobTitles' table. You can move, or remove it, as needed.
Me.JobTitlesTableAdapter.Fill(Me.EmployeeDatabaseDataSet.JobTitles)
'TODO: This line of code loads data into the 'EmployeeDatabaseDataSet.Prefixes' table. You can move, or remove it, as needed.
Me.PrefixesTableAdapter.Fill(Me.EmployeeDatabaseDataSet.Prefixes)
'TODO: This line of code loads data into the 'EmployeeDatabaseDataSet.Employees' table. You can move, or remove it, as needed.
Me.EmployeesTableAdapter.Fill(Me.EmployeeDatabaseDataSet.Employees)

Me.ToolStripComboBox1.DropDownStyle = ComboBoxStyle.DropDownList
Me.ToolStripComboBox1.Items.Clear()

Dim lstItems As New List(Of String) From {"Top 10", "Top 20", "Top 30", "Top 40", "Top 50", "Select All"}
ToolStripComboBox1.ComboBox.DataSource = lstItems

If ToolStripComboBox1.Items.Count > 0 Then
' Select All : idx = 5 (6 - 1)
Dim idx As Integer = Me.ToolStripComboBox1.Items.Count - 1
Me.ToolStripComboBox1.SelectedIndex = idx
End If

' Begin Setup DataGridView

Dim dgv As DataGridView = DataGridView1

For Each col As DataGridViewColumn In dgv.Columns
col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
col.HeaderCell.Style.Font = New Font("Microsoft Sans Serif", 14.0F, FontStyle.Regular, GraphicsUnit.Pixel)

col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
Next

dgv.RowsDefaultCellStyle.BackColor = Color.FromName("White")
dgv.AlternatingRowsDefaultCellStyle.BackColor = Color.FromName("GhostWhite")

' Hide Auto_ID
dgv.Columns(0).Visible = False
' Employee ID
dgv.Columns(1).Width = 170
dgv.Columns(1).HeaderText = "Employee ID"
' Reference ID
dgv.Columns(2).Width = 170
dgv.Columns(2).HeaderText = "Reference ID"
' ID Number
dgv.Columns(3).Width = 170
dgv.Columns(3).HeaderText = "ID Number"

' Hide Prefix_ID Column
dgv.Columns(4).Visible = False

With dgv
' First Name
.Columns(5).Width = 170
.Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
.Columns(5).HeaderText = "First Name"
' Last Name
.Columns(6).Width = 170
.Columns(6).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
.Columns(6).HeaderText = "Last Name"
' Birth Date
.Columns(7).Width = 170
.Columns(7).HeaderText = "Birth Date"
' Hire Date
.Columns(8).Width = 170
.Columns(8).HeaderText = "Hire Date"

' Address
.Columns(9).Width = 170
.Columns(9).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
.Columns(9).HeaderText = "Address"
' Phone Number
.Columns(10).Width = 170
.Columns(10).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
.Columns(10).HeaderText = "Phone Number"
' Email
.Columns(11).Width = 170
.Columns(11).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
.Columns(11).HeaderText = "E-mail"

' Salary
.Columns(12).Width = 100

' Hide Job_ID Column
.Columns(13).Visible = False

' Hold Down Alt and Drag Mouse

' Social Media
.Columns(14).Width = 150
.Columns(15).Width = 150
.Columns(16).Width = 150
.Columns(17).Width = 150
.Columns(18).Width = 150
.Columns(18).HeaderText = "Google+"

' Status
.Columns(19).Width = 90

' Hide Department_ID
.Columns(20).Visible = False

End With

' End Setup DataGridView

CreateAutoComplete()

End Sub

Private Sub CreateAutoComplete()

Dim autoComp As New AutoCompleteStringCollection()

For Each row As DataRow In Me.EmployeeDatabaseDataSet.Tables("Employees").Rows
autoComp.Add(CType(row("First_Name"), String) & Space(1) & CType(row("Last_Name"), String))
Next

With ToolStripTextBoxQuery
.AutoCompleteMode = AutoCompleteMode.SuggestAppend
.AutoCompleteSource = AutoCompleteSource.CustomSource
.AutoCompleteCustomSource = autoComp
End With

End Sub

Private Sub ToolStripComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ToolStripComboBox1.SelectedIndexChanged

Me.EmployeesBindingSource.Filter = Nothing

Select Case Me.ToolStripComboBox1.SelectedIndex
Case 0
Me.EmployeesTableAdapter.FillTop10(Me.EmployeeDatabaseDataSet.Employees)
Case 1
Me.EmployeesTableAdapter.FillTop20(Me.EmployeeDatabaseDataSet.Employees)
Case 2
Me.EmployeesTableAdapter.FillTop30(Me.EmployeeDatabaseDataSet.Employees)
Case 3
Me.EmployeesTableAdapter.FillTop40(Me.EmployeeDatabaseDataSet.Employees)
Case 4
Me.EmployeesTableAdapter.FillTop50(Me.EmployeeDatabaseDataSet.Employees)
Case 5
Me.EmployeesTableAdapter.Fill(Me.EmployeeDatabaseDataSet.Employees)
Case Else
Return
End Select

End Sub

Private Sub RefreshButton_Click(sender As Object, e As EventArgs) Handles RefreshButton.Click
Me.EmployeesBindingSource.Filter = Nothing
Me.EmployeesTableAdapter.Fill(Me.EmployeeDatabaseDataSet.Employees)
End Sub

Private Sub RefreshDataToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles RefreshDataToolStripMenuItem.Click
Me.RefreshButton.PerformClick()
End Sub

Private Sub PreviousButton_Click(sender As Object, e As EventArgs) Handles PreviousButton.Click
Me.EmployeesBindingSource.MovePrevious()
'
' Me.EmployeesBindingSource.MoveFirst()

End Sub

Private Sub NextButton_Click(sender As Object, e As EventArgs) Handles NextButton.Click
Me.EmployeesBindingSource.MoveNext()
' or go to last record
' Me.EmployeesBindingSource.MoveLast()
End Sub

Private Sub EnableEditingCheckBox_CheckedChanged(sender As Object, e As EventArgs) Handles EnableEditingCheckBox.CheckedChanged
Me.EnableEditingToolStripMenuItem.Checked = Me.EnableEditingCheckBox.Checked
End Sub

Private Sub EnableEditingToolStripMenuItem_CheckedChanged(sender As Object, e As EventArgs) Handles EnableEditingToolStripMenuItem.CheckedChanged
Me.EnableEditingCheckBox.Checked = Me.EnableEditingToolStripMenuItem.Checked
End Sub

Private Sub DeleteButton_Click(sender As Object, e As EventArgs) Handles DeleteButton.Click
' Backup your database first!

If Me.EnableEditingCheckBox.Checked = True Then

If Me.AddNewButton.Text = "Cancel" Then
Exit Sub
End If

If Not DataGridView1.Rows.Count = 0 Then

Try

Dim result As DialogResult
result = MessageBox.Show("Do you want to permanently delete the selected record?", "Deletion Confirmation : iBasskung Tutorial", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

If result = DialogResult.Yes Then

Me.EmployeesBindingSource.RemoveCurrent()
Me.EmployeesBindingSource.EndEdit()
Me.EmployeesTableAdapter.Update(Me.EmployeeDatabaseDataSet.Employees)

End If

Catch ex As Exception
MessageBox.Show("Unable to delete data from the database." & Environment.NewLine & "--> " & ex.Message.ToString(), "Delete data failed : iBasskung Tutorial", MessageBoxButtons.OK, MessageBoxIcon.Stop)
End Try

End If

Else
MessageBox.Show("Access Denied!" & Environment.NewLine & "You need permission to perform this action." & Environment.NewLine & Environment.NewLine & "Problem Solution:" & Environment.NewLine & "Please contact the programmer or ask the Google :)", "Delete operation failed : iBasskung Tutorial", MessageBoxButtons.OK, MessageBoxIcon.Stop)

Return
' Exit Sub
End If

End Sub

Private Sub AddNewButton_Click(sender As Object, e As EventArgs) Handles AddNewButton.Click

If Me.EnableEditingCheckBox.Checked = True Then

Me.EmployeesBindingSource.Filter = Nothing
Me.EmployeesTableAdapter.Fill(Me.EmployeeDatabaseDataSet.Employees)

Dim iYear As Integer = DateTime.Now().Year
Dim iMonth As Integer = Now().Month
Dim iDay As Integer = Now().Day

Dim formatDate As String = $"{iYear}{iMonth.ToString("00")}{iDay.ToString("00")}"

Me.FirstNameTextBox.Focus()

If Me.AddNewButton.Text = "Add New" Then
Me.EmployeesBindingSource.AddNew()

Me.EmployeeIDTextBox.Text = "IBK-" & formatDate & "-" & String.Format("{0:D5}", Me.DataGridView1.Rows.Count - 1 + 1)

Me.AddNewButton.Text = "Cancel"

Me.PrefixComboBox.SelectedIndex = 0
Me.JobTitleComboBox.SelectedIndex = 0
Me.DepartmentComboBox.SelectedIndex = 0

Else

Me.EmployeesBindingSource.EndEdit()
Me.AddNewButton.Text = "Add New"
Me.EmployeesTableAdapter.Fill(Me.EmployeeDatabaseDataSet.Employees)
Exit Sub

End If

End If

End Sub

Private Sub AddNewToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles AddNewToolStripMenuItem.Click
Me.AddNewButton.PerformClick()
End Sub

Private Sub EmployeeIDTextBox_TextChanged(sender As Object, e As EventArgs) Handles EmployeeIDTextBox.TextChanged
If Not String.IsNullOrEmpty(Me.EmployeeIDTextBox.Text.ToString()) Then

Dim i64 As Long
If Long.TryParse(Strings.Right(Me.EmployeeIDTextBox.Text.Trim().ToString(), 5), i64) Then
Me.NoTextBox.Text = i64.ToString()
Else
' Unable to parse
Me.NoTextBox.Text = String.Empty ' or ""

End If

End If
End Sub

Private Sub SaveButton_Click(sender As Object, e As EventArgs) Handles SaveButton.Click
If Me.EnableEditingCheckBox.Checked = True Then

Try

Dim result As New DialogResult()
result = MessageBox.Show("Do you want to save the selected record?", "Save | Update : iBasskung Tutorial", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

If result = DialogResult.Yes Then

Me.Validate()
Me.EmployeesBindingSource.EndEdit()
Me.EmployeesTableAdapter.Update(Me.EmployeeDatabaseDataSet.Employees)

MessageBox.Show("Record has been saved successfully.", "Save Data : iBasskung Tutorial", MessageBoxButtons.OK, MessageBoxIcon.Information)

' refresh data
Me.EmployeesTableAdapter.Fill(Me.EmployeeDatabaseDataSet.Employees)

Me.AddNewButton.Text = "Add New"

If Me.ToolStripComboBox1.Items.Count > 0 Then
Dim idx As Integer = Me.ToolStripComboBox1.Items.Count - 1
Me.ToolStripComboBox1.SelectedIndex = idx
End If

' so far :)

Else
' No button was pressed.
' Do nothing :)
Return
End If

Catch ex As Exception
' Something went wrong :)
MessageBox.Show("Unable to save data into the database." & vbNewLine & "--> " & ex.Message.ToString(), "Failed to save the date : iBasskung Tutorial", MessageBoxButtons.OK, MessageBoxIcon.Stop)
End Try

End If
End Sub

Private Sub SaveToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SaveToolStripMenuItem.Click
' Sorry :)
Me.SaveButton.PerformClick()
End Sub

Private Sub ExitToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ExitToolStripMenuItem.Click
Application.Exit()
End Sub

Private Sub SearchToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SearchToolStripMenuItem.Click

If (SearchToolStripMenuItem.Text = "Search >>") And (ToolStripTextBoxQuery.Visible = False) Then

With ToolStripTextBoxQuery
.Visible = True
.Text = String.Empty
.Focus()
.Select()
End With

SearchToolStripMenuItem.Text = "Search <<"
SearchToolStripMenuItem.ForeColor = Color.Crimson

Return

ElseIf (ToolStripTextBoxQuery.Visible = True) And (String.IsNullOrEmpty(ToolStripTextBoxQuery.Text)) Then

ToolStripTextBoxQuery.Visible = False
SearchToolStripMenuItem.Text = "Search >>"
SearchToolStripMenuItem.ForeColor = Color.Black

' one time refresh is enough :)
oneTimeRefresh += 1

If (oneTimeRefresh > 1) Then Return

' Value cannot be null.

' refresh data
EmployeesTableAdapter.FillByMyQueries(EmployeeDatabaseDataSet.Employees, Nothing, Nothing, Nothing)

Return

Else

' reset counter
oneTimeRefresh = 0

Dim stringQuery As String = ToolStripTextBoxQuery.Text.Trim().ToString()
EmployeesTableAdapter.FillByFullNameAndAddress(EmployeeDatabaseDataSet.Employees, stringQuery, stringQuery, stringQuery, stringQuery)

End If

End Sub

End Class

📝 Follow me around

Want to get updates on new courses or other cool free stuff? Just follow me on social media if that's your thing!

Twitter

📌 https://twitter.com/#!/IBasskung

Facebook ಠ_ಠ

📌 https://www.facebook.com/CodeAMinute

Facebook (✪ω✪)

📌 https://www.facebook.com/IbasskungTutorial

Facebook ఠ_ఠ

📌 https://www.facebook.com/IbasskungGuitar

Google+

📌 http://google.com/+iBasskung

📌 https://plus.google.com/u/0/107523775437712370629/posts