如何使用VB.NET将数据从数据表插入MS访问数据库？ [英] How do I insert data from my datatable to an MS access DB using VB.NET?

问题描述

我正在尝试使用VB.Net将我的数据表插入到MS Access数据库中。



我已为此目的编写了代码，但这并没有更新访问表。



我正在逐步学习vb.net应用程序。我也在这里提到mt代码，那么请你指引我正确的方向来解决它。

I am trying to insert my data table into an MS Access Database using VB.Net.

I have written the code for this purpose but this doesnot update the Access Table.

I am learning my way around vb.net application step by step. I also mentioning mt code here,So could you please walk me to the right direction to fix it.

Quote：

顺便说一下，数据表和Access数据库表都有相同的表结构。

By the way both the datatable and Access Database table have same table structure.

我有什么试过：

What I have tried:

Private Sub btnFinish_Click(sender As Object, e As EventArgs) Handles BbtnFinish.Click

    ImportedData.Columns.RemoveAt(0)
    ImportedData.Columns.RemoveAt(31)

    Dim ConnString As String = "D:\Works\VB.Net Projects\Shipping Helper\Project\Shipping Helper\Shipping Helper\bin\Data\ShipmentDB.accdb"
    Dim DBDataSet As DataSet = New DataSet

    Dim SQLInsertCmd As New OleDb.OleDbCommand
    Dim SQLDelCmd As New OleDb.OleDbCommand

    Using DBCon As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ConnString)

        SQLInsertCmd.Connection = DBCon
        SQLDelCmd.Connection = DBCon
        DBCon.Open()

        Dim DBAdpt As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
        SQLDelCmd.CommandText = "Delete * From AmazonData"
        SQLInsertCmd.CommandText = "INSERT INTO AmazonData ( [order-id] ) SELECT [order-id] FROM ImportedData"

        SQLDelCmd.ExecuteNonQuery()
        SQLInsertCmd.ExecuteNonQuery()

    End Using

    MessageBox.Show("Check files")
    Me.Close()
End Sub

推荐答案

SQLInsertCmd.CommandText = "INSERT INTO AmazonData ( [order-id] ) SELECT [order-id] FROM ImportedData"

以上命令直接传递给Access控制器，它无法从数据表中读取，因为它是一个mem ory对象。您需要使用正确的参数化查询来构建命令，使用数据表的元素填充每个参数。您还需要检查命令的返回值，看它们是否成功。

The above command is passed direct to the Access controller, which has no way of reading from your data table, since it is a memory object. You need to use proper parameterised queries to build your commands, filling each parameter with the elements of your data table(s). You also need to check the return values of your commands to see whether they succeeded or not.

这篇关于如何使用VB.NET将数据从数据表插入MS访问数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！