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)

mandText = 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)

mandText = 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)

mandText = 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

如果觉得《VB.NET访问ACCESS数据库的增加 修改 删除等部分过程》对你有帮助，请点赞、收藏，并留下你的观点哦！