无法将数据从 CSV 插入 SQL 表 [英] Cannot insert Data from CSV into SQL table

问题描述

我有以下代码将数据从 CSV 文件导入到 sql 表.当我运行代码时，没有显示错误.但是没有在 SQL 表中插入任何内容.

I have the following code to import data from a CSV file to a sql table. When I run the code no error are displayed. However nothing is inserted into the SQL table.

    Dim folder = "C:\Users\Administrator\Desktop\SKEDULERING\Data\"
    Dim CnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & folder & ";Extended Properties=""text;HDR=No;FMT=Delimited"";"
    Dim dt As New DataTable

    Using Adp As New OleDbDataAdapter("select * from [labanal2.csv]", CnStr)

        Adp.Fill(dt)
    End Using


    Dim strsql As String = "insert into ontledings (PLAASNO,PLAASNAAM,BLOKNO,AREA,NAME3,KULTIVAR,WINGKLAS,ANALISEDATUM,SUIKER,pH,SUUR) values (@PLAASNO,@PLAASNAAM,@BLOKNO,@AREA,@NAME3,@KULTIVAR,@WINGKLAS,@ANALISEDATUM,@SUIKER,@pH,@SUUR)"

    Dim SqlconnectionString As String = "Data Source=GIDEON-E-LAPTOP\SQLEXPRESS2014;Initial Catalog=SkeduleringDatabasis;Integrated Security=True"

    Using connection As New SqlClient.SqlConnection(SqlconnectionString)

        Dim cmd As New SqlClient.SqlCommand(strsql, connection)

        With cmd.Parameters



            .Add("@PLAASNO", SqlDbType.VarChar, 50, "PLAASNO")
            .Add("@PLAASNAAM", SqlDbType.VarChar, 50, "PLAASNAAM")
            .Add("@BLOKNO", SqlDbType.VarChar, 50, "BLOKNO")
            .Add("@AREA", SqlDbType.VarChar, 50, "AREA")
            .Add("@ANALISEDATUM", SqlDbType.Date, 50, "ANALISEDATUM")
            .Add("@NAME3", SqlDbType.VarChar, 50, "NAME3")
            .Add("@KULTIVAR", SqlDbType.VarChar, 50, "KULTIVAR")
            .Add("@WINGKLAS", SqlDbType.VarChar, 50, "WINGKLAS")
            .Add("@SUIKER", SqlDbType.Decimal, 50, "SUIKER")
            .Add("@pH", SqlDbType.Decimal, 50, "pH")
            .Add("@SUUR", SqlDbType.Decimal, 50, "SUUR")


        End With

        Dim adapter As New SqlClient.SqlDataAdapter()
        adapter.InsertCommand = cmd
        Dim iRowsInserted As Int32 = adapter.Update(dt)

        MsgBox("Klaar OLEB")
    End Using

我错过了什么?

推荐答案

该问题是由 DataTable 中的每一行都具有 RowState = DataRowState.Unchanged 引起的.这意味着当您调用 Update 时，每一行都会被简单地忽略.

The problem is caused by the fact that each row in your DataTable has the RowState = DataRowState.Unchanged. This means that each row is simply ignored when you call Update.

这是 Update 方法所期望的正常行为，插入、更新或删除命令仅考虑处于已添加、已更改或已删除状态的行.

This is the normal behavior expected by the Update method, only the rows in state Added, Changed or Deleted are taken in consideration for the Insert, Update or Delete command.

您可以在调用 update 之前添加此循环

You can add this loop before calling update

For Each row As DataRow In dt.Rows
    row.SetAdded()
Next

或者，如下面的 Plutonix 所述，更改 Fill 方法的行为，将属性 AcceptChangesDuringFill 设置为 False

Or, as explained below by Plutonix, change the behavior of the Fill method setting the property AcceptChangesDuringFill to False

Using Adp As New OleDbDataAdapter("select * from [labanal2.csv]", CnStr)
    Adp.AcceptChangesDuringFill = False
    Adp.Fill(dt)
End Using

这将使带有 DataRowState.Added 标志的 RowState 属性保持活动状态.

This will leave the RowState property with the DataRowState.Added flag active.

这篇关于无法将数据从 CSV 插入 SQL 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！