Dim myAdapter As OleDbDataAdapter = New OleDbDataAdapter
Dim myTable As DataTable = New DataTable
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim cn As OleDbConnection = New OleDbConnection( _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Jet OLEDB:Engine Type=6;Data Source=myNew1.accdb ")
Dim strSelect As String = "select * from 客户"
Dim sqlUpdata As String = _
"UPDATA 客户" & _
"set 序号=@序号" & _
"姓名=@姓名" & _
"单位=@单位"
Dim sqlInsert As String = "INSERT INTO 客户 (序号,姓名,单位) value (@序号,@姓名,@单位)"
Dim sqlDelete As String = "DELETE FROM 客户 WHERE 序号=@序号"
myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
' 设置适配器 SelectCommand 属性
myAdapter.SelectCommand = New OleDbCommand(strSelect, cn)
'设定数据适配器的 UpdateCommand 属性
myAdapter.UpdateCommand = New OleDbCommand(sqlUpdata, cn)
' 设定 UPDATE 表达式的各个参数
myAdapter.UpdateCommand.Parameters.Add("@序号", OleDbType.VarChar, 12, "序号")
myAdapter.UpdateCommand.Parameters.Add("@姓名", OleDbType.VarChar, 12, "姓名")
myAdapter.UpdateCommand.Parameters.Add("@单位", OleDbType.VarChar, 20, "单位")
'设定数据适配器的 InsertCommand 属性
myAdapter.InsertCommand = New OleDbCommand(sqlInsert, cn)
' 设定 INSERT 表达式的各个参数
myAdapter.InsertCommand.Parameters.Add("@序号", OleDbType.VarChar, 12, "序号")
myAdapter.InsertCommand.Parameters.Add("@姓名", OleDbType.VarChar, 12, "姓名")
myAdapter.InsertCommand.Parameters.Add("@单位", OleDbType.VarChar, 20, "单位")
'设定数据适配器的 DeleteCommand 属性
myAdapter.DeleteCommand = New OleDbCommand(sqlDelete, cn)
' 设定 DELETE 表达式的各个参数
Dim delParameter As OleDbParameter = myAdapter.DeleteCommand.Parameters.Add("@序号", OleDbType.VarChar, 12, "序号")
delParameter.SourceVersion = DataRowVersion.Original
Try
cn.Open()
myAdapter.Fill(myTable)
cn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message, "注意")
End Try
'增加一些新数据
Dim newRow As DataRow
For i As Integer = 1 To 10
newRow = myTable.NewRow
newRow(0) = 30 + i
newRow(1) = "李明"
newRow(2) = "山东电力"
myTable.Rows.Add(newRow)
Next
myDataGrid.CaptionText = myTable.TableName '显示表名
myDataGrid.SetDataBinding(myTable, "") '显示数据
End Sub
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
If myTable.HasErrors Then
MessageBox.Show("数据表有错误,不能更新", "请注意", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
Try
Dim number As Integer = myAdapter.Update(myTable)
myTable.AcceptChanges()
MessageBox.Show("更新笔数:" & number, "提示", MessageBoxButtons.OK)
Catch ex As Exception
MessageBox.Show(ex.ToString, "注意")
End Try
End If
End Sub