我希望 oledb 中的 SQL 查詢命令（插入）沒有重復，有沒有最好的建議？
謝謝杰克
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
connectionString = cn
con = New OleDbConnection(connectionString)
con.Open()
Dim sql As String = "INSERT INTO EXAMPLE2 SELECT * FROM EXAMPLE1"
cmd = New OleDbCommand(sql, con)
cmd.ExecuteNonQuery()
MessageBox.Show("Successfully Updated...", "Update")
con.Close()
Me.fillDataGridView1()
Me.fillDataGridView2()
Catch myerror As OleDbException
MessageBox.Show("Error: " & myerror.Message)
Finally
End Try
End Sub
uj5u.com熱心網友回復：
要僅插入EXAMPLE1 中不存在于EXAMPLE2 中的行，您需要比較一行中不想重復的每一列的值。
INSERT INTO EXAMPLE2 SELECT * FROM EXAMPLE1 EX1
WHERE NOT EXISTS
(SELECT * FROM EXAMPLE2 EX2 WHERE
EX2.CODE = EX1.CODE AND
EX2.NOD = EX1.NOD AND
EX2.QTY = EX1.QTY AND
EX2.PRICE = EX1.PRICE AND
EX2.REMARK = EX1.REMARK
)
uj5u.com熱心網友回復：
我根據以下鏈接獲得了答案解決方案（避免在 SQL Server 中的 INSERT INTO SELECT 查詢中重復）
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
connectionString = cn
con = New OleDbConnection(connectionString)
con.Open()
Dim sql As String = "INSERT INTO EXAMPLE2 (CODE,NOD,QTY,PRICE) SELECT t1.CODE, t1.NOD,t1.QTY, t1.PRICE FROM EXAMPLE1 t1 WHERE NOT EXISTS(SELECT CODE FROM EXAMPLE2 t2 WHERE t2.CODE = t1.CODE)"
cmd = New OleDbCommand(sql, con)
cmd.ExecuteNonQuery()
MessageBox.Show("Successfully Updated...", "Update")
con.Close()
Me.fillDataGridView1()
Me.fillDataGridView2()
Catch myerror As OleDbException
MessageBox.Show("Error: " & myerror.Message)
Finally
End Try
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/461161.html
標籤：sql VB.net sql-插入 数据库 oledb 命令
上一篇：解決方案指數超出范圍。必須是非負數且小于vb.net中集合的大小
下一篇：如何使用Eval處理空值