Imports System.Data.OleDb
Public Class Form1
Public connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|datadirectory|\youtubeDbase.accdb;persist security info = false"
Public conn As New OleDbConnection
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'TODO: This line of code loads data into the 'YoutubeDbaseDataSet.FullNameTable' table. You can move, or remove it, as needed.
Me.FullNameTableTableAdapter.Fill(Me.YoutubeDbaseDataSet.FullNameTable)
conn.ConnectionString = connstring
If conn.State = ConnectionState.Closed Then
conn.Open()
MsgBox("Loading Database Success!")
Else
MsgBox("Loading Database Failed!")
End If
loadlistview()
End Sub
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Try
Dim SqlQuery As String = "INSERT INTO FullNameTable (FirstName,MiddleName,LastName) VALUES ('" & txtFName.Text & "', '" & txtMName.Text & "','" & txtLName.Text & "')"
Dim SqlCommand As New OleDbCommand
With SqlCommand
.CommandText = SqlQuery
.Connection = conn
.ExecuteNonQuery()
End With
MsgBox("One record Successfully Added!")
txtFName.Text = ""
txtMName.Text = ""
txtLName.Text = ""
loadlistview()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Sub loadlistview()
ListView1.FullRowSelect = True
ListView1.MultiSelect = False
ListView1.View = View.Details
ListView1.Columns.Clear()
ListView1.Items.Clear()
ListView1.Columns.Add("ID", 50, HorizontalAlignment.Left)
ListView1.Columns.Add("First Name", 100, HorizontalAlignment.Left)
ListView1.Columns.Add("Middle Name", 100, HorizontalAlignment.Left)
ListView1.Columns.Add("Last Name", 100, HorizontalAlignment.Left)
Dim Sqlquery As String = "SELECT * FROM FullNameTable"
Dim SqlCommand As New OleDbCommand
Dim SqlAdapter As New OleDbDataAdapter
Dim TABLE As New DataTable
With SqlCommand
.CommandText = Sqlquery
.Connection = conn
End With
With SqlAdapter
.SelectCommand = SqlCommand
.Fill(TABLE)
End With
For i = 0 To TABLE.Rows.Count - 1
With ListView1
.Items.Add(TABLE.Rows(i)("ID"))
With .Items(.Items.Count - 1).SubItems
.Add(TABLE.Rows(i)("FirstName"))
.Add(TABLE.Rows(i)("MiddleName"))
.Add(TABLE.Rows(i)("LAstName"))
End With
End With
Next
End Sub
Public ID As Integer
Private Sub ListView1_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseClick
Dim Sqlquery As String = "SELECT * FROM FullNameTable"
Dim SqlCommand As New OleDbCommand
Dim SqlAdapter As New OleDbDataAdapter
Dim TABLE As New DataTable
With SqlCommand
.CommandText = Sqlquery
.Connection = conn
End With
With SqlAdapter
.SelectCommand = SqlCommand
.Fill(TABLE)
End With
If ListView1.SelectedItems.Count > 0 Then
ID = ListView1.SelectedItems(0).Text
txtFName.Text = ListView1.SelectedItems(0).SubItems(1).Text
txtMName.Text = ListView1.SelectedItems(0).SubItems(2).Text
txtLName.Text = ListView1.SelectedItems(0).SubItems(3).Text
End If
End Sub
Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click
Try
Dim SqlQuery As String = "UPDATE FullNameTable SET FirstName = '" & txtFName.Text & "', MiddleName = '" & txtMName.Text & "', LastName = '" & txtLName.Text & "' WHERE ID = " & ID & ";"
Dim SqlCommand As New OleDbCommand
With SqlCommand
.CommandText = SqlQuery
.Connection = conn
.ExecuteNonQuery()
End With
MsgBox("One record successfully updated!")
txtFName.Text = ""
txtMName.Text = ""
txtLName.Text = ""
loadlistview()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
Try
Dim SqlQuery As String = "DELETE FROM FullNameTable WHERE ID = " & ID & ";"
Dim SqlCommand As New OleDbCommand
With SqlCommand
.CommandText = SqlQuery
.Connection = conn
.ExecuteNonQuery()
End With
MsgBox("One record deleted..")
loadlistview()
txtFName.Text = ""
txtMName.Text = ""
txtLName.Text = ""
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
Dim SqlQuery As String = "SELECT * FROM FullNameTable where FirstName & Lastname & MiddleName like '%" & TextBox1.Text & "%' "
Dim SqlCommand As New OleDbCommand
Dim SqlAdpter As New OleDbDataAdapter
Dim TABLE As New DataTable
With SqlCommand
.CommandText = SqlQuery
.Connection = conn
End With
With SqlAdpter
.SelectCommand = SqlCommand
.Fill(TABLE)
End With
ListView1.Items.Clear()
For i = 0 To TABLE.Rows.Count - 1
With ListView1
.Items.Add(TABLE.Rows(i)("ID"))
With .Items(.Items.Count - 1).SubItems
.Add(TABLE.Rows(i)("FirstName"))
.Add(TABLE.Rows(i)("MiddleName"))
.Add(TABLE.Rows(i)("LastName"))
End With
End With
Next
End Sub
