将数据从一个表更新到另一个表，并在新的ms访问.net时插入 [英] Update data from one table to another and insert if new ms access .net

问题描述

我有2个表"news"和"news_dev"，每个表都有一个经过批准的复选框.

i have 2 tables 'news' and 'news_dev' each has a isapproved tick-box

我试图将数据从"news_dev"复制到新闻中，但前提是它们具有刻度，并且如果其新记录被勾选，则将新记录添加到新闻"表中

i am trying to copy the data from 'news_dev' into news but only if they have a tick and also if its a new record that is ticked add the new record to the 'news' table

我的第一个方法是

Sub NewsBtn_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("databasehere") & "")
    AccessConn.Open()
    Dim AccessCommand As New System.Data.OleDb.OleDbCommand("DELETE * FROM news", AccessConn)
    AccessCommand.ExecuteNonQuery()
    Dim AccessCommand2 As New System.Data.OleDb.OleDbCommand("INSERT INTO news SELECT * FROM news_dev WHERE isapproved", AccessConn)
    AccessCommand2.ExecuteNonQuery()
    sucsessLabel.Visible = true
    sucsessLabel.Text = "News Updated"
    AccessConn.Close()
End Sub

现在这有效了，但是如果您取消选中"news_dev"中的某个项目，它将把它从新闻"中删除，这不是我想要的.

Now this worked BUT if you untick an item in 'news_dev' it removes it from 'news' which is not what i want.

因此，基本上，我试图基于记号从花药更新一个表，并且如果在一个表中而不是另一表中对记录进行了记号，则将其添加.

So basically im trying to update one table from anther based on a tick and if a record is ticked in one and not in the other table then add it.

希望所有这些都有意义，因为我的头炸了，但是任何帮助将不胜感激.

Hope all this makes sense cos my head it fried but any help would be most appreciated.

欢呼安迪

推荐答案

在我看来，您的问题源于您运行的第一个查询.您将从新闻表中删除所有记录，然后在第二个查询中，仅插入已批准的记录.
如果您有一个不是自动编号的主键字段，则可以跳过第一个查询，而将此代码用于第二个查询.本示例将在新表中插入已批准但不存在的记录.

It seems to me that your problem stems from the first query that you run. You are deleting all records from the news table and then in the second query, you are inserting only the records that are approved.
If you have a primary key field that is not an autonumber, you could skip the first query and use this code for the second query. This example inserts the records that are approved and don't exist in the new table.

"INSERT INTO news SELECT * FROM news_dev WHERE isapproved AND PRIMARY_KEY NOT IN 
(SELECT PRIMARY_KEY FROM news)"

这篇关于将数据从一个表更新到另一个表，并在新的ms访问.net时插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！