用到數(shù)據(jù)控件DataSet，OleDbConnection，OleDbDataAdapter

'組合搜索

Private Sub btnOk_Click(ByVal sender As System.Object, ByVal e As 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 mobile like '%" & 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("沒(méi)有符合要求的記錄!", 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 As System.EventArgs) Handles BtAdd.Click
            If txName.Text = "" Or (txTele.Text = "" And txMobile.Text = "") Then
                MsgBox("請(qǐng)?zhí)畛渫瓿蓴?shù)據(jù)", 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("沒(méi)有符合要求的記錄!", MsgBoxStyle.OKOnly, "提示")
                    Me.DataGrid1.DataSource = Nothing
                End If

            End If
        End Sub

'增加記錄另一過(guò)程

        Private Sub BtADD1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtADD1.Click
            'Close()
            If txName.Text = "" Or (txTele.Text = "" And txMobile.Text = "") Then
                MsgBox("請(qǐng)?zhí)畛渫瓿蓴?shù)據(jù)", MsgBoxStyle.Information, "提示")
                txTele.Focus()
                Exit Sub
            Else
                Dim str As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=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("已經(jīng)有相同的名字，請(qǐng)修改")
                    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()                 '這里有問(wèn)題
                    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 As System.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 As System.EventArgs) Handles BtModi.Click
            If txName.Text = "" Or txTele.Text = "" Then
                MsgBox("請(qǐng)?zhí)畛渫瓿蓴?shù)據(jù)", MsgBoxStyle.Information, "提示")
                txName.Focus()
                Exit Sub
            Else

                Dim str As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=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("確定要修改此數(shù)據(jù)嗎?", 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語(yǔ)句會(huì)出錯(cuò)
                            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()         '老是這里提示錯(cuò)誤 不明白之前要用 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("沒(méi)有找到此數(shù)據(jù),請(qǐng)重新輸入用戶名", , "提示")
                    txName.Focus()
                    Exit Sub
                End If
            End If

        End Sub

'刷新表

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

            txName.Focus()

            Dim strsql As String = "select * from telecode"
            Dim str As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=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