Execute UPDATE command

Question

I wrote that to update my SQL table.

        mycmd.CommandText = "UPDATE savedinfo SET User_id='" + Login.GetUserID().ToString() + "', Date='" + DateTime.Now.ToLongDateString() + "', Evaluate='" + activeEvaluate.ToString() + "', TimeStart='" + dtCurrentTime1.ToLongTimeString() + "', TimeEnd='" +dtCurrentTime2.ToLongTimeString() + "' , Salary= '" + todaySalary.Text +"'";
        mycmd.Connection = con;
        mycmd.ExecuteNonQuery();

When getting to mycmd.ExecuteNonQuery(); I got an error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: String or binary data would be truncated.

The statement has been terminated.

What is the problem here? Thanks

What does the Commandtext look like for a record that is failing, and how does that compare to the table schema? — Rowland Shaw, Aug 14 '15 at 13:31
As the [past][1] says, The field is NOT big enough to hold your data. [1]: http://stackoverflow.com/questions/5591473/error-string-or-binary-data-would-be-truncated-when-trying-to-insert — A. Abramov, Aug 14 '15 at 13:31
Also you don't need to call `.ToString()` if the left-hand argument of the assign value(s) is a string, since it's called implicitly. — Bauss, Aug 18 '15 at 11:05

Stig · Accepted Answer · 2015-08-18T10:41:25.217

The error implies that you are attempting to insert a value which is to big for the column length within the database. Please look through your database schema and identify which column is causing the issue.

Looking at the snippet of code I have a few other observations which maybe of some help.

Does the Update statement require a where clause?

Do use parameterised queries as this will help against SQL injection attacks.

Using statement are very helpful in clearing and cleaning up an objects, in this case a using statement help managing the open connections to the database.

If you wish to check for valid string lengths before sending them to the database check the arguments before creating connections etc. ideally these check would be carried out in your business logic layer.

          if (activeEvaluate.Length > 5)
          {
             throw new ArgumentException("activeEvaluate");
          }

          using (SqlConnection connection = new SqlConnection(ConnString))
          {
               using (SqlCommand cmd = connection.CreateCommand())
               {
                 cmd.CommandText = "UPDATE savedinfo set User_id= @UserId, Date = @Date, Evaluate = @Evaluate, TimeStart = @TimeStart, TimeEnd = @TimeEnd, Salary = @Salary ";

                 cmd.Parameters.AddWithValue("@UserId", Login.GetUserID());
                 cmd.Parameters.AddWithValue("@Date",  DateTime.Now.ToLongDateString());
                 cmd.Parameters.AddWithValue("@Evaluate", activeEvaluate.ToString());
                 cmd.Parameters.AddWithValue("@TimeStart", dtCurrentTime1.ToLongTimeString());
                 cmd.Parameters.AddWithValue("@TimeEnd", dtCurrentTime2.ToLongTimeString());
                 cmd.Parameters.AddWithValue("@Salary", todaySalary.Text);
                 cmd.Connection.Open();
                 cmd.ExecuteNonQuery();
              }
            }

Execute UPDATE command

1 Answers1