Parameterized query issue
I have the following code to import data from a csv into a sql table :
Code:
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
Code:
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
Re: Parameterized query issue
Your data only has 10 cols... but your mapping referenced cold 11 & 12 (F11 & F12)
-tg
Re: Parameterized query issue
Hi,
also you define -No Header- and a semicolon
Code:
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
Re: Parameterized query issue
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
Re: Parameterized query issue
Hi Tg,
Quote:
Originally Posted by
techgnome
if there's only 10 columns of data, you can't map to 11. Or 12.
-tg
that will be the problem
regards
Chris
