Error converting data type nvarchar to datetime.

Question

Using Vb.net/SQL Server 2000 updating a row via a gridview/sqldatasource

Stored proc:

@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 added to prevent extra result sets from
    -- interfering with SELECT statements.
    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

Before the parameters are passed to the sp I make sure it should be in the right format.

e.NewValues("Date_Start") = Convert.ToDateTime(e.NewValues("Date_Start"))

I don't understand where else it could be screwing up. I'd love to be able to keep it to a null value if it is one already, I'm just trying to get it to take anything right now and this is where I'm at.

Answer 1

use DateTime.TryParse to convert your string into a date. Otherwise you run into date localization issues where folks type in dates in DMY or MDY or YMD, etc...

Answer 2

to figure out the probelm, pass it in the datetime value as a varchar(n) parameter and in the procedure you can check using ISDATE (Transact-SQL) to see if it is valid or not, even record the bad value to a log. Once you see the bad value, your problem/solution will be more obvious.

Answer 3

For some reason the parameter was set to "String". When set correctly to:

<asp:Parameter Name="Date_Start" Type="DateTime" />

I get time out of range errors, but at least its seeing it as a DateTime now.

Answer 4

Try using the

Convert.ToDateTime(e.NewValues("Date_Start"))

using a format provider as per

http://msdn.microsoft.com/en-us/library/9xk1h71t.aspx

or stick a breakpoint on e.NewValues("Date_Start") and see what exactly is being passed to SQL (or use SQL Profiler to do the same).

If you can post what is being passed to the SP it might be more obvious what hte problem is.