問題描述

我正在使用下面的代碼使用 DataTable

I'm using the code below to insert or update a MySql table using values from a DataTable

Function DB_Multi_Ins_Or_Upd_From_DT(ByVal dt As DataTable, ByVal DestTbl$) As Integer
    Using SQLConnection As New MySqlConnection(CnStr) 
        Using sqlCommand As New MySqlCommand()
            Dim CmdTxt$ = "INSERT INTO " & DestTbl & " ("
            For Each Col As DataColumn In dt.Columns
                CmdTxt &= Col.ColumnName & ", "
            Next
            CmdTxt = CmdTxt.Substring(0, CmdTxt.Length - 2) & ") VALUES ("
            For x As Integer = 0 To dt.Rows.Count - 1
                For xx As Integer = 0 To dt.Columns.Count - 1
                    CmdTxt &= "@" & "R" & x.ToString & "C" & xx.ToString & ", "
                    sqlCommand.Parameters.AddWithValue _
                        ("@" & "R" & x.ToString & "C" & xx.ToString, dt.Rows(x)(xx))
                Next xx
                CmdTxt = CmdTxt.Substring(0, CmdTxt.Length - 2) & "), ("
            Next x
            CmdTxt = CmdTxt.Substring(0, CmdTxt.Length - 3)
            CmdTxt &= " ON DUPLICATE KEY UPDATE "
            For Each Col As DataColumn In dt.Columns
                CmdTxt &= Col.ColumnName & "=VALUES(" & Col.ColumnName & "), "
            Next
            CmdTxt = CmdTxt.Substring(0, CmdTxt.Length - 2)
            With sqlCommand
                .CommandText = CmdTxt
                .Connection = SQLConnection
                .CommandType = CommandType.Text
            End With
            Try
                SQLConnection.Open()
                Dim AffRows% = sqlCommand.ExecuteNonQuery()
                Return AffRows
            Catch ex As MySqlException
                Return -1
            Finally
                SQLConnection.Close()
            End Try
        End Using
    End Using
End Function

碰巧代碼更新了 1 行但返回了 2.

It happens that the code updates 1 row but returns 2.

似乎僅在 update 上返回錯誤結(jié)果，而在 insert 上返回正確數(shù)量的受影響行

It seems that the wrong result is returned only on update while on insert is returned the right number of affected rows

怎么了?如何修復并獲取受影響行的正確數(shù)量?

What's wrong? How can I fix and get rigth number of affected rows?

要測試我的代碼，它需要:

To test my code it needs:

1) 導入 MySql.Data.MySqlClient

1) Imports MySql.Data.MySqlClient

2) 給 cnstr 賦值，比如"datasource=" + Server_Name + ";username=" + UserDB + ";password="+ 密碼 + ";database=" + Database_Name + ""

2) give a value to cnstr like "datasource=" + Server_Name + ";username= " + UserDB + ";password=" + Password + ";database=" + Database_Name + ""

3) 創(chuàng)建一個 DataTable，其列名類似于數(shù)據(jù)庫表的字段

3) create a DataTable who has column names like fields of a DB table

4) 調(diào)用傳遞DataTable和表名的函數(shù)在數(shù)據(jù)庫上

4) call the function passing the DataTable and the name of the table on the DB

推薦答案

使用 ON DUPLICATE KEY UPDATE，每行的受影響行值為 1，如果該行作為新行插入，如果更新現(xiàn)有行，則為 2，并且0 如果現(xiàn)有行設置為其當前值

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values

dev.mysql.com

這篇關于用于插入/更新 MySql DB 的函數(shù)返回錯誤的“受影響的行"；數(shù)字的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！