Notes
Notes - notes.io |
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 studentsinfo"
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
|
Notes.io is a web-based application for taking notes. You can take your notes and share with others people. If you like taking long notes, notes.io is designed for you. To date, over 8,000,000,000 notes created and continuing...
With notes.io;
- * You can take a note from anywhere and any device with internet connection.
- * You can share the notes in social platforms (YouTube, Facebook, Twitter, instagram etc.).
- * You can quickly share your contents without website, blog and e-mail.
- * You don't need to create any Account to share a note. As you wish you can use quick, easy and best shortened notes with sms, websites, e-mail, or messaging services (WhatsApp, iMessage, Telegram, Signal).
- * Notes.io has fabulous infrastructure design for a short link and allows you to share the note as an easy and understandable link.
Fast: Notes.io is built for speed and performance. You can take a notes quickly and browse your archive.
Easy: Notes.io doesn’t require installation. Just write and share note!
Short: Notes.io’s url just 8 character. You’ll get shorten link of your note when you want to share. (Ex: notes.io/q )
Free: Notes.io works for 12 years and has been free since the day it was started.
You immediately create your first note and start sharing with the ones you wish. If you want to contact us, you can use the following communication channels;
Email: [email protected]
Twitter: http://twitter.com/notesio
Instagram: http://instagram.com/notes.io
Facebook: http://facebook.com/notesio
Regards;
Notes.io Team