CODEWITHSUNDEEP
c#ms-access
user2101411
user2101411

Reputation: 1202

OleDb Exception: no value given for one or more required parameters update query

I'm trying to update a record with the values in textboxes that was filled upon row click from DGV with the id. I've checked the database and the fields are correct but it is saying no value is given for one or more required parameters.

Here is my code:

#region adds update control to the form
    public void AddUpdateControl()
    {
        // update and delete controls
        Button updateFieldsButton = new Button()
        {
            Name = "btn_updateFields",

            Text = "Update"
        };

        updateFieldsButton.Font = new Font(updateFieldsButton.Font.FontFamily, 12);

        updateFieldsButton.Location = new Point(78, 648);

        updateFieldsButton.Size = new Size(120, 30);

        updateFieldsButton.Click += (sender, args) =>
        {
            if (MessageBox.Show("Are you sure you want to update this record?", "Update", MessageBoxButtons.OKCancel) == DialogResult.OK)
            {
                UpdateData(this);
            }
        };

        Controls.Add(updateFieldsButton);
    }
    #endregion

#region shows data grid view for update
    public void ShowDataGridViewForUpdate()
    {
        qbcDataGridView.Show();

        qbcDataGridView.Font = new Font(qbcDataGridView.Font.FontFamily, 10);

        qbcDataGridView.Location = new Point(80, 100);

        qbcDataGridView.Size = new Size(1500, 500);


        DataTable dt = new DataTable();


        DbAdapter = new OleDbDataAdapter("select ID, household_head AS head, birthday, phone, email, address, status, " +
            "spouse, spouse_birthday AS sbirthday, spouse_email AS semail, anniversary, spouse_status AS sstatus, " +
            "child1, child1_birthday AS birthday1, child1_email AS email1, " +
            "child2, child2_birthday AS birthday2, child2_email AS email2, " +
            "child3, child3_birthday AS birthday3, child3_email AS email3, " +
            "child4, child4_birthday AS birthday4, child4_email AS email4, " +
            "child5, child5_birthday AS birthday5, child5_email AS email5, " +
            "child6, child6_birthday AS birthday6, child6_email AS email6, " +
            "child7, child7_birthday AS birthday7, child7_email AS email7 from members", dbc);

        DbAdapter.Fill(dt);

        if (dt != null && dt.Rows.Count > 0)
        {
            if (dt.Columns.Count > 0)
            {
                for (int i = dt.Columns.Count - 1; i >= 0; i--)
                {
                    if (dt.AsEnumerable().All(row => row[i] == null || row[i].ToString() == ""))
                    {
                        dt.Columns.RemoveAt(i);
                    }
                }

                qbcDataGridView.DataSource = dt;


                qbcDataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

                qbcDataGridView.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;

                qbcDataGridView.DefaultCellStyle.WrapMode = DataGridViewTriState.True;


                Controls.Add(qbcDataGridView);



                qbcDataGridView.RowHeaderMouseClick += (object sender, DataGridViewCellMouseEventArgs e) =>
                {
                    id = Convert.ToInt32(qbcDataGridView.Rows[e.RowIndex].Cells[0].Value.ToString());

                    // get all the columns and assign them to textboxes text
                    for (int i = 1; i < qbcDataGridView.Rows[e.RowIndex].Cells.Count; i++)
                    {
                         // enable how many textboxes were found based on the dgv 
                         var textBox = new TextBox
                         {
                             Name = qbcDataGridView.Rows[e.RowIndex].Cells[i].Value.ToString(),

                             Font = new Font(qbcDataGridView.Font.FontFamily, 12),

                             Text = qbcDataGridView.Rows[e.RowIndex].Cells[i].Value.ToString(),

                             Location = new Point(80 + (i * 140), 50)
                         };


                         Controls.Add(textBox);
                    }
                }; 
            }
        }
        else
        {
            HideAllControls(this);

            MessageBox.Show("No records exist", "Records Error", MessageBoxButtons.OK);
        }

        dbc.Close();
    }
    #endregion


    #region updates records in the database
    public void UpdateData(Control ctrl)
    {
        try
        {
            foreach (Control c in Controls)
            {
                if (c is TextBox)
                {
                    if (!string.IsNullOrEmpty(((TextBox)c).Text))
                    {
                        updatedTextboxes.Add(new KeyValuePair<string, string>(((TextBox)c).Name, ((TextBox)c).Text));
                    }
                }
            }

            using (dbc)
            {
                dbc.Open();

                dbCmd = new OleDbCommand("UPDATE members SET household_head = ?, birthday = ?, phone = ?, " +
                    "email = ?, address = ?, status = ?, spouse = ?, spouse_birthday = ?, " +
                    "spouse_phone = ?, spouse_email = ?, anniversary = ?, spouse_status = ?, " +
                    "child1 = ?, child1_birthday = ?, child1_email = ?, " +
                    "child2 = ?, child2_birthday = ?, child2_email = ?, " +
                    "child3 = ?, child3_birthday = ?, child3_email = ?, " +
                    "child4 = ?, child4_birthday = ?, child4_email = ?, " +
                    "child5 = ?, child5_birthday = ?, child5_email = ?, " +
                    "child6 = ?, child6_birthday = ?, child6_email = ?, " +
                    "child7 = ?, child7_birthday = ?, child7_email = ? WHERE ID = " + id, dbc);


                for (int i = 0; i < updatedTextboxes.Count; i++)
                {
                    dbCmd.Parameters.AddWithValue(updatedTextboxes[i].Key.ToString(), updatedTextboxes[i].Value);
                }

                if (dbCmd.ExecuteNonQuery() > 0) // RAWR not updating.. wrong parameter value.. check query
                {
                    MessageBox.Show("Record updated", "QBC", MessageBoxButtons.OK);

                    ClearAll(ctrl);
                }
            }
        }
        catch (Exception e)
        {
            MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

        dbc.Close();
    }
    #endregion

The exception screenshot - https://imgur.com/6pstqOH

the application screenshot - https://i.sstatic.net/QDaGa.jpg

the database screenshot (access) - https://i.sstatic.net/QE8Jj.jpg

I'm confused because the insert event does the same thing (grabs values from textboxes and inserts them) without any error.

Am I missing something?

I can try and provide more information if this is unclear

Appreciate the help

Thanks!

Upvotes: 1

Views: 618

Answers (1)

Jonathan Willcock
Jonathan Willcock

Reputation: 5255

You are passing the value property of various TextBoxes. If these TextBoxes have not been set with a valid Text, then the value property will be null. This is a .Net null which is not the same as a database null. To pass a db null you have to set the parameter value to DBNull.Value, otherwise you get precisely the error message you are seeing, a missing parameter error. To avoid this change your for loop to:

for (int i = 0; i < updatedTextboxes.Count; i++)
{
    if (updatedTextboxes[i].Value == null)
    {
        dbCmd.Parameters.AddWithValue(updatedTextboxes[i].Key, DBNull.Value);
    }
    else
    {
        dbCmd.Parameters.AddWithValue(updatedTextboxes[i].Key, updatedTextboxes[i].Value);
    }
}

Edit

In order to restrict your update statement to only those fields for which you have TextBoxes, you need to build your Update statement dynamically. Something along these lines should work:

dbc.Open();
dbCmd = new OleDbCommand();
var sB = new StringBuilder("UPDATE members SET ");

for (int i = 0; i < updatedTextboxes.Count; i++)
{
    if (i == 0)
    {
        sB.Append(updatedTextboxes[i].Key + " = ?");
    }
    else
    {
        sB.Append(", " + updatedTextboxes[i].Key + " = ?");
    }
    if (updatedTextboxes[i].Value == null)
    {
        dbCmd.Parameters.AddWithValue(updatedTextboxes[i].Key, DBNull.Value);
    }
    else
    {
        dbCmd.Parameters.AddWithValue(updatedTextboxes[i].Key, updatedTextboxes[i].Value);
    }
}

sB.Append(" WHERE ID = " + id);
dbCmd.CommandText = sB.ToString();

dbCmd.Connection = dbc;

if (dbCmd.ExecuteNonQuery() > 0) .....

Please note that I have not tested this code! Moreover in order to get it to work, you are going to have to change the Name value of your TextBoxes to the field name of the data. This you can do like so:

var textBox = new TextBox
{
    Name = qbcDataGridView.Columns[i].DataPropertyName,  ...

Note also that you do not need Key.ToString(). You have KeyValuePairs of string, string, so the Key is already a string.

I think these changes should be enough to get it working, but please let me know if it does not! I have deliberately tried to keep the changes to a minimum, so that the code most closely resembles your own. This does not mean, that I would code it like this myself; I am a believer in not smothering beginners' work with huge amounts of red ink!

Upvotes: 1

Related Questions