CODEWITHSUNDEEP
sqlvb.net
Gideon
Gideon

Reputation: 313

VB.Net parse string as date

I have looked but cannot find a solution for my problem. I am importing data from a CSV file with the following code:

Using connection As New SqlClient.SqlConnection(SqlconnectionString)
    Dim cmd As New SqlClient.SqlCommand(strsql, connection)
    With cmd.Parameters
        .Add("@PLAASNOMMER", SqlDbType.VarChar, 50, "F1")
        .Add("@PLAASNAAM", SqlDbType.VarChar, 50, "F2")
        .Add("@BLOKNOMMER", SqlDbType.VarChar, 50, "F3")
        .Add("@AREA", SqlDbType.VarChar, 50, "F4")
        .Add("@KULT", SqlDbType.VarChar, 50, "F7")
        .Add("@WINGERDKLAS", SqlDbType.VarChar, 50, "F8")

        .Add("@ANALISEDATUM", SqlDbType.Date).Value = Date.ParseExact("F9", "dd/MM/yyyy", DBNull.Value)

        .Add("@SUIKERWAARDE", SqlDbType.Decimal, 50, "F10")
        .Add("@PHWAARDE", SqlDbType.Decimal, 50, "F11")
        .Add("@SUURWAARDE", SqlDbType.Decimal, 50, "F12")
    End With

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

I have "empty" dates in the CSV file as "/ / ". I am using the code (line 10) :

.Add("@ANALISEDATUM", SqlDbType.Date).Value = Date.ParseExact("F9", "dd/MM/yyyy", DBNull.Value)

However I am getting an error

Unable to cast object of type 'System.DBNull' to type 'System.IFormatProvider'.

What am I missing?

My CSV date looks like this:

plaasno,name1,blokno,name2,analise_dt,name3,kult,wingklas,datum,suiker,ph,suur,deurryppnt,geur
"11332","MONOTONKA","1-MON","KLEINPLASIE","20090115","","PIN","KULT","  /  /",0.00,0.00,0.00,0.00,0.00
"11332","MONOTONKA","10-MON","KLEINPLASIE","20090115","","COL","BULK","  /  /",0.00,0.00,0.00,0.00,0.00
"11332","MONOTONKA","10-MON","VREDENDAL","20180228","","COL","BULK","28/02/18",23.50,3.08,10.42,0.00,0.00
"11332","MONOTONKA","12-MON","KLEINPLASIE","20090115","","CHB","BULK","  /  /",0.00,0.00,0.00,0.00,0.00
"11332","MONOTONKA","12-MON","VREDENDAL","20180214","","CHB","BULK","14/02/18",18.70,3.09,11.78,0.00,0.00
"11332","MONOTONKA","13-MON","KLEINPLASIE","20090115","","CHB","BULK","  /  /",0.00,0.00,0.00,0.00,0.00
"11332","MONOTONKA","13-MON","VREDENDAL","20180214","","CHB","BULK","14/02/18",18.30,3.03,13.28,0.00,0.00

Upvotes: 1

Views: 182

Answers (2)

Mary
Mary

Reputation: 15081

.TryParseExact has a few more parameters. If had to put .Add in twice because I could not assign a DBNull.Value to a Date variable. 

    Dim d As Date
    Dim F9 As String = "String that must match your format parameter"
    'Your command and parameter code
    If Date.TryParseExact(F9, "yyyy-mm-dd", System.Globalization.CultureInfo.InvariantCulture, DateTimeStyles.None, d) Then
        .Add("@ANALISEDATUM", SqlDbType.Date).Value = d
    Else
        .Add("@ANALISEDATUM", SqlDbType.Date).Value = DBNull.Value
    End If

Upvotes: 0

Esko
Esko

Reputation: 4207

The error itself is pretty explanatory, you are passing System.DBNull to a parameter which expects to be passed iFormatProvider for Date.ParseExact-method here: 

Date.ParseExact("F9", "dd/MM/yyyy", DBNull.Value)

Not sure why are you trying to do this, perhaps you want to pass null if the value cannot be parsed? If that is the case you could do if-clause with TryParseExact and if it returns false, add DBNull.Value as a value for the paremeter.

Also as @AndrewMorton pointed out, you are passing a literal string "F9" (and all the other fields also) to the function and not the value from the csv. Your code is incomplete. Anyway as per your question, I have explained why you get the error you described.

Upvotes: 1

Related Questions