Link to home
Start Free TrialLog in
Avatar of BarepAssets
BarepAssetsFlag for Ireland

asked on

Additional information: Invalid cast from Enum to DateTime.

Error Converting to DateTime
Hi I have a sp and am trying to read a csv file which inputs the data into a table

but am getting the message, does anyone know why it is giving this error

When I test my sp with the values that it gives in the debugger all works fine

 FileOpen(1, stFile, OpenMode.Input)
            Do Until EOF(1)
                sFileString = LineInput(1)
                sp = sFileString.Split(";")
                '    With cmd

                '  cmd.Parameters("@DateReport").Value = Convert.ToDateTime(Trim(sp(0))) 'Format(Convert.ToDateTime(Trim(sp(0))), "dd MMM yy")
                cmd.Parameters("@DateReport").Value = Format(Convert.ToDateTime(Trim(sp(0))), "dd MMM yy")
                cmd.Parameters("@IDChorus").Value = Convert.ToInt32(sp(1))
                cmd.Parameters("@FundName").Value = sp(2)
                cmd.Parameters("@Country").Value = sp(3)
                cmd.Parameters("@Type").Value = sp(4)
                cmd.Parameters("@IDOpe").Value = sp(6)
                cmd.Parameters("@Denomination").Value = sp(7)
                cmd.Parameters("@Quantity").Value = Convert.ToDouble(sp(10))
                If (Trim(sp(9))) = "" Then
                    cmd.Parameters("@ToDate").Value = vbNull
                Else
                    '                    cmd.Parameters("@ToDate").Value = Convert.ToDateTime(Trim(sp(9))) 'Format(Convert.ToDateTime(Trim(sp(9))), "dd MMM yy")
                    cmd.Parameters("@ToDate").Value = Format(Convert.ToDateTime(Trim(sp(9))), "dd MMM yy")
                End If

                cmd.Parameters("@Nominal").Value = Convert.ToDouble(sp(12))
                '  cmd.Parameters("@FromDate").Value = Convert.ToDateTime(Trim(sp(15))) 'Format(Convert.ToDateTime(Trim(sp(15))), "dd MMM yy")
                cmd.Parameters("@FromDate").Value = Format(Convert.ToDateTime(Trim(sp(15))), "dd MMM yy")
                cmd.Parameters("@CCY").Value = sp(16)
                cmd.Parameters("@InterestAccrued").Value = Convert.ToDouble(sp(19))
                cmd.Parameters("@Valuation").Value = Convert.ToDouble(sp(17))
                cmd.Parameters("@PercentageNav").Value = Convert.ToDouble(sp(20))

                cmd.ExecuteNonQuery()
                nLineCount = nLineCount + 1
            Loop
            FileClose(1)
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

what line is causing the error?

AW
Avatar of BarepAssets

ASKER

The date line
which 'date line' there are several which utilize Convert.ToDateTime()

AW
the date that comes from the file is like this:

20060630;

How do I transfer it to 30 june 2006
you will need to parse the value:

        Dim field As String = "20060630"
        Dim testDate As Date = DateSerial(CInt(field.Substring(0, 4)), CInt(field.Substring(4, 2)), CInt(field.Substring(6, 2)))


where Field is the name of the variable that holds the test "20060630"

you could do this in a Function:

Private Function ConvertStringtoDate(DateString as String) as Date
you will need to parse the value:

        Dim field As String = "20060630"
        Dim testDate As Date = DateSerial(CInt(field.Substring(0, 4)), CInt(field.Substring(4, 2)), CInt(field.Substring(6, 2)))


where Field is the name of the variable that holds the test "20060630"

you could do this in a Function:

Private Function StringToDate(DateString as String) as Date
    If DateString.Length = 8 then
         Return DateSerial(CInt(DateString.Substring(0, 4)), CInt(DateString.Substring(4, 2)), CInt(DateString.Substring(6, 2)))
    End If
End Function

AW
ASKER CERTIFIED SOLUTION
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
How do I call this within my code

I create a new function as above but how do I pass in the values

Do Until EOF(1)
                sFileString = LineInput(1)
                sp = sFileString.Split(";")
               
                'cmd.Parameters("@DateReport").Value = Convert.ToDateTime(sp(0)) 'Format(Convert.ToDateTime(Trim(sp(0))), "dd MMM yy")
                'cmd.Parameters("@DateReport").Value = DateTime.Parse(sp(0))
                cmd.Parameters("@DateReport").Value = Format(Convert.ToDateTime(sp(0)), "M/dd/yyyy")
                'cmd.Parameters("@DateReport").Value = Convert.ToDateTime(Trim(sp(0))) 'Format(Convert.ToDateTime(Trim(sp(0))), "dd MMM yy")
                'cmd.Parameters("@DateReport").Value = Format(Convert.ToDateTime(Trim(sp(0))), "dd MMM yy")
               
                cmd.Parameters("@Quantity").Value = Convert.ToDouble(sp(12))
                If (Trim(sp(9))) = "" Then
                    cmd.Parameters("@ToDate").Value = "01/01/1900"
                Else
                    cmd.Parameters("@ToDate").Value = Convert.ToDateTime(Trim(sp(11))) 'Format(Convert.ToDateTime(Trim(sp(9))), "dd MMM yy")
                    'cmd.Parameters("@ToDate").Value = Format(Convert.ToDateTime(Trim(sp(9))), "dd MMM yy")
                End If
worked great thanks
glad to be of assistance.

AW