C# MySQL Parameterized update returning nothing

Question

Whenever I try to update my database, the result ends up being 0.

If I do it in HeidiSQL, it updates just fine, so I know it's not the query.

I have my suspicion that it has to do with the parameters, but I'm unsure regarding that.

I tried with both @ and ?, but neither have worked.

MySqlCommand Command = new MySqlCommand("UPDATE `users` SET `cash`=@Cash, 
                           `distance_driven`=@DistanceDriven, `jobs_done`=@JobsDone,
                           `job_rank`=@JobRank WHERE `username`='@Username';"
                       , Connection);

Command.Parameters.AddWithValue("@Cash", Cash);
Command.Parameters.AddWithValue("@DistanceDriven", DistanceDriven);
Command.Parameters.AddWithValue("@JobsDone", JobsDone);
Command.Parameters.AddWithValue("@JobRank", JobRank);
Command.Parameters.AddWithValue("@Username", UName);

int result = Command.ExecuteNonQuery(); // result should be 1

Console.WriteLine(result); // ends up being 0

The connection opens fine, but I have no idea why it won't execute the query with the parameters.

Here is the function that requires this update:

public void UpdateUserInfo(object sender, ElapsedEventArgs evt, string uUName)
{
    bool cont = false;

    Console.WriteLine("UUI 1: " + evt.SignalTime); // gets here fine

    try
    {
        Console.WriteLine("UUI 2: " + evt.SignalTime); // gets here fine
        Database database = new Database();
        database.Connect();



        if (database.UpdateUserData(uUName, TotalCashWallet, TotalDistanceDriven, JobsDone, JobRank))
        {
            cont = true;
            Console.WriteLine("UUI 3: " + evt.SignalTime); // doesn't get here
        }

        if (cont == true)
        {
            cont = false;

            Console.WriteLine("UUI 4: " + evt.SignalTime);

            if (database.UpdateUserBank(uUName, BankInfo.Money, BankInfo.BonusPercentage, BankInfo.BonusLevel))
            {
                UserInfoUpdated = true;
                Console.WriteLine("UUI 5: " + evt.SignalTime);
                UserInfoUpdatedTimer.Enabled = true;
                return;
            }

        }

        UserInfoUpdated = false;
        return;
    }
    catch (Exception e)
    {
        Console.WriteLine("UUI 6: " + evt.SignalTime);
        Console.WriteLine(e.Message);
        ErrorHandler.WriteToLog(e.StackTrace);
        ErrorHandler.WriteToLog(e.Message);
        ErrorHandler.WriteToLog("------------------------------");
    }

    return;

}

It doesn't get to the catch part, so it won't log anything. I tried with both Exception and MysqlException, but it doesn't catch an error.

Doing it the unsafe way works

MySqlCommand Command = new MySqlCommand("
    UPDATE `users`
        SET `cash`=" + Cash + ",
            `distance_driven`=" + DistanceDriven + ",
            `jobs_done`=" + JobsDone + ",
            `job_rank`=" + JobRank + "
    WHERE `username`='" + UName + "';"
, Connection);

yes, it updates fine in HeidiSQL, as stated in the second paragraph. — Kibo, Oct 02 '15 at 11:11
When you set a parameter's value using `AddWithValue`, you don't need the `@`. You also shouldn't be quoting `@UserName` in your `where` clause... — Simon MᶜKenzie, Oct 02 '15 at 11:13
I tried without that as well, but it still would end up updating 0 rows. — Kibo, Oct 02 '15 at 11:14
`where username='@Username'` should be`where username=@Username` - no apostrophes. Currently you're looking for a username which starts with an @ etc. — Jon Skeet, Oct 02 '15 at 11:14
Just for the sake of clarity and eliminating the obvious, you are executing this query aren't you? — ZeroBased_IX, Oct 02 '15 at 11:22
Jezza, no offense, but I think you have a bit of trouble reading... int result = Command.ExecuteNonQuery(); // result should be 1 — Kibo, Oct 02 '15 at 11:23
What are your parameter's types? I mean, "Cash", "JobRank", "UName" etc. Are they all string? — Kemal Kefeli, Oct 02 '15 at 14:00
decimal Cash, int DistanceDriven, int JobsDone, int JobRank, string UName In the database it's decimal(10,2) cash, int(10) distance_driven, int(10) jobs_done, int(10) job_rank, and varchar(32) username — Kibo, Oct 02 '15 at 16:13

Dmytro Shevchenko · Answer 1 · 2015-10-02T11:19:15.643

0

You don't need the single quotes around string parameters. Use this query instead:

string query = @"
    UPDATE `users` SET
        `cash`=@Cash,
        `distance_driven`=@DistanceDriven,
        `jobs_done`=@JobsDone,
        `job_rank`=@JobRank
    WHERE
        `username`=@Username"

MySqlCommand command = new MySqlCommand(query, Connection);

edited Oct 02 '15 at 11:19

answered Oct 02 '15 at 11:16

Dmytro Shevchenko

33,431
6
51
67

That was my initial query, but that didn't do anything, and I can't do that since some usernames contain spaces in them. – Kibo Oct 02 '15 at 11:18
1

@Kibo this is a prepared statement. Parameters are actually sent separately from the query. You don't need to worry about spaces. – Dmytro Shevchenko Oct 02 '15 at 11:19
@Kibo please try with the exact query I provided and check if the data has been changed in the database. – Dmytro Shevchenko Oct 02 '15 at 11:24
Tried it, but still no change in the database. – Kibo Oct 02 '15 at 11:29
1

In that case, you need to find the minimal code that reproduces the problem. Right now there are too many things that could go wrong. Try with a new table and just one field, without a `WHERE` condition first. See if that would work. – Dmytro Shevchenko Oct 02 '15 at 11:30
Tried that, returns with that the column in question can't be null. I think the parameters aren't being replaced for some reason, and I tried with all kinds of variations, @ and ? in the query, @, ? and no prefix in the parameters. – Kibo Oct 02 '15 at 11:50
I tried that as well, it still wouldn't set the parameters right. – Kibo Oct 03 '15 at 00:15
@Kibo what are the exact values of the parameters you are passing to the query? – Dmytro Shevchenko Oct 03 '15 at 06:21
Differs per player, but I know that it should work as intended because I'm testing it on myself, and my data is in the database. It's weird because concatenating the values directly into the query (unsafe, I know) works fine, but using parameters it decides to just flip. – Kibo Oct 03 '15 at 11:59
@Kibo are there any culture-specific characters in your name? – Dmytro Shevchenko Oct 03 '15 at 15:42
No, they're all UTF-8. – Kibo Oct 03 '15 at 21:29

C# MySQL Parameterized update returning nothing

1 Answers1