VB.NET访问ACCESS数据库的增加，修改，删除等部分过程

用到数据控件DataSet，OleDbConnection，OleDbDataAdapter

'组合搜索

Private Sub btnOk_Click(ByVal sender As System.Object, ByVal eAs System.EventArgs) Handles BtnOK.Click

           Dim str As String
           Dim type1 As String

           str = "select * from telecode where 1=1 "
           If Me.txName.Text <> "" Then
               str = str & " and name like '%" &Me.txName.Text & "%'"
           End If
           If Me.txDept.Text <> "" Then
               str = str & " and dept like '%" &Me.txDept.Text & "%'"
           End If
           If Me.txAddr.Text <> "" Then
               str = str & " and address like '%"& Me.txAddr.Text & "%'"
           End If
           If Me.txTele.Text <> "" Then
               str = str & " and (telephone like '%"& Me.txTele.Text & "%' or mobilelike '%" & Me.txTele.Text &"%')"
           End If
           If Me.cbxType.SelectedItem <> ""Then
               type1 = Me.cbxType.SelectedIndex + 1

               str = str & " and type = '" & type1& "'"
           End If

           OleDbConnection1.Open()
           Console.Write(str)

           OleDbDataAdapter1.SelectCommand.CommandText = str
           OleDbDataAdapter1.SelectCommand.ExecuteNonQuery()
           DataSet1.Clear()
           OleDbDataAdapter1.Fill(DataSet1)
           OleDbConnection1.Close()

           ' DataView1.Table = DataSet1.Tables(0)
           '        showpostion()
           'ComboBox1.DataSource = objds.Tables(0)
           'ComboBox1.DisplayMember =objds.Tables(0).Columns(1).ToString
           'Label1.Text = objds.Tables(0).Columns(1).ColumnName

           Dim ds As DataSet
           ds = DataSet1
           If Not ds Is Nothing Then
               Me.DataGrid1.PreferredColumnWidth = Me.DataGrid1.Width /ds.Tables(0).Columns.Count
               Me.DataGrid1.DataSource = ds.Tables(0)
           Else
               MsgBox("没有符合要求的记录!", MsgBoxStyle.OKOnly, "提示")
               Me.DataGrid1.DataSource = Nothing
           End If
           '           LabAcc.Text = ds.Tables(0).Columns.Count
       End Sub

'增加记录

       Private Sub BtAdd_Click(ByVal sender As System.Object, ByVal e AsSystem.EventArgs) Handles BtAdd.Click
           If txName.Text = "" Or (txTele.Text = "" And txMobile.Text = "")Then
               MsgBox("请填充完成数据", MsgBoxStyle.Information, "提示")
               txTele.Focus()
               Exit Sub
           Else

               Dim sex1 As Integer
               Dim str, type1 As String
               If RadioBt1.Checked Then
                   sex1 = 0
               Else
                   sex1 = -1
               End If
               If Me.cbxType.SelectedItem <> ""Then
                   type1 = Me.cbxType.SelectedIndex + 1
               End If
               Str = "insert into telecode (id,name,sex,dept,address,telephone,mobile,tnote,type ) values ( "& _
                        txId.Text & ", '" & txName.Text& "', " & sex1 & ",'" & _
                        txDept.Text & "', '" & txAddr.Text& "', '" & txTele.Text& "', '" & _
                        txMobile.Text & "', '" &txNote.Text & "', '" & type1& "' )"

               OleDbConnection1.Open()
               Console.Write(Str)

               OleDbDataAdapter1.InsertCommand.CommandText = str
               OleDbDataAdapter1.InsertCommand.ExecuteNonQuery()
               DataSet1.Clear()
               OleDbDataAdapter1.Fill(DataSet1)
               OleDbConnection1.Close()

               MsgBox("添加成功")

               Dim ds As DataSet
               ds = DataSet1
               If Not ds Is Nothing Then
                   Me.DataGrid1.PreferredColumnWidth = Me.DataGrid1.Width /ds.Tables(0).Columns.Count
                   Me.DataGrid1.DataSource = ds.Tables(0)
               Else
                   MsgBox("没有符合要求的记录!", MsgBoxStyle.OKOnly, "提示")
                   Me.DataGrid1.DataSource = Nothing
               End If

           End If
       End Sub

'增加记录另一过程

       Private Sub BtADD1_Click(ByVal sender As System.Object, ByVal e AsSystem.EventArgs) Handles BtADD1.Click
           'Close()
           If txName.Text = "" Or (txTele.Text = "" And txMobile.Text = "")Then
               MsgBox("请填充完成数据", MsgBoxStyle.Information, "提示")
               txTele.Focus()
               Exit Sub
           Else
               Dim str As String = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=e:\lytelephone\iteleDB.mdb"
               Dim conn As OleDbConnection
               conn = New OleDbConnection(str)
               conn.Open()
               Dim strsql As String
               strsql = "select * from telecode where name='" &Trim$(txName.Text) & "'"
               Dim cmd As New OleDbCommand(strsql, conn)
               Dim dr As OleDbDataReader
               dr = cmd.ExecuteReader
               If (dr.Read) Then
                   MsgBox("已经有相同的名字，请修改")
                   txTele.Focus()
                   Exit Sub
               Else
                   dr.Close()
                   Dim strInsert As String
                   Dim sex1 As Integer
                   Dim type1 As String
                   If RadioBt1.Checked Then
                       sex1 = 0
                   Else
                       sex1 = -1
                   End If
                   If Me.cbxType.SelectedItem <> ""Then
                       type1 = Me.cbxType.SelectedIndex + 1
                   End If
                   strInsert = "insert into telecode (id,name,sex,dept,address,telephone,mobile,tnote,type ) values ( "& _
                            txId.Text & ", '" & txName.Text& "', " & sex1 & ",'" & _
                            txDept.Text & "', '" & txAddr.Text& "', '" & txTele.Text& "', '" & _
                            txMobile.Text & "', '" &txNote.Text & "', '" & type1& "' )"

                   Dim cmdInsert As OleDbCommand = New OleDbCommand(strInsert,conn)
                   cmdInsert.ExecuteNonQuery()                '这里有问题
                   MsgBox("添加成功")
                   strsql = "select * from telecode"
                   Dim cmdView As New OleDbCommand(strsql, conn)
                   cmdView.ExecuteNonQuery()
                   Dim da As New OleDbDataAdapter
                   da = New OleDbDataAdapter(strsql, conn)
                   Dim ds As New DataSet
                   da.Fill(ds)
                   DataGrid1.DataSource = ds.Tables(0)
               End If

           End If
       End Sub

       Private Sub BtDel_Click(ByVal sender As System.Object, ByVal e AsSystem.EventArgs) Handles BtDel.Click
           Dim str As String
           Dim type1 As String

           str = "1=1"
           If Me.txName.Text <> "" Then
               str = str & " and name = '" &Me.txName.Text & "'"
           End If
           If Me.txDept.Text <> "" Then
               str = str & " and dept = '" &Me.txDept.Text & "'"
           End If
           If Me.txAddr.Text <> "" Then
               str = str & " and address = '" &Me.txAddr.Text & "'"
           End If
           If Me.txTele.Text <> "" Then
               str = str & " and (telephone = '" &Me.txTele.Text & "' or mobile = '"& Me.txTele.Text & "')"
           End If
           If Me.cbxType.SelectedItem <> ""Then
               type1 = Me.cbxType.SelectedIndex + 1
               str = str & " and type = '" & type1& "'"
           End If
           If str = "1=1" Then
               Exit Sub
           End If
           str = "delete * from telecode where " & str
           OleDbConnection1.Open()
           Console.Write(str)

           OleDbDataAdapter1.DeleteCommand.CommandText = str
           OleDbDataAdapter1.DeleteCommand.ExecuteNonQuery()
           DataSet1.Clear()
           OleDbDataAdapter1.Fill(DataSet1)
           OleDbConnection1.Close()

       End Sub

'修改

       Private Sub BtModi_Click(ByVal sender As System.Object, ByVal e AsSystem.EventArgs) Handles BtModi.Click
           If txName.Text = "" Or txTele.Text = "" Then
               MsgBox("请填充完成数据", MsgBoxStyle.Information, "提示")
               txName.Focus()
               Exit Sub
           Else

               Dim str As String = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=e:\lytelephone\iteleDB.mdb"
               Dim conn As OleDbConnection
               conn = New OleDbConnection(str)
               conn.Open()
               Dim strsql As String
               strsql = "select * from telecode where name='" &Trim$(txName.Text) & "'"
               Dim cmd As New OleDbCommand(strsql, conn)
               Dim dr As OleDbDataReader
               dr = cmd.ExecuteReader
               If (dr.Read) Then
                   Dim r As MsgBoxResult
                   r = MsgBox("确定要修改此数据吗?", MsgBoxStyle.YesNo, "提示")
                   Select Case r
                       Case vbYes
                           dr.Close()
                           Dim strUpdate As String
                           Dim sex1 As Integer
                           Dim type1 As String
                           If RadioBt1.Checked Then
                               sex1 = 0 '注意access中boolean属性字段0(false)||-1(True)，不然使用sql语句会出错
                           Else
                               sex1 = -1
                           End If
                           If Me.cbxType.SelectedItem <> ""Then
                               type1 = Me.cbxType.SelectedIndex + 1
                           End If
                           strUpdate = "update telecode set telephone='" +Trim$(Me.txTele.Text) + "'" & _
                              ", mobile = '" + Trim$(Me.txMobile.Text) + "'" &_
                              ", address = '" + Trim$(Me.txAddr.Text) + "'" &_
                              ", tnote = '" + Trim$(Me.txNote.Text) + "'" &_
                              ", type = '" + type1 + "'" & _
                              " where name='" + Trim$(Me.txName.Text) + "'"
                           Dim cmdUpdate As OleDbCommand = New OleDbCommand(strUpdate,conn)
                           cmdUpdate.ExecuteNonQuery()        '老是这里提示错误 不明白之前要用 dr.Close()
     
                           MsgBox("修改成功", , "提示")
                           strsql = "select * from telecode"
                           Dim cmdView As New OleDbCommand(strsql, conn)
                           cmdView.ExecuteNonQuery()
                           Dim da As New OleDbDataAdapter
                           da = New OleDbDataAdapter(strsql, conn)
                           Dim ds As New DataSet
                           da.Fill(ds)
                           DataGrid1.DataSource = ds.Tables(0)
                       Case vbNo
                           Exit Sub
                   End Select
               Else
                   MsgBox("没有找到此数据,请重新输入用户名", , "提示")
                   txName.Focus()
                   Exit Sub
               End If
           End If

       End Sub

'刷新表

       Private Sub tabpage1_click(ByVal sender As System.Object, ByVal eAs System.EventArgs) Handles TabPage1.Click

           txName.Focus()

           Dim strsql As String = "select * from telecode"
           Dim str As String = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=e:\lytelephone\iteleDB.mdb"
           Dim conn As OleDbConnection
           conn = New OleDbConnection(str)
           conn.Open()
           Dim cmdView As New OleDbCommand(strsql, conn)
           cmdView.ExecuteNonQuery()
           Dim da As New OleDbDataAdapter
           da = New OleDbDataAdapter(strsql, conn)
           Dim ds As New DataSet
           da.Fill(ds)
           DataGrid1.DataSource = ds.Tables(0)
       End Sub