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();
}
}
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"
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");
User contributions licensed under CC BY-SA 3.0