none
Failed to convert parameter value from a String to a DateTime.

    Question

  • I have a SQL stored procedure which adds a new line to a table and one field is a DateTime field.  The parameter is defined as a DateTime type in the stored procedure and in VB.NET when I add the parameter.

     

    The data for the parameter comes from a FlexCell grid which in one column allows the user to choose a date and in the next column choose a time from a drop down list of pre-determined times, eg: 06:00, 06:30, 07:00 etc.

     

    My code is as follows:

    Dim DateTimeStr as String

    DateTimeStr = Mid(GridOrders.Cell(I, 9).Text, 4, 2) + "/" + Mid(GridOrders.Cell(I, 9).Text, 1, 2) + "/" + Mid(GridOrders.Cell(I, 9).Text, 7, 4) + " " + GridOrders.Cell(I, 10).Text + ":00"

    cm.Parameters.Add("@RequestedDate", SqlDbType.DateTime).Value = CType(DateTimeStr, DateTime)

     

    I always get the error above when I try to Execute the command.  I have tried using DateTime.Parse and ConvertTo.DateTime commands but with no difference to the end result.  How can I convert the text fields in the FlexCell Grid to a SQL DateTime parameter?

     

    My DateTimeStr reformats the date from dd/MM/yyyy to MM/dd/yyyy and then adds the time onto the end eg. 07:00:00, but the conversion from this string to the DateTime doesn't work.

     

    Can anybody help as I've tried all other solutions I can find but none have worked.

     

    Thanks

     

    Wednesday, November 07, 2007 11:02 AM

Answers

  • Code Block

    Dim DateTimeStr As String = "11/07/2007 07:00:00"

    Try

      Dim dt As DateTime = CType(DateTimeStr, DateTime)

      'conversion worked

      MsgBox("Worked")

    Catch ex As Exception

      'conversion failed say why

      MsgBox(ex.ToString)

    End Try

     

     

    The snippet above works, and judging from your error message, I don't think the problem comes from casting DateTimeStr into a DateTime. Try casting it first and if there's no error then try passing it to your sp, like:

    cm.Parameters.Add("@RequestedDate", SqlDbType.DateTime).Value = dt

     

     

    hope it helps

    Wednesday, November 07, 2007 1:21 PM

All replies

  • Code Block

    Dim DateTimeStr As String = "11/07/2007 07:00:00"

    Try

      Dim dt As DateTime = CType(DateTimeStr, DateTime)

      'conversion worked

      MsgBox("Worked")

    Catch ex As Exception

      'conversion failed say why

      MsgBox(ex.ToString)

    End Try

     

     

    The snippet above works, and judging from your error message, I don't think the problem comes from casting DateTimeStr into a DateTime. Try casting it first and if there's no error then try passing it to your sp, like:

    cm.Parameters.Add("@RequestedDate", SqlDbType.DateTime).Value = dt

     

     

    hope it helps

    Wednesday, November 07, 2007 1:21 PM
  • you can use CDate(expresion) function to confert string date to DateTime type.

    Wednesday, November 07, 2007 1:36 PM
  • this wasn't helpful

    Wednesday, November 07, 2007 1:49 PM
  •  

    yes i have a 5673mb pentium processor and its big (not as big as my nob)
    Wednesday, November 07, 2007 1:56 PM
  • Great thanks.  This helped me identify where the probelm was.  It was with another datetime parameter which I had tried to change the formatting on (stupidly) and was causing the error.  Thanks for helping me narrow down the problem.

     

    Regards

     

    Wednesday, November 07, 2007 2:24 PM