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

概述 VB与MySQL连接及数据写入和检索 1.VB 代码 ----------------------------------------------------------------------------------------- 'System.Data.SqlClient Imports MySql.Data.MySqlClient Public Class Form1     Pri 

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;

总结

以上是内存溢出为你收集整理的VB从MySQLDB检索数据以及写入数据全部内容，希望文章能够帮你解决VB从MySQLDB检索数据以及写入数据所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。