所有的数据库连接都要使用以下语句:Imports System.Data.OleDb,该语句声明了所使用到的OleDb类。下面分类介绍了VB.NET访问ACCESS数据库的增加,修改,删除等部分过程。
1、使用ADODB来访问数据库,对数据库进行查找。下面一段代码实现的是管理员登陆功能。用到的控件有以下:ADODB.Connection,ADODB.Recordset。并且需要在引用中添加:Microsoft ActiveX Date Objects Library 6.0来支持程序的运行。
Public Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
Try
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
Dim connstr As String
connstr = "provider=microsoft.jet.oledb.4.0;data source=bookmanage.mdb"
sql = "select * from admin where 管理员姓名='" & txtname.Text & "'"
conn.Open(connstr)
rs.Open(sql, conn, 1, 1)
If rs.RecordCount = 0 Then
MsgBox("用户不存在")
txtname.Text = ""
txtPassword.Text = ""
txtname.Select()
rs.Close()
conn.Close()
Exit Sub
End If
If txtPassword.Text <> rs("登陆密码").Value Then
MsgBox("用户口令错误")
txtname.Text = ""
txtPassword.Text = ""
txtname.Select()
rs.Close()
conn.Close()
Else
MsgBox("登陆成功")
rs.Close()
conn.Close()
main.Show()
Me.Close()
End If
Catch ex As Exception
MsgBox("登陆失败,请重试!")
End Try
End Sub
上面程序中,我通过rs("登陆密码").Value来实现对仅有一条结果的Select语句结果的某个字段的值进行输出。
2、使用OleDbConnection来访问数据库。需要用到的控件有:DataSet,OleDbConnection,OleDbDataAdapter。
首先要配置数据源,我们可以采用以下的方法:
connstr = "provider=microsoft.jet.oledb.4.0;data source=bookmanage.mdb"
2.1、查找语句。下面这段代码实现的功能查找管理员帐户登陆密码。
Private Sub btnEnter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEnter.Click
Try
Dim password As String
Dim connstr As String
connstr = "provider=microsoft.jet.oledb.4.0;data source=bookmanage.mdb"
Dim myConn As OleDbConnection = New OleDbConnection()
myConn.ConnectionString = connstr
myConn.Open()
Dim strSearch As String = "select * from admin where 登陆密码='" & txtPwdOld.Text & "'"
Dim myCommand As New OleDbCommand(strSearch, myConn)
Dim dr As OleDbDataReader
dr = myCommand.ExecuteReader
If dr.Read = False Then
MsgBox("原密码错误,请重新输入!", MsgBoxStyle.Information, "系统提示")
myConn.Close()
Exit Sub
Else
password = dr("登陆密码")
End If
Catch ex As Exception
MsgBox("查找失败,请重试!")
End Try
End Sub
上述代码能实现使用Select语句,从数据库中查找出指定的结果。我们可以通过以下程序段来实现和ADODB一样的数据字段绑定。
Try
Dim dt As New DataTable()
Dim connStr As String
connstr = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=bookmanage.mdb"
Dim sqlStr As String = "select * from book where 书名 like '%" & TextBox1.Text & "%'"
Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)
dataAdapter.Fill(dt)
dataAdapter.Dispose()
If dt.Rows.Count = 0 Then
MessageBox.Show("找不到该书的记录!")
Else
Label1.Text = dt.Rows.Item(0).Item(2)
End If
Catch ex As Exception
MsgBox("查找失败,请重试!")
End Try
这段代码还使用到了DataTable控件,其中dt.Rows.Item(0).Item(2)的意思是,取出dt中的第0行,第2列的值。或者:也可以用以下的代码实现。
Try
Dim connstr As String
connstr = "provider=microsoft.jet.oledb.4.0;data source=bookmanage.mdb"
Dim myConn As OleDbConnection = New OleDbConnection()
myConn.ConnectionString = connstr
myConn.Open()
Dim strSearch As String = "select * from book where 书号='" & Trim$(TextBox1.Text) & "'"
Dim myCommand As New OleDbCommand(strSearch, myConn)
Dim dr As OleDbDataReader
dr = myCommand.ExecuteReader
If (dr.Read = False) Then
MsgBox("错误!", MsgBoxStyle.Information, "系统提示")
Exit Sub
Else
Label1.Text = dr("书号")
End If
Catch ex As Exception
MsgBox("查找失败,请重试!")
End Try
这里是将dr("书号")字段的值取出来。要实现这个赋值,dr("书号")的内容最多最能为1条。
2.2、更新语句。这段代码实现的功能时修改登陆密码。
Private Sub btnEnter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEnter.Click
Try
If txtPwdOld.Text = password Then
Dim connstr As String
connstr = "provider=microsoft.jet.oledb.4.0;data source=bookmanage.mdb"
Dim myConn As OleDbConnection = New OleDbConnection()
myConn.ConnectionString = connstr
myConn.Open()
Dim StrUpdate As String = "Update admin SET 登陆密码='" + txtPwd.Text + "'"
Dim cmdInsert As OleDbCommand = New OleDbCommand(StrUpdate, myConn)
cmdInsert.ExecuteNonQuery()
MsgBox("密码修改成功!")
myConn.Close()
End If
Catch ex As Exception
MsgBox("查找失败,请重试!")
End Try
End Sub
2.3、删除语句。
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Try
Dim ConnectionString As String
ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=bookmanage.mdb"
Dim myConn As OleDbConnection = New OleDbConnection()
myConn.ConnectionString = ConnectionString
myConn.Open()
Dim strDelete As String
strDelete = "delete from borrower where 借书证号 = '" & txtNum.Text & "'"
Dim cmdInsert As OleDbCommand = New OleDbCommand(strDelete, myConn)
cmdInsert.ExecuteNonQuery()
MsgBox("注销成功!")
Catch ex As Exception
MsgBox("注销失败,请重试!")
End Try
End Sub
2.4、插入语句。
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Try
Dim connstr As String
connstr = "provider=microsoft.jet.oledb.4.0;data source=bookmanage.mdb"
Dim myConn As OleDbConnection = New OleDbConnection()
myConn.ConnectionString = connstr
myConn.Open()
Dim strInsert As String = "insert into book (书号,书名,出版社,数量,作者,出版日期,分类 ) values('" + txtNum.Text + "','" + txtBookName.Text + "','" + txtPublisher.Text + "','" + txtQuality.Text + "','" + txtAuthor.Text + "','" + txtDate.Text + "','" + txtDept.Text + "')"
Dim cmdInsert As OleDbCommand = New OleDbCommand(strInsert, myConn)
cmdInsert.ExecuteNonQuery()
MsgBox("添加成功!")
Catch ex As Exception
MsgBox("添加失败")
End Try
End Sub
3、使用DataGridView来显示数据。
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Try
Dim dt As New DataTable()
Dim connStr As String
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=bookmanage.mdb"
Dim sqlStr As String
sqlStr="select borrowbook.书号,book.书名,book.出版社,book.作者,book.分类,borrowbook.借书日期 from borrowbook, book where borrowbook.借书证号 = '" & lblNum.Text & "' and borrowbook.书号 = book.书号"
Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)
dataAdapter.Fill(dt)
dataAdapter.Dispose()
If dt.Rows.Count = 0 Then
MessageBox.Show("找不到借书记录!")
Else
dgvBorrowInformation.DataSource = dt
End If
Catch ex As Exception
MsgBox("填充失败")
End Try
End Sub
2011年4月10日星期日