C# - New Values not reflecting on Access Database using UPDATE SQL cmd

Question

I'm having problems with updating a row in the Users table of my Access DB. Here is the code below:

    private void SaveProfileInfo()
    {
        try
        {
            ChangeForeColorOfStatusMsg(Color.Black);
            ChangeTextOfStatusMsg("Saving new profile information...");
            const string cmd = @"UPDATE Users SET LastName=@LastName,FirstName=@FirstName,MiddleName=@MiddleName,Add_Num=@Add_Num,Add_Street=@Add_Street,Add_Brgy=@Add_Brgy,Add_City=@Add_City,MobileNumber=@MobileNumber,Gender=@Gender WHERE ID=@ID;";
            var dbConn = new OleDbConnection(cs);
            var dbCmd = new OleDbCommand(cmd, dbConn);

            dbCmd.Parameters.AddWithValue("@ID", UserLoggedIn.ID);
            dbCmd.Parameters.AddWithValue("@LastName", txtLastName.Text);
            dbCmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text);
            dbCmd.Parameters.AddWithValue("@MiddleName", txtMiddleName.Text);
            dbCmd.Parameters.AddWithValue("@Add_Num", txtUnitNum.Text);
            dbCmd.Parameters.AddWithValue("@Add_Street", txtStreet.Text);
            dbCmd.Parameters.AddWithValue("@Add_Brgy", GetBrgySelectedItem());
            dbCmd.Parameters.AddWithValue("@Add_City", GetCitySelectedItem());
            dbCmd.Parameters.AddWithValue("@MobileNumber", txtMobileNumber.Text);
            dbCmd.Parameters.AddWithValue("@Gender", GetGenderSelectedItem());

            dbConn.Open();
            dbCmd.ExecuteNonQuery();
            dbConn.Close();
            ChangeForeColorOfStatusMsg(Color.MediumSeaGreen);
            ChangeTextOfStatusMsg("All changes have been saved! This window will close itself after two seconds.");
            Thread.Sleep(2000);
            CloseForm();
        }
        catch (Exception)
        {
            ChangeForeColorOfStatusMsg(Color.Crimson);
            ChangeTextOfStatusMsg("Something went wrong while we were connecting to our database. Please try again later.");
            hasFinishedEditting = false;
        }
    }

This method will be done on a separate thread, when the user updates his profile information.

UserLoggedIn is actually a field of a User class (a class that defines a row in my table), which stores all the info of the user who's currently logged in.

When I run this, it does not produce any exceptions or errors. But when I check my table, the values are not updated.

I copy-pasted these codes from the registration form (which works) that I made with this system, and modified it into an UPDATE cmd than an INSERT cmd.

I also made Change Username and Password Forms that use the same cmd as shown below:

    public void ChangePass()
    {
        try
        {
            ChangeForeColorOfMsg(Color.Silver);
            ChangeTextOfMsg("Changing password...");

            const string cmd = "update Users set Pass=@Pass where ID=@ID";
            var dbConn = new OleDbConnection(cs);
            var dbCmd = new OleDbCommand(cmd, dbConn);

            dbCmd.Parameters.AddWithValue("@Pass", txtNewPass.Text);
            dbCmd.Parameters.AddWithValue("@ID", UserLoggedIn.ID);

            dbConn.Open();
            dbCmd.ExecuteNonQuery();
            dbConn.Close();
            ChangeTextOfMsg("Password successfully changed!");
        }
        catch (Exception)
        {
            ChangeForeColorOfMsg(Color.Silver);
            ChangeTextOfMsg("A problem occurred. Please try again later.");
        }
    }

And these codes work for me. So I'm really confused right now as to why this update cmd for the profile information isn't working... Is there something I'm not seeing here?

Steve · Accepted Answer · 2015-03-14 16:45:55Z

1

OleDb cannot recognize parameters by their name. It follows a strictly positional order when sending them to your database for updates. In your code above the first parameter is the @ID but this parameter is used last in your query. Thus everything is messed up.

You just need to move the add of the @ID parameter as last in the collection

As a side note, you should be very careful with AddWithValue. It is an handy shortcut, but it has a dark side that could result in wrong queries.
Take a look at

Can we stop using AddWithValue already?

answered Mar 14, 2015 at 16:45

Steve

217k22 gold badges242 silver badges296 bronze badges

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

1 Comment

Neil Petterson Over a year ago

Thank you! I'm reading the blog right now. And oh, the code works now. Thanks again!

Collectives™ on Stack Overflow

C# - New Values not reflecting on Access Database using UPDATE SQL cmd

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related