vb.net数据库操作

Question

我已经将数据库中表people的内容，用adapter1 fill 到dataset1中了，想对dataset1中的数据进行修改和删除操作该怎么弄啊
people中的字段有name和age

Answer 1

参考一下下面这段代码就可以了。

Imports System.Data
'引入数据库操作类命名空间
Imports System.Data.OleDb
'引入ADO.NET操作命名空间
Public Class FrmModifystInfo
Inherits System.Windows.Forms.Form
Public ADOcmd As OleDbDataAdapter
Public ds As DataSet = New DataSet()
'建立DataSet对象
Public mytable As Data.DataTable
'建立表单对象
Public myrow As Data.DataRow
'建立数据行对象
Public rownumber As Integer
'定义一个整型变量来存放当前行数
Public SearchSQL As String
Public cmd As OleDbCommandBuilder
'======================================================
#Region " Windows 窗体设计器生成的代码 "

#End Region
'======================================================
Private Sub FrmModifystInfo_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
'窗体的载入
TxtSID.Enabled = False
TxtName.Enabled = False
ComboSex.Enabled = False
TxtBornDate.Enabled = False
TxtClassno.Enabled = False
TxtRuDate.Enabled = False
TxtTel.Enabled = False
TxtAddress.Enabled = False
TxtComment.Enabled = False '设置信息为只读
Dim tablename As String = "student_Info "
SearchSQL = "select * from student_Info "
ExecuteSQL(SearchSQL, tablename) '打开数据库
ShowData() '显示记录
End Sub

Private Sub ShowData()
'在窗口中的textbox中显示数据
myrow = mytable.Rows.Item(rownumber)
TxtSID.Text = myrow.Item(0).ToString
TxtName.Text = myrow.Item(1).ToString
ComboSex.Text = myrow.Item(2).ToString
TxtBornDate.Text = Format(myrow.Item(3), "yyyy-MM-dd ")
TxtClassno.Text = myrow.Item(4).ToString
TxtTel.Text = myrow.Item(5).ToString
TxtRuDate.Text = Format(CDate(myrow.Item(6)), "yyyy-MM-dd ")
TxtAddress.Text = myrow.Item(7).ToString
TxtComment.Text = myrow.Item(8).ToString
End Sub

Private Sub BtFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtFirst.Click
'指向第一条数据
rownumber = 0
ShowData()
End Sub

Private Sub BtPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtPrev.Click
'指向上一条数据
BtNext.Enabled = True
rownumber = rownumber - 1
If rownumber < 0 Then
rownumber = 0 '如果到达记录的首部，行号设为零
BtPrev.Enabled = False
End If
ShowData()
End Sub

Private Sub BtNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtNext.Click
'指向上一条数据
BtPrev.Enabled = True
rownumber = rownumber + 1
If rownumber > mytable.Rows.Count - 1 Then
rownumber = mytable.Rows.Count - 1 '判断是否到达最后一条数据
BtNext.Enabled = False
End If
ShowData()
End Sub

Private Sub BtLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtLast.Click
'指向最后一条数据
rownumber = mytable.Rows.Count - 1
ShowData()
End Sub

Private Sub BtDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtDelete.Click
mytable.Rows.Item(rownumber).Delete() '删除记录
If MsgBox( "确定要删除改记录吗？ ", MsgBoxStyle.OKCancel + vbExclamation, "警告 ") = MsgBoxResult.OK Then
cmd = New OleDbCommandBuilder(ADOcmd)
'使用自动生成的SQL语句
ADOcmd.Update(ds, "student_Info ")
BtNext.PerformClick()
End If
End Sub

Private Sub BtModify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtModify.Click
TxtSID.Enabled = False '关键字段只读
TxtName.Enabled = True '可读写
ComboSex.Enabled = True
TxtBornDate.Enabled = True
TxtClassno.Enabled = True
TxtRuDate.Enabled = True
TxtTel.Enabled = True
TxtAddress.Enabled = True
TxtComment.Enabled = True
End Sub

Private Sub BtUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtUpdate.Click
If Not Testtxt(TxtName.Text) Then
MsgBox( "请输入姓名！ ", vbOKOnly + vbExclamation, "警告 ")
TxtName.Focus()
Exit Sub
End If
If Not Testtxt(ComboSex.Text) Then
MsgBox( "请选择性别！ ", vbOKOnly + vbExclamation, "警告 ")
ComboSex.Focus()
Exit Sub
End If
If Not Testtxt(TxtClassno.Text) Then
MsgBox( "请选择班号！ ", vbOKOnly + vbExclamation, "警告 ")
TxtClassno.Focus()
Exit Sub
End If

If Not Testtxt(TxtTel.Text) Then
MsgBox( "请输入联系电话！ ", vbOKOnly + vbExclamation, "警告 ")
TxtTel.Focus()
Exit Sub
End If
If Not Testtxt(TxtAddress.Text) Then
MsgBox( "请输入家庭住址！ ", vbOKOnly + vbExclamation, "警告 ")
TxtAddress.Focus()
Exit Sub
End If
If Not IsNumeric(Trim(TxtSID.Text)) Then
MsgBox( "请输入数字学号！ ", vbOKOnly + vbExclamation, "警告 ")
Exit Sub
TxtSID.Focus()
End If
If Not IsDate(TxtBornDate.Text) Then
MsgBox( "出生时间应输入日期格式（yyyy-mm-dd）！ ", vbOKOnly + vbExclamation, "警告 ")
Exit Sub
TxtBornDate.Focus()
End If
If Not IsDate(TxtRuDate.Text) Then
MsgBox( "入校时间应输入日期格式（yyyy-mm-dd）！ ", vbOKOnly + vbExclamation, "警告 ")
TxtRuDate.Focus()
Exit Sub
End If
myrow.Item(0) = Trim(TxtSID.Text)
myrow.Item(1) = Trim(TxtName.Text)
myrow.Item(2) = Trim(ComboSex.Text)
myrow.Item(3) = Trim(TxtBornDate.Text)
myrow.Item(4) = Trim(TxtClassno.Text)
myrow.Item(5) = Trim(TxtTel.Text)
myrow.Item(6) = Trim(TxtRuDate.Text)
myrow.Item(7) = Trim(TxtAddress.Text)
myrow.Item(8) = Trim(TxtComment.Text)
mytable.GetChanges()
cmd = New OleDbCommandBuilder(ADOcmd)
'使用自动生成的SQL语句
ADOcmd.Update(ds, "student_Info ")
'对数据库进行更新
MsgBox( "修改学籍信息成功！ ", vbOKOnly + vbExclamation, "警告 ")
TxtName.Enabled = False
ComboSex.Enabled = False
TxtBornDate.Enabled = False
TxtClassno.Enabled = False
TxtRuDate.Enabled = False
TxtTel.Enabled = False
TxtAddress.Enabled = False
TxtComment.Enabled = False '重新设置信息为只读
End Sub

Private Sub BtCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtCancel.Click
TxtSID.Enabled = False
TxtName.Enabled = False
ComboSex.Enabled = False
TxtBornDate.Enabled = False
TxtClassno.Enabled = False
TxtRuDate.Enabled = False
TxtTel.Enabled = False
TxtAddress.Enabled = False
TxtComment.Enabled = False
End Sub

Public Function ExecuteSQL(ByVal SQL As String, ByVal table As String)
Try
'建立ADODataSetCommand对象
'数据库查询函数
ADOcmd = New OleDbDataAdapter(SQL, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\student.mdb ")
'建立ADODataSetCommand对象
ADOcmd.Fill(ds, table) '取得表单
mytable = ds.Tables.Item(0) '取得名为table的表
rownumber = 0 '设置为第一行
myrow = mytable.Rows.Item(rownumber)
'取得第一行数据
Catch
MsgBox(Err.Description)
End Try
End Function
End Class

Answer 2

VB.net 操作Access数据库的一些常用方法，代码如下
Imports System
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Collections.Generic
Imports System.Text
Imports System.Data
Imports System.Configuration.ConfigurationSettings
Imports System.Data.OleDb

Public Class DB

Private Shared com As OleDb.OleDbCommand
Private Shared reader As OleDb.OleDbDataReader
Private Shared adapter As OleDb.OleDbDataAdapter
Private Shared conn As OleDb.OleDbConnection

''' <summary>
''' 获取数据库连接
''' </summary>
''' <value></value>
''' <returns></returns>
''' <remarks></remarks>
Public Shared ReadOnly Property NewConn() As OleDbConnection
Get
Dim connectionString As String
'connectionString = System.Configuration.ConfigurationSettings.GetConfig("Supermarket")
'连接2010数据库
'connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\GCU.accdb"
'连接03-07数据库
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\JL\GCU.MDB"

'应该在这里先判断conn是否为Nothing
If conn Is Nothing Then
conn = New OleDb.OleDbConnection(connectionString)
End If
If conn.State <> ConnectionState.Open Then
conn.Open()
End If
Return conn
End Get
End Property

''' <summary>
''' 执行增删改(无参)
''' </summary>
''' <param name="sql">执行的Sql语句</param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function ExecuteNonQuery(ByVal sql As String) As Integer
com = New OleDb.OleDbCommand(sql, NewConn())
Return com.ExecuteNonQuery()
End Function

''' <summary>
''' 执行增删改(有参)
''' </summary>
''' <param name="sql"></param>
''' <param name="para"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function ExecuteNonQuery(ByVal sql As String, ByVal para As OleDbParameter()) As Integer
com = New OleDb.OleDbCommand(sql, NewConn)
com.Parameters.AddRange(para)
Return com.ExecuteNonQuery()
End Function

''' <summary>
''' 执行增删改的存储过程
''' </summary>
''' <param name="para"></param>
''' <param name="ProcedureName"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function ExecuteNonQuery(ByVal para As SqlParameter(), ByVal ProcedureName As String) As Integer
Dim cmd As OleDb.OleDbCommand
cmd = New OleDb.OleDbCommand()
cmd.Connection = NewConn()
cmd.CommandText = ProcedureName
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddRange(para)
Return com.ExecuteNonQuery
End Function

''' <summary>
''' 执行查询(返回一个结果集，无参)
''' </summary>
''' <param name="sql"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function GetScalar(ByVal sql As String) As String
Dim dataset As DataSet
dataset = New DataSet()
com = New OleDb.OleDbCommand(sql, NewConn)
adapter = New OleDbDataAdapter(com)
adapter.Fill(dataset)
If dataset.Tables.Count > 0 And dataset.Tables(0).Rows.Count > 0 Then
Return dataset.Tables(0).Rows(0)(0).ToString()
End If
Return "Null"
End Function

''' <summary>
''' 执行查询(返回一个结果集，有参)
''' </summary>
''' <param name="sql"></param>
''' <param name="para"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function GetScalar(ByVal sql As String, ByVal para As SqlParameter()) As Integer
com = New OleDb.OleDbCommand(sql, NewConn)
com.Parameters.AddRange(para)
Return Convert.ToInt32(com.ExecuteScalar())
End Function

''' <summary>
''' 执行查询(返回一行数据,无参)
''' </summary>
''' <param name="sql"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function GetReader(ByVal sql As String) As OleDbDataReader
com = New OleDb.OleDbCommand(sql, NewConn)
reader = com.ExecuteReader()
Return reader
End Function

''' <summary>
''' 执行查询(返回一行数据,有参)
''' </summary>
''' <param name="sql"></param>
''' <param name="para"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function GetReader(ByVal sql As String, ByVal para As SqlParameter()) As OleDbDataReader
com = New OleDb.OleDbCommand(sql, NewConn)
com.Parameters.AddRange(para)
reader = com.ExecuteReader()
Return reader
End Function

''' <summary>
''' 执行查询(返回一个数据集,无参)
''' </summary>
''' <param name="sql"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function GetDataSet(ByVal sql As String) As DataTable
Dim dataset As DataSet
dataset = New DataSet()
com = New OleDb.OleDbCommand(sql, NewConn)
adapter = New OleDbDataAdapter(com)
adapter.Fill(dataset)
Return dataset.Tables(0)
End Function

''' <summary>
''' 执行查询(返回一个数据集,有参)
''' </summary>
''' <param name="sql"></param>
''' <param name="para"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function GetDataSet(ByVal sql As String, ByVal para As SqlParameter()) As DataTable
Dim dataset As DataSet
dataset = New DataSet()
com = New OleDbCommand(sql, NewConn)
com.Parameters.AddRange(para)
adapter = New OleDbDataAdapter(com)
adapter.Fill(dataset)
Return dataset.Tables(0)
End Function
End Class