SqlDataAdapter.Update 不更新 SQL 表 [英] SqlDataAdapter.Update does not update SQL table

问题描述

我在获取一些 VB.NET 代码来更新我的 MS SQL 表时遇到了一些麻烦.

I'm having some trouble getting some VB.NET code to update my MS SQL table.

在实际程序中，DataTable 作为 xml 序列化的 DataTable 从文件中读取，但我已将问题代码简化为以下内容:

In the real program the DataTable is read from a file as an xml-serialized DataTable, but I have reduced the problem code down to the following:

Imports System.Data.SqlClient

Module Module1

    Sub Main()
        Dim myConn As New SqlConnection("<myconnstring>")

        Dim myDA As New SqlDataAdapter("SELECT * FROM TestTable WHERE 1=2", myConn)

        Dim myTable As New DataTable
        myDA.Fill(myTable)

        Dim myRow As DataRow

        myRow = myTable.NewRow
        myRow("IntField") = 4
        myRow("TextField") = "loik"
        myTable.Rows.Add(myRow)
        myRow.AcceptChanges()
        myRow.SetAdded()

        myRow = myTable.NewRow
        myRow("IntField") = 3
        myRow("TextField") = "poiuy"
        myTable.Rows.Add(myRow)
        myRow.AcceptChanges()
        myRow.SetModified()

        Dim myCB As New SqlCommandBuilder(myDA)
        ' My changes should overwrite whatever is already in the database
        myCB.ConflictOption = ConflictOption.OverwriteChanges

        Debug.Print(myCB.GetInsertCommand.CommandText)
        Debug.Print(myCB.GetUpdateCommand.CommandText)
        Debug.Print(myDA.Update(myTable))

    End Sub

End Module

最初 TestTable 看起来像这样(IntField 是表上的主键):

Initially TestTable looks like this (IntField is the Primary Key on the table):

IntField    TextField
---------------------
1           qwert
2           asdfg
3           zxcvb

在我运行程序后 TestTable 看起来像这样:

After I run the program TestTable looks like this:

IntField    TextField
---------------------
1           qwert
2           asdfg
3           zxcvb
4           loik

正确添加了新记录 (IntField = 4)，但更新的记录 (IntField = 3) 不包含 TextField 中的新值.

The new record (IntField = 4) is added correctly, BUT the updated record (IntField = 3) does not contain the new value in the TextField.

程序的输出(3 条调试行)是:

The output from the program (the 3 debug lines) is:

INSERT INTO [TestTable] ([IntField], [TextField]) VALUES (@p1, @p2)
UPDATE [TestTable] SET [IntField] = @p1, [TextField] = @p2 WHERE (([IntField] = @p3))
2

我还在 SQL Server 上创建了一个跟踪(使用 Profiler)，这是唯一执行的语句:

I have also created a trace on the SQL Server (using the Profiler), and this is the only statements executed:

SELECT * FROM TestTable WHERE 1=2
SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;SELECT * FROM TestTable WHERE 1=2 SET FMTONLY OFF; SET NO_BROWSETABLE OFF;
exec sp_executesql N'INSERT INTO [TestTable] ([IntField], [TextField]) VALUES (@p1, @p2)',N'@p1 int,@p2 nvarchar(4)',@p1=4,@p2=N'loik'

所以似乎代码出于某种原因决定不更新 SQL 表中的更新记录，而不会引发错误，这让我很困惑.

So it seems that the code for some reason decides NOT to update the updated record in the SQL table, without raising an error, and this confuses me a lot.

推荐答案

如果您要更新一行，我认为您需要检索该行然后更新它.假设intField"是独一无二的，你可以这样做.这是 C#，但它应该给你思路.

If you're going to update a row I would think you need to retrieve that row and then update it. Assuming "intField" is unique you could do this. This is C#, but it should give you the idea.

//find intField row    
DataRow[] Rows = MydataTable.Select("IntField=3");

Rows[0]["TextField"] = "poiuy";
MydataTable.AcceptChanges();

这篇关于SqlDataAdapter.Update 不更新 SQL 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！