Syntax error converting datetime from character string
Getting this error when updating a row via a gridview with a sqldatasource in Vb.net/SQL Server 2000. No matter what input (1/1/2010, blank, etc) I give it I can't seem to get it right.
Code before the input is passed to the sp:
Dim sqldatenull As DateTime
Dim DateVerify As DateTime
sqldatenull = DateTime.MaxValue
If (e.NewValues("Date_Start") Is Nothing) Then
e.NewValues("Date_Start") = sqldatenull
Else
DateTime.TryParse(e.NewValues("Date_Start").ToString, DateVerify)
MsgBox("Worked!")
e.NewValues("Date_Start") = DateVerify
End If
SP:
@ISTag varchar(10),
@PCISTag varchar(10),
@User varchar(50),
@Date_Start datetime,
@Date_End datetime,
@Status varchar(50),
@Cost money,
@Notes varchar(500),
@CreatedBy varchar(50),
@ModifiedBy varchar(50)
AS
BEGIN
SET NOCOUNT ON;
EXEC sp_changeLog 'HardDrive', @ISTag, @ModifiedBy
UPDATE T_HardDrive
SET PCIStag = @PCISTag,
[User] = @User,
Date_Start = @Date_Start,
Date_End = @Date_End,
[Status] = @Status,
Cost = @Cost,
Notes = @Notes,
ModifiedBy = @ModifiedBy
WHERE ISTag = @ISTag
SQLDatasource definition:
<asp:SqlDataSource ID="InventoryHardDrive" runat="server"
ConnectionString="<%$ ConnectionStrings:InventoryConnectionString %>"
DeleteCommand="DELETE FROM [T_HardDrive] WHERE [ISTag] = @original_ISTag"
InsertCommand="sp_HardDriveInsert" InsertCommandType="StoredProcedure"
OldValuesParameterFormatString="original_{0}"
SelectCommand="sp_HardDriveSelect" SelectCommandType="StoredProcedure"
UpdateCommand="sp_HardDriveUpdate" UpdateCommandType="StoredProcedure">
<DeleteParameters>
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="ISTag" Type="String" />
<asp:Parameter Name="PCISTag" Type="String" />
<asp:Parameter Name="User" Type="String" />
<asp:Parameter Name="Date_Start" Type="DateTime" />
<asp:Parameter Name="Date_End" Type="DateTime" />
<asp:Parameter Name="Status" Type="String" />
<asp:Parameter Name="Cost" Type="Decimal" />
<asp:Parameter Name="Notes" Type="String" />
<asp:Parameter Name="CreatedBy" Type="String" />
<asp:Parameter Name="ModifiedBy" Type="String" />
</InsertParameters>
<SelectParameters>
<asp:ControlParameter ControlID="hideInactiveCheckBox" Name="Active"
PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="filterText" DefaultValue="%" Name="ISTag"
PropertyName="Text" Type="String" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="ISTag" Type="String" />
<asp:Parameter Name="PCISTag" Type="String" />
<asp:Parameter Name="User" Type="String" />
<asp:Parameter Name="Date_Start" Type="DateTime" />
<asp:Parameter Name="Date_End" Type="DateTime" />
<asp:Parameter Name="Status" Type="String" />
<asp:Parameter Name="Cost" Type="Decimal" />
<asp:Parameter Name="Notes" Type="String" />
<asp:Parameter Name="CreatedBy" Type="String" />
<asp:Parameter Name="ModifiedBy" Type="String" />
</UpdateParameters>
</asp:SqlDataSource>
If I leave the box empty the value is set to "12/31/9999 11:59:59 PM". Is this not the correct format for DateTime?
Error:
Syntax error converting datetime from character string.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Syntax error converting datetime from character string.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Syntax error converting datetime from character string.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1951450
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4849003
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2394
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
System.Web.UI.WebC开发者_如何学JAVAontrols.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +386
System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +325
System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +92
System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +907
System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +704
System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +95
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +123
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +118
System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +135
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +175
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
My guess would be that you're trying to store a date in SQL Server that exceeds the range of its date type. .NET's DateTime
will happily store dates some millenia into the future, but SQL Server is more limited.
Why don't you use a nullable DateTime
instead, and pass actual null
(or better yet, DBNull
) when you have to?
精彩评论