Truncated incorrect DOUBLE value: 'hello.world'

0

In my DAO, I have:

public void UpdateProfile(string newName, string newBio, long profileId)
{
    using var dbConnection = _databaseProvider.GetConnection();
    dbConnection.SetQuery($"UPDATE `profile_data` SET `name` = @newName AND `bio` = @newBio AND `fixed_unicode` = 1 WHERE `profile_id` = @profileId");
    dbConnection.AddParameter("newName", newName);
    dbConnection.AddParameter("newBio", newBio);
    dbConnection.AddParameter("profileId", profileId);
    dbConnection.ExecuteQuery();
}

It results in:

Unhandled exception. MySql.Data.MySqlClient.MySqlException (0x80004005): Truncated incorrect DOUBLE value: 'hello.world'

I don't understand why, because the name column is a varchar(255), verified via:

>> SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'profile_data' AND COLUMN_NAME = 'name'
>> varchar

Below I will place code references in my main example, for anyone to check out if it helps debug the issue.

_databaseProvider:

public class DatabaseProvider : IDatabaseProvider
{
    private readonly string _connectionString;

    public DatabaseProvider(string connectionString)
    {
        _connectionString = connectionString;
    }

    public DatabaseConnection GetConnection()
    {
        var connection = new MySqlConnection(_connectionString);
        var command = connection.CreateCommand();
        
        return new DatabaseConnection(connection, command);
    }
}

DatabaseConnection:

public class DatabaseConnection : IDisposable
{
    private readonly MySqlConnection _connection;
    private readonly MySqlCommand _command;

    public DatabaseConnection(MySqlConnection connection, MySqlCommand command)
    {
        _connection = connection;
        _command = command;

        _connection.Open();
    }

    public void SetQuery(string commandText)
    {
        _command.Parameters.Clear();
        _command.CommandText = commandText;
    }

    public int ExecuteQuery()
    {
        return _command.ExecuteNonQuery();
    }

    public Task ExecuteQueryAsync()
    {
        return _command.ExecuteNonQueryAsync();
    }

    public MySqlDataReader ExecuteReader()
    {
        return _command.ExecuteReader();
    }

    public object ExecuteScalar()
    {
        return _command.ExecuteScalar();
    }

    public int GetLastId()
    {
        SetQuery("SELECT LAST_INSERT_ID();");
        return int.Parse(ExecuteScalar().ToString());
    }

    public void AddParameter(string name, object value)
    {
        _command.Parameters.AddWithValue(name, value);
    }

    public void Dispose()
    {
        _connection.Close();
        _command.Dispose();
    }
}
c#
mysql
database
asked on Stack Overflow Dec 16, 2020 by AAA

2 Answers

2

You UPDATE query is incorrect. Replace AND keyword with a comma. The query should look like this:

$"UPDATE `profile_data` SET `name` = @newName, 
                            `bio` = @newBio, 
                            `fixed_unicode` = 1 
WHERE `profile_id` = @profileId"
answered on Stack Overflow Dec 16, 2020 by mb14
0

Without executing your code, I can notice that your UPDATE command is not correct. Try this

UPDATE `profile_data` SET `name` = @newName ,`bio` = @newBio ,`fixed_unicode` = 1 WHERE `profile_id` = @profileId");
answered on Stack Overflow Dec 16, 2020 by vlada

User contributions licensed under CC BY-SA 3.0