none
String or binary data would be truncated... on DateTime column RRS feed

  • Question

  • Hi!

    I have a program in C# where I use an SqlDataAdapter named Adapter to retrieve and update one row in a database table.

    First I fill the DataTable named QueryBuffer with a row than I change some fields and finaly I update it back to the database with help of the adapter:

    Adapter.Update(QueryBuffer);

    I get an error message: "String or binary data would be truncated..."

    There is a field in the table named "ErkezesDat" (DataTime type) which is not modified and contains a full DateTime value with hours, minutes and seconds. If I stop at the command above with a breakpoint and open the QueryBuffer table I can see a DateTime value in a readable format:

    2012.03.02. 20:32:57

    If I delete the dot after the date part but leave everything other unchanged I do not get error message.

    I dont understand because the  ErkezesDat field is a DateTime field not a varchar so the ADO.NET stores the DateTime value in a binary format and shows it in the format above only for me. How can the error message depend on that dot?

    And what is the solution?


    Gabriel

    Friday, March 2, 2012 9:14 PM

Answers

  • The data type of this column in the database is full DateTime. The data type in the data table is full DateTime. I read a record from the database into the data table and I can not write it back because of the error message. The only different from an other DateTime column that it contains not only a date value but a real date-time value (the time part is not 00:00:00). And I would like to keep this time part.

    The command is:

    Adapter.Update(QueryBuffer);

    wher Adapter is an SqlDataAdapter. The database is in an MSSQL 2008 server.


    Gabriel

    • Marked as answer by IamConcerned Saturday, March 3, 2012 6:01 PM
    Saturday, March 3, 2012 4:23 PM

All replies

  • Gabriel,

    Be aware that a DateTime value in Net is a struct where the value is stored in a Int64 which are the ticks been in 100th-nanoseconds been since 1-1-1 0:0:0

    The datetime is also not in an SQL server represented in a character format, (would be silly if you know in how many ways that is used in the world). 

    In SQL Server are however 3 datetime types, the SmallData starting at 1990, the DateTime starting at 1-1-1753 (the default start taken for the Gregorian calendar in the British empire which contained than the current USA) and the DateTime2 which is the same format as the Net date time.

    For the communication between .Net and SQL server you always use the DateTime from Net the best given in a parameter.

     


    Success
    Cor


    Saturday, March 3, 2012 8:52 AM
  • Thank you for the reply, but I still don't know what I have to do to avoid the error messege.

    I can not beleive that I must manually delete the point from the string representation of DateTime field in the data table.


    Gabriel

    Saturday, March 3, 2012 12:25 PM
  • Tell us what the datatype is in the database, if that is a whatever date time than show your code which does the update. 

    If that is not a datetime column than make it a datetime column. 


    Success
    Cor

    Saturday, March 3, 2012 1:56 PM
  • The data type of this column in the database is full DateTime. The data type in the data table is full DateTime. I read a record from the database into the data table and I can not write it back because of the error message. The only different from an other DateTime column that it contains not only a date value but a real date-time value (the time part is not 00:00:00). And I would like to keep this time part.

    The command is:

    Adapter.Update(QueryBuffer);

    wher Adapter is an SqlDataAdapter. The database is in an MSSQL 2008 server.


    Gabriel

    • Marked as answer by IamConcerned Saturday, March 3, 2012 6:01 PM
    Saturday, March 3, 2012 4:23 PM
  • It was an other mistake as I see. The source of problem was a different thing. I'm sorry.

    Thank you for attention.


    Gabriel

    Saturday, March 3, 2012 6:01 PM