'Data too long for column at row1' When data is 1 character

Question

When I try to input data into a column in a table, the error 'Data too long' appears despite my data being 5 characters and the field is a varchar(8). What would I need to change in order for this to work as when I add a watch to the command the character is only 5 characters long still.

Here is my command code:

[HttpPost]
    public IActionResult CreateCar(CarModel carModel)
    {
        if (ModelState.IsValid)
        {
            var connection = new MySqlConnection(Configuration.GetConnectionString("MySqlConnection"));
            var command =
                "INSERT INTO `tblcars` (`reg`, `make`, `model`, `colour`, `type`, `fueltype`, `regDate`, `lastService`, `nextService`, `mileage`, `status`) VALUES ('@Reg', '@Make', '@Model', '@Colour', '@Type', '@FuelType', '@RegDate', '@LastService', '@NextService', '@Mileage', '@Status')";
            var cmd = new MySqlCommand(command, connection);
            cmd.Parameters.AddWithValue("@Reg", carModel.Reg.ToUpper(new CultureInfo("en-GB", false)));
            cmd.Parameters.AddWithValue("@Make", carModel.Make.ToUpper(new CultureInfo("en-GB", false)));
            cmd.Parameters.AddWithValue("@Model", carModel.Model.ToUpper(new CultureInfo("en-GB", false)));
            cmd.Parameters.AddWithValue("@Colour", carModel.Colour.ToUpper(new CultureInfo("en-GB", false)));
            cmd.Parameters.AddWithValue("@Type", carModel.Type.ToUpper(new CultureInfo("en-GB", false)));
            cmd.Parameters.AddWithValue("@FuelType", carModel.FuelType.ToUpper(new CultureInfo("en-GB", false)));
            cmd.Parameters.AddWithValue("@regDate", carModel.RegDate.ToString("yyyy-MM-dd hh:mm:ss"));
            cmd.Parameters.AddWithValue("@LastService", carModel.ServiceDate.ToString("yyyy-MM-dd hh:mm:ss"));
            cmd.Parameters.AddWithValue("@NextService", carModel.NextServiceDate.ToString("yyyy-MM-dd hh:mm:ss"));
            cmd.Parameters.AddWithValue("@Mileage", carModel.Mileage);
            cmd.Parameters.AddWithValue("@Status", carModel.Status);
            connection.Open();
            cmd.ExecuteNonQuery();
            connection.Dispose();
            cmd.Dispose();
            return RedirectToAction("CreateCarConfirmed");
        }

        return View();
    }

The view is set to only accept a dropdown selection for the field that is telling is too long. Here is the view code (pastebin as it tries to display the HTML): https://pastebin.com/ijG8CG9j

None of those values is less than four characters in length. For example, `'@Reg'` is a 4-character string literal, `"@Reg"`, not a string containing the contents of the `@Reg` parameter. Remove the apostrophes in the `VALUES` list. — madreflection, Mar 30 '20 at 21:50
off-topic comments: [AddWithValue is Evil](https://www.dbdelta.com/addwithvalue-is-evil/) , `carModel.NextServiceDate.ToString("yyyy-MM-dd hh:mm:ss")` dates stored as strings are evil, too — Selvin, Mar 30 '20 at 21:55
Not so with MySQL: https://mysqlconnector.net/overview/using-addwithvalue/ — madreflection, Mar 30 '20 at 21:57
@Selvin How would I be able to convert the DateTime to a string I can put into the query for the MySQL database without using that sort of thing? — DanielRoberts, Mar 30 '20 at 23:41
When you pass it as a parameter, pass it as a `DateTime`, not as a string. `AddWithValue` will set the `MySqlDataType` property to the correct enum member if you pass a `DateTime` (i.e. don't call `ToString`). — madreflection, Mar 31 '20 at 05:01
@DanielRoberts I suggest you debug in the `CreateCar` method to see if the content of the field value corresponding to `carModel` is within 8 characters.You can refer to this: https://stackoverflow.com/questions/15949038/error-code-1406-data-too-long-for-column-mysql# — LouraQ, Mar 31 '20 at 08:06

DanielRoberts · Accepted Answer · 2020-04-01T12:19:26.290

Thanks to a comment from user @madreflection I have found having the ' characters around the parameters stops it replacing it with the correct parameter. My new command is as follows:

var command =
            "INSERT INTO tblcars (reg, make, model, colour, type, fueltype, regDate, lastService, nextService, mileage, status) VALUES (@Reg, @Make, @Model, @Colour, @Type, @FuelType, @RegDate, @LastService, @NextService, @Mileage, @Status)";

'Data too long for column at row1' When data is 1 character

1 Answers1