Wednesday, November 07, 2007 11:02 AM
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.
Wednesday, November 07, 2007 1:21 PMCode 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
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:36 PM
you can use CDate(expresion) function to confert string date to DateTime type.
Wednesday, November 07, 2007 1:49 PM
this wasn't helpful
Wednesday, November 07, 2007 1:56 PM
Wednesday, November 07, 2007 2:24 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.