Parameterized query issue

[GideonE]

I have the following code to import data from a csv into a sql table :

 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,pH,SUUR) 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("@BLOKNO", SqlDbType.VarChar, 50, "F3")
                .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()

However I get the following error : Additional information: The parameterized query '(@PLAASNO varchar(50),@BLOKNO varchar(50),@DATUM date,@SUIKER de' expects the parameter '@pH', which was not supplied.


I cannot seem to solve the issue. Any help would be much appreciated.

A sample of my CSV file :

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

My SQL database table has the following structure :

          CREATE TABLE [dbo].[Ontledings_Vyfjaar_Histories] (
    [Ontledings_VyfjaarID] INT          IDENTITY (1, 1) NOT NULL,
    [Plaasno]              VARCHAR (50) NULL,
    [Blokno]               VARCHAR (50) NULL,
    [Datum]                DATE         NULL,
    [suiker]               REAL         NULL,
    [suur]                 REAL         NULL,
    [ph]                   REAL         NULL,
    [Tipe]                 CHAR (1)     NULL,
    [Alias]                VARCHAR (50) NULL,
    [Aliasnaam]            VARCHAR (50) NULL,
    [Plaasnaam]            VARCHAR (50) NULL
);

Regards

[techgnome]

Your data only has 10 cols... but your mapping referenced cold 11 & 12 (F11 & F12)

-tg

[ChrisE]

Hi,

also you define -No Header- and a semicolon

Dim CnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & folder & ";Extended Properties=""text;HDR=No;FMT=Delimited"";"

but it looks like you have a Header and a ;

regards
Chris

[techgnome]

Chris - you mean the trailing ; following the FMT=Delimited? That's allowed... it's extraneous, but allowable... it notes the end of the field, just like all the ones before. But you're right about the header, I didn't notice that. But header, no header, if there's only 10 columns of data, you can't map to 11. Or 12.

-tg

[ChrisE]

Hi Tg,

if there's only 10 columns of data, you can't map to 11. Or 12.
-tg

that will be the problem

regards
Chris