Okay, so I'm trying to update a specific row and column in my database table "Delivery1" with a datetime from a webform. But I keep getting this error and I have no idea why. My datatype for the column in the table is set to datetime, and I'm feeding in
a system.datetime.now value into it. Can anyone help? Here is my code..
Protected Sub BtnSignOut_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnSignOut.Click
With DeliveryDataAdapter.SelectCommand
'if the parameter has not been created yet then create it and set its value.
If .Parameters.Contains("@EndTime") = False Then
.Parameters.Add("@EndTime", SqlDbType.DateTime)
.Parameters("@EndTime").Value = System.DateTime.Now
.Parameters.Add("@DeliveryID", SqlDbType.Int)
.Parameters("@DeliveryID").Value = DropDownList4.SelectedItem.Value
Else
'otherwise if the parameter has already been created then just set its value.
.Parameters("@EndTime").Value = System.DateTime.Now
.Parameters("@DeliveryID").Value = DropDownList4.SelectedItem.Value
End If
End With
With cmdDelivery
If .Parameters.Contains("@EndTime") = False Then
.Parameters.Add("@EndTime", SqlDbType.DateTime)
.Parameters("@EndTime").Value = System.DateTime.Now
.Parameters.Add("@DeliveryID", SqlDbType.Int)
.Parameters("@DeliveryID").Value = DropDownList4.SelectedItem.Value
Else
.Parameters("@EndTime").Value = System.DateTime.Now
.Parameters("@DeliveryID").Value = DropDownList4.SelectedItem.Value
End If
End With
strSQLStatement = "SELECT * FROM Mpirson.Delivery1 WHERE DeliveryID = @DeliveryID"
DeliveryDataAdapter.SelectCommand.CommandText = strSQLStatement
Try
If con.State = ConnectionState.Closed Then
con.Open()
End If
Catch ex As Exception
Response.Write(ex.Message)
Exit Sub
End Try
strSqlStatementCmd = "UPDATE Mpirson.Delivery1 SET EndTime='@EndTime' WHERE DeliveryID = @DeliveryID"
cmdDelivery.CommandText = strSqlStatementCmd
cmdDelivery.ExecuteScalar()
End Sub
Man I feel like such a rookie, I can't believe I missed that error in my sql statement. Will have to be more careful in the future when copying and pasting code. >.>
Marked as answer by Chen Yu - MSFT on Dec 13, 2012 01:31 AM
Leschandrew
Member
1 Points
11 Posts
Conversion failed when converting date and/or time from character string
Dec 12, 2012 01:46 AM|LINK
Okay, so I'm trying to update a specific row and column in my database table "Delivery1" with a datetime from a webform. But I keep getting this error and I have no idea why. My datatype for the column in the table is set to datetime, and I'm feeding in a system.datetime.now value into it. Can anyone help? Here is my code..
Protected Sub BtnSignOut_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnSignOut.Click With DeliveryDataAdapter.SelectCommand 'if the parameter has not been created yet then create it and set its value. If .Parameters.Contains("@EndTime") = False Then .Parameters.Add("@EndTime", SqlDbType.DateTime) .Parameters("@EndTime").Value = System.DateTime.Now .Parameters.Add("@DeliveryID", SqlDbType.Int) .Parameters("@DeliveryID").Value = DropDownList4.SelectedItem.Value Else 'otherwise if the parameter has already been created then just set its value. .Parameters("@EndTime").Value = System.DateTime.Now .Parameters("@DeliveryID").Value = DropDownList4.SelectedItem.Value End If End With With cmdDelivery If .Parameters.Contains("@EndTime") = False Then .Parameters.Add("@EndTime", SqlDbType.DateTime) .Parameters("@EndTime").Value = System.DateTime.Now .Parameters.Add("@DeliveryID", SqlDbType.Int) .Parameters("@DeliveryID").Value = DropDownList4.SelectedItem.Value Else .Parameters("@EndTime").Value = System.DateTime.Now .Parameters("@DeliveryID").Value = DropDownList4.SelectedItem.Value End If End With strSQLStatement = "SELECT * FROM Mpirson.Delivery1 WHERE DeliveryID = @DeliveryID" DeliveryDataAdapter.SelectCommand.CommandText = strSQLStatement Try If con.State = ConnectionState.Closed Then con.Open() End If Catch ex As Exception Response.Write(ex.Message) Exit Sub End Try strSqlStatementCmd = "UPDATE Mpirson.Delivery1 SET EndTime='@EndTime' WHERE DeliveryID = @DeliveryID" cmdDelivery.CommandText = strSqlStatementCmd cmdDelivery.ExecuteScalar() End SubLeschandrew
Member
1 Points
11 Posts
Re: Conversion failed when converting date and/or time from character string
Dec 12, 2012 02:17 AM|LINK
Man I feel like such a rookie, I can't believe I missed that error in my sql statement. Will have to be more careful in the future when copying and pasting code. >.>
Chen Yu - MS...
All-Star
21584 Points
2493 Posts
Microsoft
Re: Conversion failed when converting date and/or time from character string
Dec 13, 2012 01:31 AM|LINK
Hi,
Glad to hear your have solved your problem. I will mark your reply as answer. If you have any question, welcome to ASP.NET forum again.
Best Wishes,
Feedback to us
Develop and promote your apps in Windows Store