vb.net mysql delete_VB.NET MYSQL DataGridView 增删改查（INSERT,SELECT,UPDATE,DELETE）

Imports MySql.Data.MySqlClient

Public Class Form1

' GLOBAL DECLARATIONS

Dim conString As String = "Server=localhost;Database=net2;Uid=root;Pwd=123456;"

Dim con As New MySqlConnection(conString)

Dim cmd As MySqlCommand

Dim adapter As MySqlDataAdapter

Dim dt As New DataTable()

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

'CONSTRUCT DATAGRIDVIEW

DataGridView1.ColumnCount = 4

DataGridView1.Columns(0).Name = "ID"

DataGridView1.Columns(1).Name = "Name"

DataGridView1.Columns(2).Name = "Position"

DataGridView1.Columns(3).Name = "Team"

DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect

End Sub

'CLEAR TXT

Private Sub cleartxt()

nameTxt.Text = ""

PosTxt.Text = ""

TeamTxt.Text = ""

End Sub

'INSERT INTO DB

Private Sub Add()

Dim sql As String = "INSERT INTO peopletb(Name,Position,Team) VALUES(@PNAME,@POSITION,@TEAM)"

cmd = New MySqlCommand(sql, con)

'PARAMETERS

cmd.Parameters.AddWithValue("@PNAME", nameTxt.Text)

cmd.Parameters.AddWithValue("@POSITION", PosTxt.Text)

cmd.Parameters.AddWithValue("@TEAM", TeamTxt.Text)

'OPEN CONNECTION And INSERT

Try

con.Open()

If cmd.ExecuteNonQuery() > 0 Then

MsgBox("Successfully Inserted")

cleartxt()

End If

con.Close()

retrieve()

Catch ex As Exception

MsgBox(ex.Message)

con.Close()

End Try

End Sub

'POPULATE DGVIEW

Private Sub Populate(id As String, name As String, pos As String, team As String)

Dim row As String() = New String() {id, name, pos, team}

'ADD ROW TO ROWS COLLEC

DataGridView1.Rows.Add(row)

End Sub

Private Sub retrieve()

DataGridView1.Rows.Clear()

'SQL STMT

Dim sql As String = "SELECT * FROM peopletb"

cmd = New MySqlCommand(sql, con)

'OPEN CON,RETRIEVE,FILL,DGVIEW

Try

con.Open()

adapter = New MySqlDataAdapter(cmd)

adapter.Fill(dt)

'FILL DGVIEW

For Each row In dt.Rows

Populate(row(0), row(1), row(2), row(3))

Next

con.Close()

'CLEAR DT

dt.Rows.Clear()

Catch ex As Exception

MsgBox(ex.Message)

con.Close()

End Try

End Sub

'UPDATE

Private Sub UpdateDG(id As String)

Dim sql As String = "UPDATE peopletb SET Name='" + nameTxt.Text + "',Position='" + PosTxt.Text + "',Team='" + TeamTxt.Text + "'WHERE ID='" + id + "'"

'OPEN CON,EXEUTE UPDATE,CLOSE

Try

con.Open()

adapter.UpdateCommand = con.CreateCommand()

adapter.UpdateCommand.CommandText = sql

If adapter.UpdateCommand.ExecuteNonQuery() > 0 Then

MsgBox("Successfully Updated")

cleartxt()

End If

con.Close()

'REFRESH

retrieve()

Catch ex As Exception

MsgBox(ex.Message)

con.Close()

End Try

End Sub

'delete

Private Sub deleteDG(id As String)

Dim sql As String = "DELETE FROM peopletb WHERE ID='" + id + "'"

cmd = New MySqlCommand(sql, con)

'OPEN CON,EXECUTE UPDATE,CLOSE CON

Try

con.Open()

adapter.DeleteCommand = con.CreateCommand()

adapter.DeleteCommand.CommandText = sql

'PROMPT FOR CONFIRMATION

If MessageBox.Show("sure??", "DELETE", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.OK Then

If cmd.ExecuteNonQuery() > 0 Then

MsgBox("Successfully Deleted")

End If

End If

con.Clone()

retrieve()

Catch ex As Exception

MsgBox(ex.Message)

con.Close()

End Try

End Sub

Private Sub DataGridView1_MouseClick(sender As Object, e As MouseEventArgs) Handles DataGridView1.MouseClick

Dim name As String = DataGridView1.SelectedRows(0).Cells(1).Value

Dim position As String = DataGridView1.SelectedRows(0).Cells(2).Value

Dim team As String = DataGridView1.SelectedRows(0).Cells(3).Value

nameTxt.Text = name

PosTxt.Text = position

TeamTxt.Text = team

End Sub

Private Sub addBtn_Click(sender As Object, e As EventArgs) Handles addBtn.Click

Add()

End Sub

Private Sub retrieweBtn_Click(sender As Object, e As EventArgs) Handles retrieweBtn.Click

retrieve()

End Sub

Private Sub UpdateBtn_Click(sender As Object, e As EventArgs) Handles UpdateBtn.Click

Dim id As String = DataGridView1.SelectedRows(0).Cells(0).Value

' MsgBox(id)

UpdateDG(id)

End Sub

Private Sub DeleteBtn_Click(sender As Object, e As EventArgs) Handles DeleteBtn.Click

Dim id As String = DataGridView1.SelectedRows(0).Cells(0).Value

deleteDG(id)

End Sub

End Class

'代码下载;QQ群 550818513