holemania
asked on
VB.NET INSERT ERROR - Conversion failed when converting datetime from character string
I am having issue with inserting some value from a textbox into SQL database from a windows form application. Basically I have 2 textboxes that are use for Start/End Time. So 6:00 or 14:00 will be the input data. When I try to insert that, I get the following error. Any ideas on what I need to do to these textboxes?
"Conversion failed when converting datetime from character string."
Below is my query:
"Conversion failed when converting datetime from character string."
Below is my query:
CMD.COMMANDTEXT = "INSERT INTO EMP_TIME VALUES(" & strID & ", " & _
"'" & txtStart.Text & "', '" & txtEnd.Text & "')"
mohan_sekar
assuming you've a valid date value in the text field, use CDate(txtStart.Text)
Just the time value won't cast to a date. You have two choices:
1. Change your date fields in the Db to varchars
2. Load you time value as follows '1900-01-01' 06:00:00.000'
1. Change your date fields in the Db to varchars
2. Load you time value as follows '1900-01-01' 06:00:00.000'
ASKER
Yes it is just the time alone. Is there anyway I can check to make sure it's in time format or way of automatically formatting it into time? If that can be done, then I can also change the date field into varchars?
Example if someone enter 6:00 or 600 should be formated as 6:00? If someone put in 123ABC, it would error out or give message that it needs to be in time format?
Example if someone enter 6:00 or 600 should be formated as 6:00? If someone put in 123ABC, it would error out or give message that it needs to be in time format?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
use this lostfocus handle not the one above.
Private Sub TextBox1_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.LostFocus
'Edit date
If Trim$(TextBox1.Text) <> "" Then
If Not IsDate(TextBox1.Text) Then
MsgBox("Time is invalid.", vbOKOnly, "Error message")
TextBox1.Focus()
Else
TextBox1.Text = Format(CDate(TextBox1.Text), "HH:mm")
End If
End If
End Sub
My Suggestion to you.
1) Change the datatype of the field in your database to DateTime.
2) Then in storing the value, use the function I posted below to check if you have a valid time before saving it.
1) Change the datatype of the field in your database to DateTime.
2) Then in storing the value, use the function I posted below to check if you have a valid time before saving it.
Private Function IsValidTime(ByVal Time As String) As Boolean
Return DateTime.TryParse(Time, New DateTime)
End Function
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. That worked well.