Update 影响0个记录!(请教各高手)

[liuguangwei]

数据库: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 不行

[saucer]

OleDb use positioning, try

cmdInsert.CommandText = "INSERT into SaleOrders (SaleOrderID,CustomerID,CDate,Amounts,Units,TotalMoney,Remarks) " & _
"Values (?,?,?,?,?,?,?) "

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")



cmdUpdate.CommandText = "UPDATE SaleOrders SET CustomerID=?," & _
"CDate=?, Amounts=?, Units=?," & _
"TotalMoney=?,Remarks=? " & _
"WHERE 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.Parameters.Add("@p1", OleDbType.VarChar, 50, "SaleOrderID")

[benlee]

我是自动更新的！

[yexin]

我的数据集与DATAGRID绑定，可是增加新记录无法更新回ACCESS数据库，修改某个记录也无法更新回ACCESS数据库，是不是VB.NET自身问题？？？
yexin

[kandyasp]

看看你的数据库有没有写权限，具体我也不清楚，学习中~~~

[duibudui]

daOrder.Update(dvOrder.Table)
改成：daOrder.Update(ds, "Master"),行不行？

[liuguangwei]

To duibudui(要命的一分):
daOrder.Update(dvOrder.Table)和daOrder.Update(ds, "Master")效果是一样;

To: saucer(思归):
一样的;(? or @)

To: yexin(星星之火);
知音啊;

To: benlee(寂寞成双):
怎么自动更新?

[saucer]

in OLEDB, the order of parameters are important, make sure you are adding the parameters in the correct order

[snow66]

sql2000中SQL语句变量没有顺序问题，Access的SQL语句中变量有顺序要求，改成以下顺序试一下：

'-----------下面将定义用于在数据源更新(update)表的命令
cmdUpdate.CommandText = "UPDATE SaleOrders SET CustomerID=@p2," & _
"CDate=@p3, Amounts=@p4, Units=@p5," & _
"TotalMoney=@p6,Remarks=@p7 " & _
"WHERE SaleOrderID=@p1 "

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.Parameters.Add("@p1", OleDbType.VarChar, 50, "SaleOrderID")

[ltach]

试试这个，我的可以更新Access数据库！
Try
UpdateCommand = "UPDATE " & ItableName & " SET 姓名='" & strName & "',性别 ='" & strSex & "',年龄 ='" & strAge & "',电子邮件 ='" & strEmail & "',电话 ='" & strPhone & "',家庭住址 ='" & strAdd & "',身份证号码='" & strIDCard & "',祖籍 ='" & strCityName & "' WHERE 人员ID=" & intID
OleCommand = New System.Data.OleDb.OleDbCommand(UpdateCommand, OleConnect)
OleAdapter.UpdateCommand = OleCommand
OleAdapter.UpdateCommand.ExecuteNonQuery()
Catch
System.Windows.Forms.MessageBox.Show(Microsoft.VisualBasic.Err.Description, "更新错误", MessageBoxButtons.OK, MessageBoxIcon.Warning)

End Try


End Function

[91bct]

思归老兄说的对，OleDB中参数的名称、类型、长度、顺序都有严格的要求，
只要有一点差别你的update就会出问题的，这个我确实试过的。

[jackeaaa]

To:saucer(思归)

Yes,这是不是.NET的一个BUG在SQLServer中可以通过的

[banni2003]

把这段 '-----------下面将定义用于在数据源更新(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
改为:
with cmdUpdate
.Connection = cn
.CommandText = "UPDATE SaleOrders SET CustomerID=@p2," & _
"CDate=@p3, Amounts=@p4, Units=@p5," & _
"TotalMoney=@p6,Remarks=@p7 " & _
"WHERE SaleOrderID=@p1 "
.Parameters.Add("@p1", OleDbType.VarChar, 50, "SaleOrderID")
.Parameters.Add("@p2", OleDbType.VarChar, 50, "CustomerID")
.Parameters.Add("@p3", OleDbType.DBDate, 8, "CDate")
.Parameters.Add("@p4", OleDbType.Double, 8, "Amounts")
.Parameters.Add("@p5", OleDbType.Double, 8, "Units")
.Parameters.Add("@p6", OleDbType.Double, 8, "TotalMoney")
.Parameters.Add("@p7", OleDbType.VarChar, 200, "Remarks")
daOrder.UpdateCommand = cmdUpdate
end with
是要还不行就把这句 dvOrder = ds.Tables("Master").DefaultView
daOrder.Update(dvOrder.Table)
改成:
daOrder.Update(ds,"Master")

SQLserver,ACCESS都是微软自家的产品,不可能出现不支持的情况.
看看access数据库是否有什么地方不对.比如是否设置主键,字段是否与代码匹配等等.

自动更新是可以的.用OleDbCommandBuilder就可以了 但是它能用于单表,多表的话就只能
自己动手写代码了.就像楼主写的一样.

[yexin]

我也碰到这问题，为何数据适配器无法更新（UPDATE）数据集到ACCESS数据库？