今天学习了刘金玉老师零基础VB教程的第85期,也是我学习VB基础知识的最后一期,学习的主要内容是数据库mshflexgrid数据库联动更新。
1.思路:今天学习的内容,主要在昨天数据删除联动的基础上,增加数据联动更新的功能,通过鼠标选择确定需要更新的行号,然后将选中行的数据库内容链接到新的窗体(form2)的text控件中,然后修改form2的text控件内容,最后将更新后的内容,分别输入数据库和mshflexgrid表格,最终达到更新数据的目的。
2.补充知识:Form2.Show vbModal表示的是form窗体弹出时候,原关联的窗体只能在后面。
3.案例:数据库和mshflexgrid联动录入删除查询和更新
(1)控件
窗体一:
窗体二:
(2)代码:
窗体一代码:
Option Explicit
Dim sql As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Private Sub Command1_Click()
'连接数据库,并通过text控件连接数据库表格,实现数据输入
If conn.State = 0 Then
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\作业成果\学习VB编程第68天\Database1.mdb;Persist Security Info=False" '连接语句填写在此处
conn.Open
End If
sql = "insert into [users]([username],[sex],[age]) values('" & Text1.Text & "','" & Text2.Text & "','" & Text3.Text & "')"
Set rs = New ADODB.Recordset
rs.Open sql, conn, adOpenKeyset, adLockBatchOptimistic
'同步显示数据到MSHFlexGrid表格
sql = "select * from users"
rs.Open sql, conn, adOpenKeyset, adLockBatchOptimistic
Set MSHFlexGrid1.DataSource = rs
MSHFlexGrid1.TextMatrix(0, 0) = "序号"
MSHFlexGrid1.TextMatrix(0, 1) = "姓名"
MSHFlexGrid1.TextMatrix(0, 2) = "性别"
MSHFlexGrid1.TextMatrix(0, 3) = "年龄"
End Sub
Public Sub Command2_Click()
'连接数据库,并通过text控件连接数据库表格
If conn.State = 0 Then
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\作业成果\学习VB编程第68天\Database1.mdb;Persist Security Info=False"
conn.Open
End If
Set rs = New ADODB.Recordset
sql = "select * from users"
rs.Open sql, conn, adOpenKeyset, adLockBatchOptimistic
Set MSHFlexGrid1.DataSource = rs
MSHFlexGrid1.TextMatrix(0, 0) = "序号"
MSHFlexGrid1.TextMatrix(0, 1) = "姓名"
MSHFlexGrid1.TextMatrix(0, 2) = "性别"
MSHFlexGrid1.TextMatrix(0, 3) = "年龄"
Command4.Enabled = True
End Sub
Private Sub Command3_Click()
If conn.State = 0 Then
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\作业成果\学习VB编程第68天\Database1.mdb;Persist Security Info=False"
conn.Open
End If
Set rs = New ADODB.Recordset
sql = "delete from [users] where [id]=" & MSHFlexGrid1.TextMatrix(MSHFlexGrid1.Row, 0)
rs.Open sql, conn, adOpenKeyset, adLockBatchOptimistic
'方法一删除MSHFlexGrid数据
'Call Command2_Click
'方法二更新MSHFlexGrid数据
MSHFlexGrid1.RemoveItem MSHFlexGrid1.Row
End Sub
Private Sub Command4_Click()
Form2.Show vbModal
End Sub
Private Sub Form_Load()
End Sub
窗体二代码:
Option Explicit
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Private Sub Command1_Click()
'打开数据库
If conn.State = 0 Then
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\作业成果\学习VB编程第68天\Database1.mdb;Persist Security Info=False"
conn.Open
End If
'更新数据库
Set rs = New ADODB.Recordset
sql = "update [users] set [username]='" & Text1.Text & "',[sex]='" & Text2.Text & "',[age]='" & Text3.Text & "' where id=" & Label5.Caption
rs.Open sql, conn, adOpenKeyset, adLockBatchOptimistic
'更新MSHFlexGrid表格
Form1.MSHFlexGrid1.TextMatrix(Form1.MSHFlexGrid1.Row, 1) = Text1.Text
Form1.MSHFlexGrid1.TextMatrix(Form1.MSHFlexGrid1.Row, 2) = Text2.Text
Form1.MSHFlexGrid1.TextMatrix(Form1.MSHFlexGrid1.Row, 3) = Text3.Text
Unload Form2
End Sub
Private Sub Form_Load()
Label5.Caption = Form1.MSHFlexGrid1.TextMatrix(Form1.MSHFlexGrid1.Row, 0)
Text1.Text = Form1.MSHFlexGrid1.TextMatrix(Form1.MSHFlexGrid1.Row, 1)
Text2.Text = Form1.MSHFlexGrid1.TextMatrix(Form1.MSHFlexGrid1.Row, 2)
Text3.Text = Form1.MSHFlexGrid1.TextMatrix(Form1.MSHFlexGrid1.Row, 3)
End Sub
(3)运行效果详见视频
,