Imports MySql.Data.
MySqlClient
Public Class Form1
' Initialize the connection with a connection string directly
Dim con As New MySqlConnection("server=localhost;database=vbnet;Uid=root;pwd=''")
Dim cmd As MySqlCommand
Dim id As Integer
Dim currentIndex As Integer = 0
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
' Display data when the form loads]=
disp_data()
End Sub
' Method to display data in DataGridView
Public Sub disp_data()
Try
' Open connection if not already open
If con.State = ConnectionState.Closed Then
con.Open()
End If
' Set up the command and data adapter
cmd = con.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT * FROM table1"
Dim da As New MySqlDataAdapter(cmd)
Dim dt As New DataTable()
da.Fill(dt)
DataGridView1.DataSource = dt
Catch ex As Exception
MessageBox.Show("Error displaying data: " & ex.Message)
Finally
' Close the connection to free resources
If con.State = ConnectionState.Open Then
con.Close()
End If
End Try
End Sub
' Button to add a new record to the table
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Try
' Open connection if not already open
If con.State = ConnectionState.Closed Then
con.Open()
End If
' Set up the command to insert data
cmd = con.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "INSERT INTO table1 VALUES (NULL, @value1, @value2, @value3)"
cmd.Parameters.AddWithValue("@value1", TextBox1.Text)
cmd.Parameters.AddWithValue("@value2", TextBox2.Text)
cmd.Parameters.AddWithValue("@value3", TextBox3.Text)
cmd.ExecuteNonQuery()
' Clear text boxes and refresh data
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
disp_data()
MessageBox.Show("Record saved successfully")
Catch ex As Exception
MessageBox.Show("Error saving record: " & ex.Message)
Finally
' Ensure the connection is closed
If con.State = ConnectionState.Open Then
con.Close()
End If
End Try
End Sub
' Button to refresh the data in DataGridView
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
disp_data()
End Sub
' Button to filter data based on the value in TextBox1
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
Try
' Open connection if not already open
If con.State = ConnectionState.Closed Then
con.Open()
End If
' Set up the command with a WHERE clause to filter by a specific column
cmd = con.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT * FROM table1 WHERE firstname = @value" ' Replace
'column_name' with the actual column name
cmd.Parameters.AddWithValue("@value", TextBox1.Text)
' Execute the query and display the filtered data
Dim da As New MySqlDataAdapter(cmd)
Dim dt As New DataTable()
da.Fill(dt)
DataGridView1.DataSource = dt
Catch ex As Exception
MessageBox.Show("Error filtering data: " & ex.Message)
Finally
' Ensure the connection is closed
If con.State = ConnectionState.Open Then
con.Close()
End If
End Try
End Sub
Private Sub DataGridView1_CellContentClick(sender As Object, e As
DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick, DataGridView1.CellClick
Try
' Get the ID and other values from the selected cell
id = Convert.ToInt32(DataGridView1.SelectedCells.Item(0).Value.ToString())
TextBox1.Text = DataGridView1.SelectedCells.Item(1).Value.ToString() ' Assuming this is
firstname
TextBox2.Text = DataGridView1.SelectedCells.Item(2).Value.ToString() ' Assuming this is
lastname
TextBox3.Text = DataGridView1.SelectedCells.Item(3).Value.ToString() ' Assuming this is
email
Catch ex As Exception
MessageBox.Show("Error: " & ex.Message)
End Try
End Sub
Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
Try
' Check if a valid ID is selected
If id = 0 Then
MessageBox.Show("Please select a record to update.")
Return
End If
' Open connection if not already open
If con.State = ConnectionState.Closed Then
con.Open()
End If
' Set up the command to update the record with the values in TextBox1, TextBox2, and
TextBox3
cmd = con.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "UPDATE table1 SET firstname = @firstname, lastname =
@lastname, city = @city WHERE id = @id"
cmd.Parameters.AddWithValue("@firstname", TextBox1.Text)
cmd.Parameters.AddWithValue("@lastname", TextBox2.Text)
cmd.Parameters.AddWithValue("@city", TextBox3.Text)
cmd.Parameters.AddWithValue("@id", id)
' Execute the command
cmd.ExecuteNonQuery()
' Clear text boxes and reset the ID
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
id = 0 ' Reset id after update
disp_data()
MessageBox.Show("Record updated successfully")
Catch ex As Exception
MessageBox.Show("Error updating record: " & ex.Message)
Finally
' Ensure the connection is closed
If con.State = ConnectionState.Open Then
con.Close()
End If
End Try
End Sub
Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
Try
' Check if a valid ID is selected
If id = 0 Then
MessageBox.Show("Please select a record to delete.")
Return
End If
' Confirm deletion
Dim confirmDelete As DialogResult = MessageBox.Show("Are you sure you want to delete
this record?", "Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Warning)
If confirmDelete = DialogResult.No Then
Return
End If
' Open connection if not already open
If con.State = ConnectionState.Closed Then
con.Open()
End If
' Set up the command to delete the record with the selected id
cmd = con.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "DELETE FROM table1 WHERE id = @id"
cmd.Parameters.AddWithValue("@id", id)
' Execute the command
cmd.ExecuteNonQuery()
' Clear text boxes and reset the ID
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
id = 0 ' Reset id after delete
disp_data()
MessageBox.Show("Record deleted successfully")
Catch ex As Exception
MessageBox.Show("Error deleting record: " & ex.Message)
Finally
' Ensure the connection is closed
If con.State = ConnectionState.Open Then
con.Close()
End If
End Try
End Sub
Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
End
End Sub
Private Sub UpdateTextBoxes()
If DataGridView1.Rows.Count > 0 AndAlso currentIndex >= 0 AndAlso currentIndex <
DataGridView1.Rows.Count Then
' Ensure each cell contains a non-null value before accessing
TextBox1.Text = If(DataGridView1.Rows(currentIndex).Cells(1).Value IsNot Nothing,
DataGridView1.Rows(currentIndex).Cells(1).Value.ToString(), "")
TextBox2.Text = If(DataGridView1.Rows(currentIndex).Cells(2).Value IsNot Nothing,
DataGridView1.Rows(currentIndex).Cells(2).Value.ToString(), "")
TextBox3.Text = If(DataGridView1.Rows(currentIndex).Cells(3).Value IsNot Nothing,
DataGridView1.Rows(currentIndex).Cells(3).Value.ToString(), "")
Else
MessageBox.Show("No data to display.")
End If
End Sub
Private Sub btnPrevious_Click(sender As Object, e As EventArgs) Handles btnPrevious.Click
If currentIndex > 0 Then
currentIndex -= 1
UpdateTextBoxes()
Else
MessageBox.Show("You are already at the first record.")
End If
End Sub
Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
If currentIndex < DataGridView1.Rows.Count - 1 Then
currentIndex += 1
UpdateTextBoxes()
Else
MessageBox.Show("You are already at the last record.")
End If
End Sub
Private Sub BtnFirst_Click(sender As Object, e As EventArgs) Handles BtnFirst.Click
currentIndex = 0
UpdateTextBoxes()
End Sub
Private Sub BtnLast_Click(sender As Object, e As EventArgs) Handles BtnLast.Click
If DataGridView1.Rows.Count > 0 Then
currentIndex = DataGridView1.Rows.Count - 1
UpdateTextBoxes()
End If
End Sub
End Class
