Notes

Imports MySql.Data.MySqlClient
Imports System.Data

Public Class Form1
Dim Connect As MySqlConnection
Dim ConnectDB As String = "data source=localhost;user id=root;database=student1;SSL Mode=None"
Dim IsConnected As Boolean = False

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
End Sub

Private Sub btnConnect_Click(sender As Object, e As EventArgs) Handles btnConnect.Click
Try
Connect = New MySqlConnection(ConnectDB)
Connect.Open()
IsConnected = True
MsgBox("Successfully connected to the Database!", vbOKOnly, "Connected")
Catch ex As Exception
IsConnected = False
MessageBox.Show(ex.Message)
End Try
End Sub

Private Sub btnLoad_Click(sender As Object, e As EventArgs) Handles btnLoad.Click
If Not IsConnected Then
MessageBox.Show("Please connect to the database first.", "Not Connected", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Return
End If

Try
Dim query As String = "SELECT * FROM studentsinfo1"
Dim command As New MySqlCommand(query, Connect)
Dim adapter As New MySqlDataAdapter(command)
Dim table As New DataTable()

adapter.Fill(table)
DataGridView1.DataSource = table
Catch ex As MySqlException
MessageBox.Show("Error: " & ex.Message)
End Try
End Sub

Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
If Not IsConnected Then
MessageBox.Show("Please connect to the database first.", "Not Connected", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Return
End If

Try
Dim studentID As String = txtStudentID.Text.Trim()
Dim firstName As String = txtFirstName.Text.Trim()
Dim lastName As String = txtLastName.Text.Trim()

Dim query As String = "SELECT * FROM studentsinfo WHERE (StudentID LIKE @studentID) AND (FName LIKE @firstName) AND (LName LIKE @lastName)"
Dim command As New MySqlCommand(query, Connect)

command.Parameters.AddWithValue("@studentID", "%" & studentID & "%")
command.Parameters.AddWithValue("@firstName", "%" & firstName & "%")
command.Parameters.AddWithValue("@lastName", "%" & lastName & "%")

Dim adapter As New MySqlDataAdapter(command)
Dim table As New DataTable()

adapter.Fill(table)
DataGridView1.DataSource = table
Catch ex As MySqlException
MessageBox.Show("Error: " & ex.Message)
End Try
End Sub

Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
If Not IsConnected Then
MessageBox.Show("Please connect to the database first.", "Not Connected", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Return
End If

Try
Dim studentID As String = txtStudent.Text.Trim()
Dim firstName As String = txtFirst.Text.Trim()
Dim lastName As String = txtLast.Text.Trim()
Dim middlename As String = txtMiddle.Text.Trim()
Dim address As String = txtAddress.Text.Trim()
Dim contactno As String = txtContacts.Text.Trim()
Dim email As String = txtEmail.Text.Trim()

Dim query As String = "INSERT INTO studentsinfo (StudentID, FName, LName, MName, Address, ContactNo, Email) VALUES (@studentID, @firstName, @lastName, @middlename, @address, @contactno, @email)"
Dim command As New MySqlCommand(query, Connect)

command.Parameters.AddWithValue("@studentID", studentID)
command.Parameters.AddWithValue("@firstName", firstName)
command.Parameters.AddWithValue("@lastName", lastName)
command.Parameters.AddWithValue("@middlename", middlename)
command.Parameters.AddWithValue("@address", address)
command.Parameters.AddWithValue("@contactno", contactno)
command.Parameters.AddWithValue("@email", email)

If Connect.State = ConnectionState.Closed Then
Connect.Open()
End If

command.ExecuteNonQuery()

MessageBox.Show("Data added successfully.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As MySqlException
MessageBox.Show("Error: " & ex.Message)
Finally

If Connect.State = ConnectionState.Open Then
Connect.Close()
End If
End Try
End Sub

Private Sub btnView_Click(sender As Object, e As EventArgs) Handles btnView.Click
If btnView.Enabled = True Then
txtStudent.Enabled = False
End If

If DataGridView1.SelectedRows.Count > 0 Then
Dim selectedRow As DataGridViewRow = DataGridView1.SelectedRows(0)

txtStudent.Text = selectedRow.Cells("StudentID").Value.ToString()
txtFirst.Text = selectedRow.Cells("FName").Value.ToString()
txtLast.Text = selectedRow.Cells("LName").Value.ToString()
txtMiddle.Text = selectedRow.Cells("MName").Value.ToString()
txtAddress.Text = selectedRow.Cells("Address").Value.ToString()
txtContacts.Text = selectedRow.Cells("ContactNo").Value.ToString()
txtEmail.Text = selectedRow.Cells("Email").Value.ToString()
Else
MessageBox.Show("Please select a row to view.", "No Row Selected", MessageBoxButtons.OK, MessageBoxIcon.Warning)
End If
End Sub

Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
If Not IsConnected Then
MessageBox.Show("Please connect to the database first.", "Not Connected", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Return
End If


If String.IsNullOrEmpty(txtStudent.Text.Trim()) Then
MessageBox.Show("Please select a row to update.", "No Row Selected", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Return
End If

Try
Dim studentID As String = txtStudent.Text.Trim()
Dim firstName As String = txtFirst.Text.Trim()
Dim lastName As String = txtLast.Text.Trim()
Dim middleName As String = txtMiddle.Text.Trim()
Dim address As String = txtAddress.Text.Trim()
Dim contactNo As String = txtContacts.Text.Trim()
Dim email As String = txtEmail.Text.Trim()

Dim query As String = "UPDATE studentsinfo SET FName=@firstName, LName=@lastName, MName=@middleName, Address=@address, ContactNo=@contactNo, Email=@email WHERE StudentID=@studentID"
Dim command As New MySqlCommand(query, Connect)

command.Parameters.AddWithValue("@studentID", studentID)
command.Parameters.AddWithValue("@firstName", firstName)
command.Parameters.AddWithValue("@lastName", lastName)
command.Parameters.AddWithValue("@middleName", middleName)
command.Parameters.AddWithValue("@address", address)
command.Parameters.AddWithValue("@contactNo", contactNo)
command.Parameters.AddWithValue("@email", email)

If Connect.State = ConnectionState.Closed Then
Connect.Open()
End If

command.ExecuteNonQuery()
Connect.Close()

MessageBox.Show("Data updated successfully.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)

btnLoad.PerformClick()
Catch ex As Exception
MessageBox.Show("Error: " & ex.Message)
If Connect.State = ConnectionState.Open Then
Connect.Close()
End If
End Try
End Sub

Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
If Not IsConnected Then
MessageBox.Show("Please connect to the database first.", "Not Connected", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Return
End If

If DataGridView1.SelectedRows.Count > 0 Then
Dim selectedRow As DataGridViewRow = DataGridView1.SelectedRows(0)
Dim studentID As Integer = Convert.ToInt32(selectedRow.Cells("StudentID").Value)

Try
Dim deleteQuery As String = "DELETE FROM studentsinfo WHERE StudentID = @studentID"
Dim deleteCommand As New MySqlCommand(deleteQuery, Connect)
deleteCommand.Parameters.AddWithValue("@studentID", studentID)

Connect.Open()
deleteCommand.ExecuteNonQuery()
Connect.Close()

Dim resetQuery As String = "ALTER TABLE studentsinfo AUTO_INCREMENT = @nextID"
Dim resetCommand As New MySqlCommand(resetQuery, Connect)
resetCommand.Parameters.AddWithValue("@nextID", studentID)

Connect.Open()
resetCommand.ExecuteNonQuery()
Connect.Close()

MessageBox.Show("Record deleted", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)

btnLoad.PerformClick()
Catch ex As Exception
MessageBox.Show("Error: " & ex.Message)
If Connect.State = ConnectionState.Open Then
Connect.Close()
End If
End Try
Else
MessageBox.Show("Please select a row to delete.", "No Row Selected", MessageBoxButtons.OK, MessageBoxIcon.Warning)
End If
End Sub

End Class