Update 影响0个记录!(请教各高手)
数据库:Access XP
Imports System.Data.oledb
Private Sub GetdaMasterUpdateCommand(ByRef DA As System.Data.oledb.oledbDataAdapter)
Dim cmdInsert As New oledbCommand
Dim cmdUpdate As New oledbCommand
Dim cmdDelete As New oledbCommand
'-------------下面将定义用于在数据源插入(Insert)表的命令
cmdInsert.CommandText = "INSERT into SaleOrders (SaleOrderID,CustomerID,CDate,Amounts,Units,TotalMoney,Remarks) " & _
"Values (@p1,@p2,@p3,@p4,@p5,@p6,@p7) "
cmdInsert.Parameters.Add("@p1", OleDbType.VarChar, 50, "SaleOrderID")
cmdInsert.Parameters.Add("@p2", OleDbType.VarChar, 50, "CustomerID")
cmdInsert.Parameters.Add("@p3", OleDbType.DBDate, 8, "CDate")
cmdInsert.Parameters.Add("@p4", OleDbType.Double, 8, "Amounts")
cmdInsert.Parameters.Add("@p5", OleDbType.Double, 8, "Units")
cmdInsert.Parameters.Add("@p6", OleDbType.Double, 8, "TotalMoney")
cmdInsert.Parameters.Add("@p7", OleDbType.VarChar, 200, "Remarks")
cmdInsert.Connection = cn
daOrder.InsertCommand = cmdInsert
'-----------下面将定义用于在数据源更新(update)表的命令
cmdUpdate.CommandText = "UPDATE SaleOrders SET CustomerID=@p2," & _
"CDate=@p3, Amounts=@p4, Units=@p5," & _
"TotalMoney=@p6,Remarks=@p7 " & _
"WHERE SaleOrderID=@p1 "
cmdUpdate.Parameters.Add("@p1", OleDbType.VarChar, 50, "SaleOrderID")
cmdUpdate.Parameters.Add("@p2", OleDbType.VarChar, 50, "CustomerID")
cmdUpdate.Parameters.Add("@p3", OleDbType.DBDate, 8, "CDate")
cmdUpdate.Parameters.Add("@p4", OleDbType.Double, 8, "Amounts")
cmdUpdate.Parameters.Add("@p5", OleDbType.Double, 8, "Units")
cmdUpdate.Parameters.Add("@p6", OleDbType.Double, 8, "TotalMoney")
cmdUpdate.Parameters.Add("@p7", OleDbType.VarChar, 200, "Remarks")
cmdUpdate.Connection = cn
daOrder.UpdateCommand = cmdUpdate
'------------------下面将定义用于在数据源删除(Delete)表的命令
cmdDelete.CommandText = "Delete * from SaleOrders WHERE SaleOrderID= @p1 "
cmdDelete.Parameters.Add("@p1", OleDbType.VarChar, 50, "SaleOrderID")
cmdDelete.Connection = cn
daOrder.DeleteCommand = cmdDelete
End Sub
Public Function getConnection() As Boolean
Dim cnStr As String
Try
If cn.State = ConnectionState.Open = True Then cn.Close()
Dim str As String = "Data Source=" & Application.StartupPath & "\CustomerManager.dat"
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Jet OLEDB:Database Password=accp;" & str
cn.Open()
Return True
Catch ex As OleDbException
MsgBox(ex.Message, MsgBoxStyle.Critical, "Message")
Return False
End Try
End Function
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
If getConnection() Then
stroledb = " SELECT SaleOrders.SaleOrderID, SaleOrders.CustomerID, SaleOrders.CDate, " & _
" SaleOrders.Amounts, SaleOrders.Units, SaleOrders.TotalMoney, " & _
" SaleOrders.Remarks, Customers.ContactName,Customers.CompanyName " & _
" FROM SaleOrders INNER JOIN " & _
" Customers ON SaleOrders.CustomerID = Customers.CustomerID"
daOrder = New oledbDataAdapter(stroledb, cn)
daOrder.MissingSchemaAction = MissingSchemaAction.AddWithKey
daOrder.Fill(ds, "Master")
'--------调用生成适配器的添加,修改,删除命令
GetdaMasterUpdateCommand(daOrder)
dvOrder = ds.Tables("Master").DefaultView
End If
daOrder.Update(dvOrder.Table)
End Sub
上面在SQLServer 通过,但是在Access 不行