none
LINQ to SQL Server UPDATE query fails on datetime field RRS feed

  • Question

  • I'm getting errors doing a simple update on a SQL Server table that has a datetime (not null) column.

    The table is something like this:

    tablename = auctions
    id [int] - primary key
    auction_datetime [datetime]
    status [int]

    I then try to update the status for a record. Code in C#:

            LinqDataSource auction = new LinqDataSource(); 
            auction.ContextTypeName = "DataClassesDataContext"
            auction.TableName = "auctions"
            auction.EnableUpdate = true
             
            ListDictionary keys = new ListDictionary(); 
            ListDictionary oldValues = new ListDictionary(); 
            ListDictionary newValues = new ListDictionary(); 
     
            keys.Add("id", auctionInfo.id); 
     
            oldValues.Add("status", auctionInfo.status); 
            newValues.Add("status", auctionInfo.status + 1); 
             
            try 
            { 
                auction.Update(keys, newValues, oldValues); 
            } 
            catch (Exception e) 
            { 
                Logger.WriteDebug(e.Message); 
            } 

    This always returns the error "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.".

    Analysing the SQL shows that it's doing something like:

    UPDATE [dbo].[Auction]
    SET [status] = @p4
    WHERE ([id] = @p0) AND ([underlying_id] = @p1) AND ([auction_datetime] = @p2) AND ([status] = @p3)
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [26]
    -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
    [@p2] SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

    The datetime value for this record is "19/03/2009 17:00:00" [British english setting in SQL Server and on my Vista locale]

    The annoying thing is that I have another page that uses a pure .aspx LinqDataSource/GridView and it can update fine. If I look at the SQL generated by that page I see

    UPDATE [dbo].[Auction]
    SET [status] = @p4
    WHERE ([id] = @p0) AND ([underlying_id] = @p1) AND ([auction_datetime] = @p2) AND ([status] = @p3)
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [16]
    -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
    -- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [19/03/2009 13:00:00]
    -- @p3: Input Int (Size = 0; Prec = 0; Scale = 0) [3]

    This is driving me nuts. What am I doing wrong in my code?

    thanks
    Thursday, March 19, 2009 10:53 AM

Answers

  • The uninitialized DateTime value comes from the fact that you didn't populate that value in the oldValues dictionary. Since your DateTime property is marked as UpdateCheck.Always, LINQ to SQL uses that property to find the existing row in the database to update. The locale, etc. are red herrings here.

    You can fix this by changing the "Update Check" property to Never when the auction_datetime is selected in the DBML designer. If you're not using the designer, you can change the UpdateCheck property on the ColumnAttribute for auction_datetime to UpdateCheck.Never.

    While you're there you might want to do the same thing with the underlying_id property. If you look at the DBML logs you'll see it's looking for a row where the ID is 0.

    UPDATE [dbo].[Auction]
    SET [status] = @p4
    WHERE ([id] = @p0) AND ([underlying_id] = @p1) AND ([auction_datetime] = @p2) AND ([status] = @p3)
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [26]
    -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
    [@p2] SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
    • Marked as answer by hkrhk Monday, March 30, 2009 12:58 PM
    Sunday, March 29, 2009 5:10 PM
    Answerer

All replies

  • I should say that if I use an SqlDataConnection instead, everything is fine. But I would sooner stick to one type of data source for my application.

    SqlDataSource sqlAuction = new SqlDataSource(); 
    sqlAuction.ConnectionString = WebConfigurationManager.ConnectionStrings
    ["MyDatabaseConnectionString"].ConnectionString; 
    sqlAuction.UpdateCommand = "update auction set status = @NewStatus where id = @ID"
    sqlAuction.UpdateParameters.Add("NewStatus", (auctionInfo.status + 1).ToString()); 
    sqlAuction.UpdateParameters.Add("id", auctionInfo.id.ToString()); 
    sqlAuction.Update(); 
     

    Thursday, March 19, 2009 1:53 PM
  • From your output,it looks like Your sql server and .net application  are following different date settings, as you have stated your SQL server is using british setting so it will expect date in dd/mm/yyyy format. and from your error log,it looks liks environment where your code is being executed follows mm/dd/yyyy format (notice 12/31/9999 11:59:59 PM in exception text) .

    log of your .aspx page that is working clearly shows that its following dd/mm/yyyy (notice 19/03/2009 13:00:00 in log) and that is same as your SQL server locale settings,thts why its working well.  

    what i assume is that IIS server where .aspx is running is configured to follow proper locale. Whereas your windows apllication (.net runtime on ur machine) is following different locale so its creating troubles for you. You just need to look into your .net framework settings or just making you vista follow british regional setting will do if currently its not following the same .

    And one more thing reason why your SqlDataConnection is working is ADO.Net takes care of all this things .when its fetching data from SQL server,internally is follows same data format and locale.I assume LINQ is not taking care of the same. 

    Hope this helps you.

    • Proposed as answer by Jatinp Sunday, March 22, 2009 7:53 PM
    Sunday, March 22, 2009 7:26 PM
  • >>what i assume is that IIS server where .aspx is running is configured to follow proper locale. Whereas your windows apllication (.net runtime on ur machine) is following different locale

    This is running under VWD on the same machine. Both SQL server and Vista are set to british english. I dont see any regional options specific to .Net 2.

    >>I assume LINQ is not taking care of the same.

    As per above, LINQ works fine when I just add it to an .aspx page. The problem only seems to come when I call it programatically.

    I wonder if there is some problem with the way that I'm calling Linq from code?

    thx

    Monday, March 23, 2009 2:26 AM
  • If a web app, maybe the locale settings for the user account that IIS is using does not have the same locale settings as the user account you (and SQL Server) is running under...?
    Kristofer - Huagati Systems Co., Ltd. - web: www.huagati.com - blog: blog.huagati.com - twitter: twitter.com/KristoferA
    Monday, March 23, 2009 3:25 AM
    Answerer
  • Thanks for the info, however I'm running via "debug/F5" under VWD, so I assumed that would launch the ASP development server process (the mini IIS one) under my user account. Isnt that the case?

    Also I tried setting <globalisation> in web.config to en-gb, but that made no difference.

    Again, if I just dump the following aspx code on another page in the same project and edit the table via GridView it works just fine:

        <asp:LinqDataSource ID="LinqDSAuctions" runat="server" 
            ContextTypeName="DataClassesDataContext" EnableDelete="True" 
            EnableInsert="True" EnableUpdate="True" TableName="Auctions" 
        </asp:LinqDataSource> 
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
            CellPadding="4" DataKeyNames="id" DataSourceID="LinqDSAuctions" 
            ForeColor="#333333" GridLines="None" AllowPaging="True" 
            AllowSorting="True" CssClass="BoxTable"
            <RowStyle BackColor="#E7E6E3" ForeColor="#333333" /> 
            <Columns> 
            ... 
     

    So I'm still wondering why Linq on an aspx page works fine, but my code above doesnt work, with all other things being equal.

    Anyone else has any other ideas ?

    thanks
    Monday, March 23, 2009 11:05 AM
  • Is the culture or lcid attributes set on the page directive on the asp page where it fails, but not on the page where it doesn't fail?
    Kristofer - Huagati Systems Co., Ltd. - web: www.huagati.com - blog: blog.huagati.com - twitter: twitter.com/KristoferA
    Tuesday, March 24, 2009 5:13 AM
    Answerer
  • You may have uninitialized datatime values.  The default .Net DateTime value is outside the range of a legal SQL date time.
    Wayward LINQ Lacky
    Wednesday, March 25, 2009 2:38 AM
    Moderator
  • Thanks for the suggestions...

    >>Is the culture or lcid attributes set on the page directive on the asp page where it fails, but not on the page where it doesn't fail?

    No - there are no page directives that affect culture on either page.

    Also as I mentioned above setting globalization culture="en-GB" in web.config makes no difference to whether the .update() code fails or not. Also the GridView works fine every time.


    >>You may have uninitialized datatime values.  The default .Net DateTime value is outside the range of a legal SQL date time.

    This is just an update to an existing record that has a non-null date value. I'm not even updating the datetime value - just some other fields on the record. Also as mentioned earlier, the same operation when conducted by a GridView/LinqDataSource embedded in a web page works fine. So I dont see where the uninitialised value would come from.

    So I'm pretty sure this has something to do with the way I'm calling the .update() method.
    Sunday, March 29, 2009 6:20 AM
  • The uninitialized DateTime value comes from the fact that you didn't populate that value in the oldValues dictionary. Since your DateTime property is marked as UpdateCheck.Always, LINQ to SQL uses that property to find the existing row in the database to update. The locale, etc. are red herrings here.

    You can fix this by changing the "Update Check" property to Never when the auction_datetime is selected in the DBML designer. If you're not using the designer, you can change the UpdateCheck property on the ColumnAttribute for auction_datetime to UpdateCheck.Never.

    While you're there you might want to do the same thing with the underlying_id property. If you look at the DBML logs you'll see it's looking for a row where the ID is 0.

    UPDATE [dbo].[Auction]
    SET [status] = @p4
    WHERE ([id] = @p0) AND ([underlying_id] = @p1) AND ([auction_datetime] = @p2) AND ([status] = @p3)
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [26]
    -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
    [@p2] SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
    • Marked as answer by hkrhk Monday, March 30, 2009 12:58 PM
    Sunday, March 29, 2009 5:10 PM
    Answerer