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
– Kibo, Commented Oct 2, 2015 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
– Simon MᶜKenzie, Commented Oct 2, 2015 at 11:13
where username='@Username' should bewhere username=@Username - no apostrophes. Currently you're looking for a username which starts with an @ etc. — Jon Skeet
– Jon Skeet, Commented Oct 2, 2015 at 11:14
Jezza, no offense, but I think you have a bit of trouble reading... int result = Command.ExecuteNonQuery(); // result should be 1 — Kibo
– Kibo, Commented Oct 2, 2015 at 11:23

Dmytro Shevchenko · Accepted Answer · 2015-10-02 11:19:15Z

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 2, 2015 at 11:19

answered Oct 2, 2015 at 11:16

Dmytro Shevchenko

34.9k6 gold badges56 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Kibo Over a year ago

That was my initial query, but that didn't do anything, and I can't do that since some usernames contain spaces in them.

Dmytro Shevchenko Over a year ago

@Kibo this is a prepared statement. Parameters are actually sent separately from the query. You don't need to worry about spaces.

Dmytro Shevchenko Over a year ago

@Kibo please try with the exact query I provided and check if the data has been changed in the database.

Kibo Over a year ago

Tried it, but still no change in the database.

Dmytro Shevchenko Over a year ago

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.

|

Collectives™ on Stack Overflow

C# MySQL Parameterized update returning nothing

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related