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

 

Answer 1

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

Answer 2

you can use CDate(expresion) function to confert string date to DateTime type.

Answer 3

this wasn't helpful

Answer 4

 

yes i have a 5673mb pentium processor and its big (not as big as my nob)

Answer 5

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