Unable to Update Record in Access Database Using C# and OleDb

Question

Hello,

I am working on a Windows Forms application using C# and the .NET Framework, and I have encountered an issue when trying to update a record in an Access database. I'm using OleDb to connect to the Access database, and I can successfully insert new records, but when I try to update an existing record, it fails, and no rows are affected.

Here's my database schema for the MedicalHistory table:

• • Username (Text): Primary Key, Indexed with No Duplicates
• Age (Number)
• Gender (Text)
• Allergies (Text)
• ChronicConditions (Text)

Here's the C# code I'm using to update the record:

      private void ButtonSubmit_Click(object sender, EventArgs e)
        {
            string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Guest H\\OneDrive - Farnborough College of Technology\\Health Advice COLLEGE PROJECT\\WeatherHealthAdviser\\HealthAppData.accdb";
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    // Check if the record already exists
                    string selectQuery = "SELECT COUNT(*) FROM MedicalHistory WHERE Username=@Username";
                    OleDbCommand selectCmd = new OleDbCommand(selectQuery, conn);
                    selectCmd.Parameters.AddWithValue("@Username", TextBoxUsername.Text);
                    int count = (int)selectCmd.ExecuteScalar();
                    MessageBox.Show("Count of records with the specified Username: " + count);
                    string query;
                    // If the record exists, update the existing record
                    if (count > 0)
                    {
                        query = "UPDATE MedicalHistory SET Age=@Age, Gender=@Gender, Allergies=@Allergies, ChronicConditions=@ChronicConditions WHERE Username=@Username";
                    }
                    else
                    {
                        // If the record doesn't exist, insert a new record
                        query = "INSERT INTO MedicalHistory (Username, Age, Gender, Allergies, ChronicConditions) VALUES (@Username, @Age, @Gender, @Allergies, @ChronicConditions)";
                    }
                    using (OleDbCommand cmd = new OleDbCommand(query, conn))
                    {
                        cmd.Parameters.AddWithValue("@Username", TextBoxUsername.Text);
                        cmd.Parameters.AddWithValue("@Age", int.Parse(TextBoxAge.Text));
                        cmd.Parameters.AddWithValue("@Gender", ComboBoxGender.SelectedItem.ToString());
                        cmd.Parameters.AddWithValue("@Allergies", TextBoxAllergies.Text);
                        cmd.Parameters.AddWithValue("@ChronicConditions", TextBoxChronicConditions.Text);
                        int result = cmd.ExecuteNonQuery();
                        if (result > 0)
                        {
                            MessageBox.Show("Medical history saved successfully!");
                        }
                        else
                        {
                            if (count>0)
                            {
                                MessageBox.Show("Error saving medical history. No rows were affected.\n\nQuery: " + query + "\n\nParameters:\n" +
                             "Username: " + TextBoxUsername.Text + "\n" +
                             "Age: " + TextBoxAge.Text + "\n" +
                             "Gender: " + ComboBoxGender.SelectedItem.ToString() + "\n" +
                             "Allergies: " + TextBoxAllergies.Text + "\n" +
                             "ChronicConditions: " + TextBoxChronicConditions.Text);
                            }
                            else
                            {
                                MessageBox.Show("Error inserting medical history. No rows were affected.\n\nQuery: " + query + "\n\nParameters:\n" +
                             "Username: " + TextBoxUsername.Text + "\n" +
                             "Age: " + TextBoxAge.Text + "\n" +
                             "Gender: " + ComboBoxGender.SelectedItem.ToString() + "\n" +
                             "Allergies: " + TextBoxAllergies.Text + "\n" +
                             "ChronicConditions: " + TextBoxChronicConditions.Text);
                             }
                        }
                    }
                }
                catch (OleDbException oleEx)
                {
                    MessageBox.Show("OleDb Error: " + oleEx.Message);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error: " + ex.Message);
                }
            }
        }

I've tested the same UPDATE query directly in Access, and it works as expected. However, when executed through the C# code, it doesn't update the record and no rows are affected.

I've already tried the following:

• Checked the connection string and ensured the OleDb library is installed correctly
• Simplified the UPDATE query to only update one field
• Changed the order of the parameters to match the order in the SQL statement

None of these attempts resolved the issue.

Could anyone please help me understand what might be causing the problem, or suggest any additional steps I can take to troubleshoot and fix the issue?

Note: I've been using GPT-4 to help me code & debug this but it eventually suggested I turn to the developer forums (I've never experienced that!). Also thought Id get it to write me joke about my problem, here you go.

Why did the developer turn to ChatGPT for help with their code? Because they thought it was a "GPT" (Great Programming Teacher)! But when ChatGPT said to ask other devs, the developer couldn't help but laugh, realizing that even the Great Programming Teacher sometimes needs a "human touch" in debugging! 😄

Answer 1

I fixed it!
I had to modify the part of my code where I create the OleDbCommand and add the parameters like this:

using (OleDbCommand cmd = new OleDbCommand(query, conn))
{
    if (count > 0) // Update query
    {
        cmd.Parameters.AddWithValue("@Age", int.Parse(TextBoxAge.Text));
        cmd.Parameters.AddWithValue("@Gender", ComboBoxGender.SelectedItem.ToString());
        cmd.Parameters.AddWithValue("@Allergies", TextBoxAllergies.Text);
        cmd.Parameters.AddWithValue("@ChronicConditions", TextBoxChronicConditions.Text);
        cmd.Parameters.AddWithValue("@Username", TextBoxUsername.Text);
    }
    else // Insert query
    {
        cmd.Parameters.AddWithValue("@Username", TextBoxUsername.Text);
        cmd.Parameters.AddWithValue("@Age", int.Parse(TextBoxAge.Text));
        cmd.Parameters.AddWithValue("@Gender", ComboBoxGender.SelectedItem.ToString());
        cmd.Parameters.AddWithValue("@Allergies", TextBoxAllergies.Text);
        cmd.Parameters.AddWithValue("@ChronicConditions", TextBoxChronicConditions.Text);
    }
    // The rest of my code
}  

Thought this was amusing:

Answer 2

You should using a int/number for the primary key rather than Username (Text): Primary Key, Indexed with No Duplicates which can easily be added and used in the WHERE condition.

In regards to Changed the order of the parameters to match the order in the SQL statement Access parameters are positional unlike SQL-Server which the order does not matter.

A simple example

public bool Update(int primaryKey, string description)
{

    using (var cn = new OleDbConnection { ConnectionString = ConnectionString })
    {
        using (var cmd = new OleDbCommand { Connection = cn })
        {
            cmd.CommandText =
                "UPDATE Pictures SET Description = @Description " +
                "WHERE Identifier = @Identifier";

            var descriptionParameter = new OleDbParameter
            {
                DbType = DbType.String,
                ParameterName = "@Description",
                Value = description
            };

            cmd.Parameters.Add(descriptionParameter);

            var identifierParameter = new OleDbParameter
            {
                DbType = DbType.String,
                ParameterName = "@Identifier",
                Value = primaryKey
            };

            cmd.Parameters.Add(identifierParameter);

            try
            {
                cn.Open();

                int affected = cmd.ExecuteNonQuery();

                if (affected == 1)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                // log error
                return false;
            }

        }
    }

    return false;
}