Imports Mysql.data.mysqlclient
Public Class Form1
Public dbcomm As MySqlCommand
Public dbconn As New MySqlConnection
Public sql As String
Public dbread As MySqlDataReader
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
dbconn = New MySqlConnection("data source=localhost; user id=root; database=qwerty")
Try
dbconn.Open()
Catch ex As Exception
MsgBox("error in connection" & ex.Message)
End Try
refresh_list()
End Sub
Sub refresh_list()
lstq.Items.Clear()
sql = "SELECT id, name, pg, mg, fg, ((pg+mg+fg)/3)as average FROM tab"
Try
dbcomm = New MySqlCommand(sql, dbconn)
dbread = dbcomm.ExecuteReader
While dbread.Read
lstq.Items.Add(dbread("id"))
lstq.Items(lstq.Items.Count - 1).SubItems.Add(dbread("name"))
lstq.Items(lstq.Items.Count - 1).SubItems.Add(dbread("pg"))
lstq.Items(lstq.Items.Count - 1).SubItems.Add(dbread("mg"))
lstq.Items(lstq.Items.Count - 1).SubItems.Add(dbread("fg"))
lstq.Items(lstq.Items.Count - 1).SubItems.Add(dbread("average"))
End While
dbread.Close()
Catch ex As Exception
MsgBox("error in displaying" & ex.Message)
End Try
dbread.Close()
End Sub
Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
sql = "INSERT INTO tab(name, pg, mg, fg) VALUES('" & txtname.Text & "' , '" & txtpg.Text & "' , '" & txtmg.Text & "' , '" & txtfg.Text & "')"
Try
dbcomm = New MySqlCommand(sql, dbconn)
dbread = dbcomm.ExecuteReader
dbread.Close()
Catch ex As Exception
MsgBox("error in saving data" & ex.Message)
End Try
MsgBox("save")
refresh_list()
End Sub
Private Sub btnup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnup.Click
If MsgBox("are you sure you want to update?", vbOKCancel) = vbOK Then
sql = "UPDATE tab set name='" & txtname.Text & _
"', pg ='" & txtpg.Text & _
"', mg ='" & txtmg.Text & _
"', fg ='" & txtfg.Text & _
"' Where id='" & lstq.SelectedItems(0).Text & "'"
Try
dbcomm = New MySqlCommand(sql, dbconn)
dbread = dbcomm.ExecuteReader
dbread.Close()
refresh_list()
btnup.Enabled = False
txtname.Text = ""
txtpg.Text = ""
txtmg.Text = ""
txtfg.Text = ""
Catch ex As Exception
MsgBox("error" & ex.Message)
dbread.Close()
End Try
dbread.Close()
lstq.Items.Clear()
refresh_list()
MsgBox("data save")
End If
End Sub
Private Sub btnup_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles btnup.MouseDoubleClick
sql = "SELECT id, name, pg, mg, fg FROM tab WHERE id='" & lstq.SelectedItems(0).Text & "'"
Try
dbcomm = New MySqlCommand(sql, dbconn)
dbread = dbcomm.ExecuteReader
While dbread.Read
txtname.Text = dbread("name")
txtpg.Text = dbread("pg")
txtmg.Text = dbread("mg")
txtfg.Text = dbread("fg")
End While
dbread.Close()
Catch ex As Exception
MsgBox("error in displaying data" & ex.Message)
dbread.Close()
End Try
dbread.Close()
End Sub
Private Sub lstq_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles lstq.MouseDoubleClick
sql = "SELECT id, name, pg, mg, fg FROM tab WHERE id='" & lstq.SelectedItems(0).Text & "'"
btnup.Enabled = True
Try
dbcomm = New MySqlCommand(sql, dbconn)
dbread = dbcomm.ExecuteReader
While dbread.Read
txtname.Text = dbread("name")
txtpg.Text = dbread("pg")
txtmg.Text = dbread("mg")
txtfg.Text = dbread("fg")
End While
dbread.Close()
Catch ex As Exception
MsgBox("error in displaying data" & ex.Message)
dbread.Close()
End Try
End Sub
Private Sub btndel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndel.Click
If MsgBox("are you sure you want to delete?") Then
sql = "DELETE FROM tab WHERE id='" & lstq.SelectedItems(0).Text & "'"
Try
dbcomm = New MySqlCommand(sql, dbconn)
dbread = dbcomm.ExecuteReader
dbread.Close()
refresh_list()
Catch ex As Exception
MsgBox("error in del" & ex.Message)
End Try
dbread.Close()
End If
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
lstq.Items.Clear()
sql = "SELECT id, name, pg, mg, fg, average FROM tab order by name ASC "
Try
dbcomm = New MySqlCommand(sql, dbconn)
dbread = dbcomm.ExecuteReader
While dbread.Read
lstq.Items.Add(dbread("id"))
lstq.Items(lstq.Items.Count - 1).SubItems.Add(dbread("name"))
lstq.Items(lstq.Items.Count - 1).SubItems.Add(dbread("pg"))
lstq.Items(lstq.Items.Count - 1).SubItems.Add(dbread("mg"))
lstq.Items(lstq.Items.Count - 1).SubItems.Add(dbread("fg"))
lstq.Items(lstq.Items.Count - 1).SubItems.Add(dbread("average"))
End While
Catch ex As Exception
MsgBox("error in displaying" & ex.Message)
End Try
dbread.Close()
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
lstq.Items.Clear()
sql = "SELECT id, name, pg, mg, fg, average FROM tab order by name DESC "
Try
dbcomm = New MySqlCommand(sql, dbconn)
dbread = dbcomm.ExecuteReader
While dbread.Read
lstq.Items.Add(dbread("id"))
lstq.Items(lstq.Items.Count - 1).SubItems.Add(dbread("name"))
lstq.Items(lstq.Items.Count - 1).SubItems.Add(dbread("pg"))
lstq.Items(lstq.Items.Count - 1).SubItems.Add(dbread("mg"))
lstq.Items(lstq.Items.Count - 1).SubItems.Add(dbread("fg"))
lstq.Items(lstq.Items.Count - 1).SubItems.Add(dbread("average"))
End While
Catch ex As Exception
MsgBox("error in displaying" & ex.Message)
End Try
dbread.Close()
End Sub
Private Sub txtsearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtsearch.TextChanged
lstq.Items.Clear()
sql = "SELECT id, name, pg, mg, fg, average FROM tab WHERE name like'%" & txtsearch.Text & "%'"
Try
dbcomm = New MySqlCommand(sql, dbconn)
dbread = dbcomm.ExecuteReader
While dbread.Read
lstq.Items.Add(dbread("id"))
lstq.Items(lstq.Items.Count - 1).SubItems.Add(dbread("name"))
lstq.Items(lstq.Items.Count - 1).SubItems.Add(dbread("pg"))
lstq.Items(lstq.Items.Count - 1).SubItems.Add(dbread("mg"))
lstq.Items(lstq.Items.Count - 1).SubItems.Add(dbread("fg"))
lstq.Items(lstq.Items.Count - 1).SubItems.Add(dbread("average"))
End While
Catch ex As Exception
MsgBox("error" & ex.Message)
End Try
dbread.Close()
End Sub
End Class
