VB与MysqL连接及数据写入和检索

1.VB 代码

-----------------------------------------------------------------------------------------

'System.Data.sqlClient
Imports MysqL.Data.MysqLClient

Public Class Form1

Private Sub Form1_Load(sender As Object,e As EventArgs) Handles MyBase.Load
'On Error GoTo Err1
Dim pubssqlConn As MysqLConnection 'db 连接
Dim conStr As String
pubssqlConn = New MysqLConnection

conStr = "server=localhost;user id=root;pwd=root;database=vb;port=3306" '连接字符串
pubssqlConn.ConnectionString = conStr
pubssqlConn.Open()

Dim tr As MysqLTransaction = pubssqlConn.BeginTransaction '事务
Dim sql As String
sql = "select * from user" '如果是select之类语句，最好用个dataAdapter

Dim cmd As New MysqLCommand(sql,pubssqlConn,tr)
Dim adpt As MysqLDataAdapter = New MysqLDataAdapter(cmd)
Dim ds As New DataSet
Dim BS As New BindingSource
'Dim records As Integer = cmd.ExecuteNonQuery() '执行
adpt.Fill(ds,"信息数据库")
BS.DataSource = ds.Tables("信息数据库")
DataGridView1.DataSource = BS
'Err1:
' MsgBox("DB処理失敗！")
End Sub

Private Sub bt001_Click(sender As Object,e As EventArgs) Handles bt001.Click
' On Error GoTo Err1
Dim pubssqlConn As MysqLConnection 'db 连接
Dim conStr As String
pubssqlConn = New MysqLConnection

conStr = "server=localhost;user id=root;pwd=root;database=vb;port=3306" '连接字符串
pubssqlConn.ConnectionString = conStr
pubssqlConn.Open()

Dim tr As MysqLTransaction = pubssqlConn.BeginTransaction '事务
Dim sql As String
sql = "insert into user(userName,passWord,authority,recordMakeTime,recordUpdateTime,deleteflg) values( " & "'" & TextBox1.Text & "'" & "," & "'" & TextBox2.Text & "'" & "," & "'" & ComboBox1.Text & "'" & "," & "'" & Format$(Now,"General Date") & "'" & "," & "'0'" & ")" '如果是select之类语句，最好用个dataAdapter
MsgBox(sql)
Dim cmd As New MysqLCommand(sql,tr)

Dim records As Integer = cmd.ExecuteNonQuery() '执行
If (records >= 1) Then
tr.Commit() '提交　　提交失败也可回滚 我这边就不定了．
MsgBox("DBにデータ挿入済！")
Exit Sub
Else
MsgBox("DB処理失敗！11111111")
tr.Rollback()
Debug.WriteLine(records) '结果应是＞=1
Debug.Print(conStr)
Exit Sub
End If
'Err1:
' MsgBox("DB処理失敗！")
End Sub

Private Sub Button1_Click(sender As Object,e As EventArgs) Handles Button1.Click
ActiveForm.Close()
End Sub
End Class

--------------------------------------------------------------------------------------------

2.MysqL DB的表定义

MysqL> show columns from user;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| userName | varchar(10) | NO | PRI | NULL | |
| passWord | varchar(15) | NO | | NULL | |
| authority | varchar(8) | NO | | NULL | |
| recordMakeTime | varchar(20) | YES | | NULL | |
| recordUpdateTime | varchar(20) | YES | | NULL | |
| deleteflg | char(1) | YES | | NULL | |
+------------------+-------------+------+-----+---------+-------+

3.MysqL DB用户权限检索（局域网内两台机器连接是，要看用户权限）

MysqL> select user,host from MysqL.user;
+-----------+-----------+
| user | host |
+-----------+-----------+
| libaoan | % |
| root | % |
| test | % |
| test1 | % |
| MysqL.sys | localhost |

| root | localhost |
+-----------+-----------+

4. 用户权限赋予

MysqL> grant all privileges on vb.* to root@"%" identified by 'root' with grant option;