参数化查询出现VB.NET错误

Question

我使用以下代码将数据从CSV文件导入到SQL数据库表中：

Dim folder = "C:\Users\Administrator\Desktop\SKEDULERING\EZY Wine 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 skedp_lb.csv", CnStr)
   Adp.AcceptChangesDuringFill = False
   Adp.Fill(dt)
End Using



    Dim konneksie As New SqlConnection
    Dim opdragdelete, opdragreseed, opdragSitTipeIn As New SqlCommand
    konneksie.ConnectionString = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Administrator\Desktop\SKEDULERING\Skedulering6\Skedulering6\SkeduleringsDatabasis6.mdf;Integrated Security=True"
    konneksie.Open()

    Dim strsql As String = "insert into ontledings_vyfjaar_histories (PLAASNO,BLOKNO,DATUM,SUIKER,SUUR,pH) values (@PLAASNO,@BLOKNO,@DATUM,@SUIKER,@pH,@SUUR)"

    Dim SqlconnectionString As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Administrator\Desktop\SKEDULERING\Skedulering6\Skedulering6\SkeduleringsDatabasis6.mdf;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, "F1")
            .Add("@NAME1", SqlDbType.VarChar, 50, "F2")
            .Add("@BLOKNO", SqlDbType.VarChar, 50, "F3")
            .Add("@NAME2", SqlDbType.VarChar, 50, "F4")
            .Add("@ANALISEDATUM", SqlDbType.VarChar, 50, "F7")
            .Add("@NAME3", SqlDbType.VarChar, 50, "F8")
            .Add("@DATUM", SqlDbType.Date, 50, "F9")
            .Add("@SUIKER", SqlDbType.Decimal, 50, "F10")
            .Add("@pH", SqlDbType.Decimal, 50, "F11")
            .Add("@SUUR", SqlDbType.Decimal, 50, "F12")

        End With

        Dim adapter As New SqlClient.SqlDataAdapter()

        adapter.InsertCommand = cmd

        Dim iRowsInserted As Int32 = adapter.Update(dt)

    End Using
    konneksie.Close()

然而，我在底部的第三行得到了以下错误代码，即adapter.Update(dt)：附加信息:参数化查询'(@PLAASNO varchar(50)，@NAME1 varchar(50)，@BLOKNO varchar(50)，@NA‘期望参数'@pH'，但没有提供。

我的CSV示例(希望粘贴正确)：

plaasno,name1,blokno,name2,analisedat,name3,datum,suiker,ph,suur
"03002","BLOMVELD-SE-VLEI","1","VREDENDAL","20120307","","07/03/12",20.60,3.16,8.35
"03002","BLOMVELD-SE-VLEI","1","VREDENDAL","20130225","","25/02/13",20.60,3.08,9.47
"03002","BLOMVELD-SE-VLEI","10","VREDENDAL","20120321","","21/03/12",22.80,3.28,8.17
"03002","BLOMVELD-SE-VLEI","10","VREDENDAL","20130225","","25/02/13",20.60,3.09,10.24
"03002","BLOMVELD-SE-VLEI","11","VREDENDAL","20120131","","31/01/12",19.00,3.16,8.32
"03002","BLOMVELD-SE-VLEI","11","VREDENDAL","20120227","","27/02/12",22.40,3.46,6.01
"03002","BLOMVELD-SE-VLEI","11","VREDENDAL","20120308","","08/03/12",22.80,3.39,6.67
"03002","BLOMVELD-SE-VLEI","11","VREDENDAL","20120314","","14/03/12",23.20,3.39,6.41
"03002","BLOMVELD-SE-VLEI","11","VREDENDAL","20130215","","15/02/13",21.50,3.24,8.60
"03002","BLOMVELD-SE-VLEI","2","VREDENDAL","20120201","","01/02/12",16.80,2.93,12.45
"03002","BLOMVELD-SE-VLEI","2","VREDENDAL","20120220","","20/02/12",21.00,3.24,7.13
"03002","BLOMVELD-SE-VLEI","2","VREDENDAL","20130215","","15/02/13",21.20,3.08,9.31
"03002","BLOMVELD-SE-VLEI","4","VREDENDAL","20120131","","31/01/12",19.20,3.06,9.18
"03002","BLOMVELD-SE-VLEI","4","VREDENDAL","20120207","","07/02/12",22.80,3.20,7.62
"03002","BLOMVELD-SE-VLEI","4","VREDENDAL","20130208","","08/02/13",21.60,3.02,7.48
"03002","BLOMVELD-SE-VLEI","5","VREDENDAL","20120131","","31/01/12",18.80,3.10,9.54
"03002","BLOMVELD-SE-VLEI","5","VREDENDAL","20120201","","01/02/12",19.20,3.08,9.12
"03002","BLOMVELD-SE-VLEI","5","VREDENDAL","20120207","","07/02/12",21.60,3.23,7.53
"03002","BLOMVELD-SE-VLEI","5","VREDENDAL","20130208","","08/02/13",20.00,2.96,8.00
"03002","BLOMVELD-SE-VLEI","6","VREDENDAL","20120321","","21/03/12",21.00,3.14,8.11
"03002","BLOMVELD-SE-VLEI","6","VREDENDAL","20130225","","25/02/13",20.00,2.94,8.74
"03002","BLOMVELD-SE-VLEI","7","VREDENDAL","20120321","","21/03/12",20.00,3.07,8.52
"03002","BLOMVELD-SE-VLEI","7","VREDENDAL","20130225","","25/02/13",21.00,2.94,8.59
"03002","BLOMVELD-SE-VLEI","8","VREDENDAL","20120321","","21/03/12",21.80,3.26,8.42
"03002","BLOMVELD-SE-VLEI","8","VREDENDAL","20120326","","26/03/12",22.00,3.34,8.16
"03002","BLOMVELD-SE-VLEI","8","VREDENDAL","20130225","","25/02/13",20.20,3.10,9.63
"03002","BLOMVELD-SE-VLEI","9","VREDENDAL","20120321","","21/03/12",22.30,3.50,4.98
"03002","BLOMVELD-SE-VLEI","9","VREDENDAL","20130225","","25/02/13",19.60,3.32,5.23
"03566","PERSEEL 101","UIT-22","VREDENDAL","20120323","","23/03/12",24.00,3.66,7.29
"03785","KOEKENAAP PER 420 & 797 & 798","1","VREDENDAL","20120131","","31/01/12",18.60,3.11,9.09
"03785","KOEKENAAP PER 420 & 797 & 798","1","VREDENDAL","20130221","","21/02/13",23.00,3.21,7.71
"03785","KOEKENAAP PER 420 & 797 & 798","2","VREDENDAL","20120131","","31/01/12",19.20,3.12,9.40
"03785","KOEKENAAP PER 420 & 797 & 798","2","VREDENDAL","20120207","","07/02/12",16.40,3.09,11.63
"03785","KOEKENAAP PER 420 & 797 & 798","2","VREDENDAL","20120307","","07/03/12",19.40,3.34,7.15
"03785","KOEKENAAP PER 420 & 797 & 798","2","VREDENDAL","20120326","","26/03/12",20.00,3.48,6.41
"03785","KOEKENAAP PER 420 & 797 & 798","2","VREDENDAL","20120402","","02/04/12",21.0

我哪里错了？

问候

Answer 1

您添加的参数比查询所需的多。

您应该只添加与您在查询中定义的一样多的参数。不多，也不少。最好以您在查询中指定的相同顺序添加参数值，因为某些DB引擎希望它们是这样的。

我注意到您已经交换了最后两个参数的位置。

您键入：

Dim strsql As String = "insert into ontledings_vyfjaar_histories
           (PLAASNO ,BLOKNO,  DATUM, SUIKER, SUUR, pH) 
    values (@PLAASNO,@BLOKNO,@DATUM,@SUIKER,@pH,  @SUUR)"

请注意，SUUR和pH应该按一定的顺序排列，但参数名称是互换的。

您应该重新键入它：

Dim strsql As String = "insert into ontledings_vyfjaar_histories
           (PLAASNO ,BLOKNO,  DATUM, SUIKER, SUUR, pH) 
    values (@PLAASNO,@BLOKNO,@DATUM,@SUIKER,@SUUR,@pH)"

因为您的insert语句只需要(PLAASNO,BLOKNO,DATUM,SUIKER,SUUR,pH)，所以没有必要添加NAME1,NAME2,NAME3,...等参数。

With cmd.Parameters
     .Add("@PLAASNO", SqlDbType.VarChar, 50, "F1")
     '.Add("@NAME1", SqlDbType.VarChar, 50, "F2")         'Remove this line
     .Add("@BLOKNO", SqlDbType.VarChar, 50, "F3")
     '.Add("@NAME2", SqlDbType.VarChar, 50, "F4")         'Remove this
     '.Add("@ANALISEDATUM", SqlDbType.VarChar, 50, "F7")  'Remove
     '.Add("@NAME3", SqlDbType.VarChar, 50, "F8")         'Remove
     .Add("@DATUM", SqlDbType.Date, 50, "F9")
     .Add("@SUIKER", SqlDbType.Decimal, 50, "F10")
     .Add("@SUUR", SqlDbType.Decimal, 50, "F12")          'SUUR before pH
     .Add("@pH", SqlDbType.Decimal, 50, "F11")            'pH is last
End With