Results 1 to 16 of 16

Thread: why do I use SQL Server Management Studio to update a row of a table in SQL Server ?

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    517

    why do I use SQL Server Management Studio to update a row of a table in SQL Server ?

    why do I use SQL Server Management Studio to update a row of a table in SQL Server 2005 but it's not row was update ?

    A few days ago me to use SQL Server Management Studio to update a table in Microsoft SQL Server 2005. In Object Explorer, you right-click the name of the table, and then I click Open Table. I update a row of the table normal but now I do it again. I may receive one of the following error messages unexpectedly in the Microsoft SQL Server Management Studio dialog box:

    No row was updated
    The data in row 61 was not committed
    The statement has bĂȘn terminated
    Correct the errors and retry or press ESC to cancel the change(s).


    You see the attached image file: Name:  SQLServer01.jpg
Views: 544
Size:  39.2 KB

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve

    You shouldn't be posting questions on forums before you even bothered to use a search engine. If you'd have simply copied the error message into Google or Bing then you'd have got your answer. Too much trouble?

    "Truncated" means cut short, so the error message is telling you that you entered some data that would have to be cut short in order to save, i.e. is too long for the column you're trying to save it to. If you're using SSMS, you ought to know how wide your columns are.

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve

    Quote Originally Posted by jmcilhinney View Post
    If you're using SSMS, you ought to know how wide your columns are.
    Or they shouldn't give him access to SSMS in any case if he doesn't know such basic things.....
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve

    Sigh...
    Please remember next time...elections matter!

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    517

    Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve

    I have searched on google but no results, I do not think it is "truncated" as you said but a few days ago I edited directly from the normal table, if I use SQL statements to update data is normal, but if I fix it directly from the table, the above error will appear, see my attached video http://www.mediafire.com/file/cd14ia...teSQL.swf/file

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve

    Quote Originally Posted by dong View Post
    I do not think it is "truncated" as you said
    I haven't watched your video but I'm confident that you're wrong.

    Name:  Truncated.jpg
Views: 418
Size:  33.0 KB

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    517

    Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve

    My data type is datetime, I revised the posting date, in your opinion, how do I fix this error ?

    Code:
    UPDATE Article
    SET PostDate = '2019/03/28 02:43:08'
    WHERE ArticleID = 107;

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve

    If you want to use literal text to represent a date in T-SQL then use 'yyyy-MM-dd' format, not 'yyyy/MM/dd'. If you use slashes then I'm guessing that it would try to interpret it as 'dd/MM/yyyy' or 'MM/dd/yyyy'. The truncation that it is referring to is likely to a 2-digit day or month from the 4-digit substring in your code.

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    517

    Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve

    I asked how to fix the edit directly on the Table, still using through the SQL statement I know, you see my attached attachments
    Name:  SQLServer02.jpg
Views: 358
Size:  44.9 KB
    Name:  SQLServer03.jpg
Views: 394
Size:  24.1 KB

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve

    When inserting/updating dates via sql, best practice is to explicitly convert the string to a date using a format. The formats can be found here. That said, assuming the default format as JM has suggested is basically OK and lots of us do it as a short cut. You really should do one or the other though as anything else is likely to produce incorrect results.

    As for chaning it using SSMS, that error message is telling you that some piece of string data on the row is being truncated. It's not checked until you leave the row, though, so it could be any field on the row, not necessarily the date field you've just updated. In fact, if that field is genuinely set to a date, it's definitely not the problem.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  11. #11
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve

    Quote Originally Posted by dong View Post
    I asked how to fix the edit directly on the Table, still using through the SQL statement I know, you see my attached attachments
    Name:  SQLServer02.jpg
Views: 358
Size:  44.9 KB
    Name:  SQLServer03.jpg
Views: 394
Size:  24.1 KB
    Look at your SQL statement again... and then read this part next, again...


    Quote Originally Posted by jmcilhinney View Post
    If you want to use literal text to represent a date in T-SQL then use 'yyyy-MM-dd' format, not 'yyyy/MM/dd'. If you use slashes then I'm guessing that it would try to interpret it as 'dd/MM/yyyy' or 'MM/dd/yyyy'. The truncation that it is referring to is likely to a 2-digit day or month from the 4-digit substring in your code.
    Now, read it again, pay attention to part I've highlighted...


    And then compare that to you SQL statement, again.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  12. #12
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve

    I think the date issue is a distraction. Dong absolutely should be taking good advice on how to express dates, but it's not the cause of that error.

    The error is being thrown because a string or binary field on that row is being overflowed.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  13. #13

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    517

    Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve

    In my opinion this error is due to SQL Server Management Studio software, do you think so ?

  14. #14
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve

    Quote Originally Posted by FunkyDexter View Post
    I think the date issue is a distraction. Dong absolutely should be taking good advice on how to express dates, but it's not the cause of that error.

    The error is being thrown because a string or binary field on that row is being overflowed.
    Sounds plausible. A sensible person would actually test that specifically, rather than insist that it is a bug in a piece of software used by millions and ask whether we would agree when everything in this thread indicates that we don't.

  15. #15
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve

    Errr....? The OP does know, that, basically, the underlying Datatype of a DateTime is actually a double?
    As i said many times: i've no experience whatsoever in MS-SQL, but he's changing the value directly in the table.
    Has he tried to enter, say "456789.2356"?
    Nevermind that it eludes me completely, why someone would want to change something directly in the table, and not with an SQL-Statement.
    And since he said, that it works with SQL, it's probably because the engine is correctly "translating" the value to its underlying datatype.
    Maybe he's trying to avoid some auditing mechanism, which shows who has changed what....
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  16. #16
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve

    In my opinion this error is due to SQL Server Management Studio software, do you think so ?
    No, I don't agree. SSMS is doing exactly what it should. It's telling you that you have made an error and it's not going to let you.

    SSMS does not commit changes you make as you leave each cell, it commits them as you leave the row. So when you leave the row every cell in the is checked. You have entered too much data into one of the string or binary cells so SSMS would have to truncate it in order to accept it. That would involve data loss so it is refusing to accept it and telling you why: "String or Binary Data would be truncated". You need to work out which cell is affected but it won't be a date column because that's neither string nor binary.

    The only other possible explanation is that your PostDate column isn't a DateTime but is actually a string (varchar) column and the value you're entering is too long. This, of course, would actually be exactly the same underlying error as above. It seems unlikely because the formatting in your screenshots is perfect but we can't be 100% just from the screenshot. I suggest that you check the type on that column if only to eliminate it as a possibility.

    Errr....? The OP does know...
    That wouldn't be an issue in SSMS. SqlServer itself will allow you to handle datetimes as double but Management Studio converts them in the presentation layer. So when you entered 456789.2356, it would try to convert that to a date using a known format and fail. So you can use those tricks directly with SQL, but not the UI.

    @JM, I always try to be the nice guy but sometimes, just sometimes, I feel the lure of the darkside
    Last edited by FunkyDexter; Feb 20th, 2020 at 03:22 AM.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width