VB.Net parse string as date

Question

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

"F1" is the literal string "F1". It is not the first field of the CSV file. You need to do some more programming the get the fields. — Andrew Morton
– Andrew Morton, Commented Aug 24, 2018 at 12:31

Esko · Accepted Answer · 2018-08-24 12:34:54Z

1

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.

edited Aug 24, 2018 at 12:34

answered Aug 24, 2018 at 12:25

Esko

4,2172 gold badges26 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gideon Over a year ago

My problems seems to be the csv file. No problem with one, error in another. To me the two files seem the same.

Mary · Accepted Answer · 2018-08-25 00:02:45Z

0

.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

edited Aug 25, 2018 at 0:02

answered Aug 24, 2018 at 23:40

Mary

15.1k3 gold badges20 silver badges27 bronze badges

Collectives™ on Stack Overflow

VB.Net parse string as date

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related