|
vb.net和 sql server2000程序： Imports System.Data.SqlClient ‘引用类 Public Class Form1 Inherits System.Windows.Forms.Form Dim sqlconn As New SqlClient.SqlConnection("Server=localhost;DataBase=student;integrated security=true;") ’定义sqlconn为SqlConnection类一个连接 Dim adapter As SqlDataAdapter ‘定义数据适配器 Dim ds As DataSet ’定义数据集 ‘窗体代码略 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Try ’select语句中一定要包含主键字段，否则无法更新；在表stuinf中，字段stuno是主键 adapter = New SqlDataAdapter("select stuno'学号',stuname'姓名' from stuinf", sqlconn) ds = New DataSet adapter.Fill(ds, "student") '往适配器中添加数据集，表名为student DataGrid1.SetDataBinding(ds, "student") '把datagrid控件绑定数据集 Catch sqlExc As Exception '异常处理 MessageBox.Show(sqlExc.ToString, "SQL 异常错误!", _ MessageBoxButtons.OK, MessageBoxIcon.Error) Exit Sub End Try End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Try Dim cBuilder As New SqlCommandBuilder(adapter) '创建一个CommandBuilder对象 adapter.UpdateCommand = cBuilder.GetUpdateCommand '为数据适配器adapter的更新命令赋值 adapter.Update(ds, "student") '更新数据集 ds.Clear() '清空当前数据集 adapter.Fill(ds, "student") '重新绑定数据适配器 DataGrid1.SetDataBinding(ds, "student") '在datagrid中显示更新后数据 MsgBox("更新数据成功！", MsgBoxStyle.OKOnly, "请确认") '提示更新数据成功 Catch sqlExc As Exception '异常处理 MessageBox.Show(sqlExc.ToString, "SQL 异常错误!", _ MessageBoxButtons.OK, MessageBoxIcon.Error) Exit Sub End Try End Sub End Class 1 填充的数据集中的数据表一定要包含原表(要更新的表)的主键，即select语句中一定要包含主键字段，否则无法更新； 2 commandbuilder类只能在datatable映射到单个数据表的时候使用。这意味着如果数据库查询的是两个或者更多的表的联接，就不能使用commandbuilder类； 3 在本例子中，字段stuno'学号'是主键，假如用户更改学号，导致有两个一样的学号时，异常处理会提示“不能插入重复值”，这时更新失败。 以下是另一个Access和vB.net Imports System.Data.OleDb
Public Class Form1
Dim adapter As OleDbDataAdapter
Dim ds As New DataSet()
Dim myconn As OleDbConnection
Dim OLEB As OleDbCommandBuilder
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim constr As String = "provider=microsoft.jet.oledb.4.0;data source=" & Application.StartupPath & "\test.mdb"
Dim sqlstr As String = "select * from man"
Dim myconn As New OleDbConnection(constr)
Dim mycomm As New OleDbCommand(sqlstr, myconn)
myconn.Open()
adapter = New OleDbDataAdapter(sqlstr, myconn)
adapter.Fill(ds, "man")
DataGridView1.DataSource = ds.Tables("man")
OLEB = New OleDbCommandBuilder(adapter)
'获取或设置指定其名称包含空格或保留标记等字符的数据库对象（例如，表或列）时使用的开始字符
OLEB.QuotePrefix = "["
OLEB.QuoteSuffix = "]"
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
adapter.Update(ds, "man")
End Sub
End Class
|