VB从MySQLDB检索数据以及写入数据

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処理失敗！22222111")
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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!