VB.NET MYSQL DataGridView 增删改查(INSERT,SELECT,UPDATE,DEL
发布时间:2020-12-17 07:29:08 所属栏目:百科 来源:网络整理
导读:? Imports MySql.Data.MySqlClientPublic 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 MySq
? 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,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 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |