学习记录:VB.NET.操作ACCESS数据库

作者: admin 分类: Vb.net 发布时间: 2022-06-24 11:34 浏览 134 次

从98年到现在,VB6使用了20多年,真的舍不得扔掉,但是随着新计算机预装操作系统的版本越来越高,做好的安装包在安装时遇到的问题越来越多,无奈之下只能转向使用VB.NET了。安装了VS2005来学习,这个家伙不错,个头不是特大,在xp上跑的很顺畅,哈哈。

今天学习在VB.NET上操作ACCESS数据库,为防止将来忘记,记录于此。

一、清空某张表中的全部数据

Dim cn As OleDbConnection
cn = New OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & Application.StartupPath & “\Comm.mdb”)
cn.Open() ‘打开当前文件夹下comm.mdb文件

Dim cmd As New OleDbCommand()
cmd.Connection = cn
cmd.CommandText = “delete * from tbTest” ‘清空表tbTest
cmd.ExecuteNonQuery()

cmd.Dispose() ‘释放资源
cn.Close()
cn.Dispose() ‘释放资源 感觉很像VB6里的 set xx=nothing
MessageBox.Show(“全部删除完成”)

二 向某张表插入数据

Dim cn As OleDbConnection
cn = New OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & Application.StartupPath & “\Comm.mdb”)
cn.Open() ‘打开当前文件夹下comm.mdb文件

Dim cmd As New OleDbCommand()
cmd.Connection = cn

Dim id As Long
Dim sex As String
Dim address As String

id = 1
sex = “男”
address = “松山区”
cmd.CommandText = “insert into tbtest (id,sex,address) values(” & id & “,” & Chr(39) & sex & Chr(39) & “,” & Chr(39) & address & Chr(39) & “)”
cmd.ExecuteNonQuery()

id = 2
sex = “男”
address = “红山区”
cmd.CommandText = “insert into tbtest (id,sex,address) values(” & id & “,” & Chr(39) & sex & Chr(39) & “,” & Chr(39) & address & Chr(39) & “)”
cmd.ExecuteNonQuery()

id = 3
sex = “女”
address = “元宝山区”
cmd.CommandText = “insert into tbtest (id,sex,address) values(” & id & “,” & Chr(39) & sex & Chr(39) & “,” & Chr(39) & address & Chr(39) & “)”
cmd.ExecuteNonQuery()

cmd.Dispose()
cn.Close()
cn.Dispose()
MessageBox.Show(“插入3行测试完成”)

三 修改

Dim cn As OleDbConnection
cn = New OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & Application.StartupPath & “\Comm.mdb”)
cn.Open() ‘打开当前文件夹下comm.mdb文件

Dim cmd As New OleDbCommand()
cmd.Connection = cn
Dim strNewAddress As String
strNewAddress = “平庄”
Dim strCondition As String
strCondition = “元宝山区”
cmd.CommandText = “update tbtest set address=” & Chr(39) & strNewAddress & Chr(39) & ” where address=” & Chr(39) & strCondition & Chr(39)
cmd.ExecuteNonQuery()

cmd.Dispose()
cn.Close()
cn.Dispose()
MessageBox.Show(“修改测试完成”)

四 删除

Dim cn As OleDbConnection
cn = New OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & Application.StartupPath & “\Comm.mdb”)
cn.Open() ‘打开当前文件夹下comm.mdb文件

Dim cmd As New OleDbCommand()
cmd.Connection = cn
Dim strCondition As String
strCondition = “平庄”
cmd.CommandText = “delete from tbtest where address=” & Chr(39) & strCondition & Chr(39)
cmd.ExecuteNonQuery()

cmd.Dispose()
cn.Close()
cn.Dispose()
MessageBox.Show(“删除测试完成”)

五 查询

Dim cn As OleDbConnection
cn = New OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & Application.StartupPath & “\Comm.mdb”)
cn.Open() ‘打开当前文件夹下comm.mdb文件

Dim lngIDCondition As Long
lngIDCondition = 2
Dim strSexCondition As String
strSexCondition = “男”

Dim cmd As New OleDbCommand()
cmd.Connection = cn
cmd.CommandText = “select * from tbTest where id=” & lngIDCondition & ” and sex=” & Chr(39) & strSexCondition & Chr(39)

Dim dr As OleDbDataReader
dr = cmd.ExecuteReader()

If dr.HasRows() = True Then
Do While dr.Read() = True ‘如果有记录,就循环打印符合条件的记录的address字段中内容
Debug.Print(dr.Item(“id”) & ” ” & dr.Item(“sex”) & ” ” & dr.Item(“Address”))
Loop
Else
MessageBox.Show(“搜索失败”)
End If
dr.Close()
‘结果为
‘2 男 红山区

‘查询全部
cmd.CommandText = “select * from tbTest order by id”
dr = cmd.ExecuteReader()
If dr.HasRows() = True Then
Do While dr.Read() = True ‘如果有记录,就循环打印符合条件的记录的address字段中内容
Debug.Print(dr.Item(“id”) & ” ” & dr.Item(“sex”) & ” ” & dr.Item(“Address”))
Loop
End If
‘结果为
‘1 男 松山区
‘2 男 红山区

dr.Close()
cmd.Dispose()
cn.Close()
cn.Dispose()

六 总结

练习后发现,除了查询,VB.NET操作ACCESS数据库的方法与VB6使用差不多,仅仅变量类型不一样。

但是查询要麻烦些。
————————————————
版权声明:本文为CSDN博主「iamtsfw」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/iamtsfw/article/details/91819136

 

eg:

Private Sub cr()
opendb()
Dim cmdcr As New OleDbCommand()
cmdcr.Connection = cn
Dim id As Integer, name As String, zjf As Double, daf As Double, bizhi As Double, dhzf As Double, hzf As String
id = TextBox6.Text + 1
name = TextBox1.Text
zjf = Convert.ToDouble(TextBox2.Text)
daf = Convert.ToDouble(TextBox3.Text)
bizhi = Convert.ToDouble(TextBox4.Text)
dhzf = zjf + daf * bizhi ‘计算最后分数
hzf = Math.Round(dhzf, 3).ToString() ‘最后分数转化为字符串格式
TextBox5.Text = hzf
Dim strcr = “insert into summ (id,name,zjf,daf,bizhi,hzf) values(” & id & “,” & Chr(39) & name & Chr(39) & “,” & zjf & “,” & daf & “,” & bizhi & “,” & dhzf & “)”
cmdcr.CommandText = strcr
cmdcr.ExecuteNonQuery()
cmdcr.Dispose()
cn.Close()
MessageBox.Show(“插入测试完成”)
End Sub

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续整理创作!

发表评论

邮箱地址不会被公开。 必填项已用*标注