DataGridView1 to update access table

Question

This code is part off a bigger function I hope Ive copied the {} properly balancing them out. I have a dataGridView which has been updated this percedure is simply to write the changes back into a blanked out table?

    OleDbCommand cmd = new OleDbCommand();
        for (int ii = 0; ii < dataGridView1.Rows.Count; ii++)
        {
            DataGridViewRow dr = dataGridView1.Rows[ii];
            if (dr.Selected == true)
            {
                string connetionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\temp\\Set.mdb;Persist Security Info=False";

                try
                {
                    int number = 0; //possible empty statment warning next line
                    using (OleDbConnection connection = new OleDbConnection(connetionString))
                    {
                        sql = "Insert into EnableOne SET Property=@var1, Pvalue=@var2,  Pdefault=@var3, PType=@var4";
                        connection.Open(); //This fail's to compile connection does not exist in the current context
                        using (OleDbCommand cmd1 = new OleDbCommand(sql, connection))//This fail's to compile connection does not exist in the current context
                        {
                            number = dataGridView1.CurrentCell.RowIndex;
                            int numr = 0;


                            numr = dataGridView1.Rows.Count;


                            {


                                for (int x = 0; x < numr; x++)
                                {
                                    cmd1.Parameters.Add(new OleDbParameter("@var1", dataGridView1.Rows[x].Cells[0].Value.ToString()));
                                    cmd1.Parameters.Add(new OleDbParameter("@var2", dataGridView1.Rows[x].Cells[1].Value.ToString()));
                                    cmd1.Parameters.Add(new OleDbParameter("@var3", dataGridView1.Rows[x].Cells[2].Value.ToString()));
                                    cmd1.Parameters.Add(new OleDbParameter("@var4", dataGridView1.Rows[x].Cells[3].Value.ToString()));
                                    cmd1.CommandText = sql;
                                    cmd1.ExecuteNonQuery();
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
        }
    }

The above code produces this error

Error System.Data.OleDb.OleDbException (0x80040E14): Syntax error in INSERT INTO statement.

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at CrewCheifSettingsBeta3.Form1.updater(String table) in

I can see a lot of problems in the code above, but no one can be the source of a compilation problem. If the compiler complains about an undefined connection variable then there is something else that is not apparent from this code. — Steve
Ive added the database error that is produced when running the program @steve — Data

Steve Steve · Accepted Answer · 2017-07-01T20:20:00

There is no apparent problem in the code above that can be the source of a compiler error (as you say in your code comments). But there are a lot of others problems that will be the source of at least two runtime errors and one logical error.

The most important one is the wrong syntax used in the INSERT INTO statement. There is no SET when you use INSERT INTO. (SET is used in the UPDATE statement)

.....
using (OleDbConnection connection = new OleDbConnection(connetionString))
{
    sql = @"Insert into EnableOne (Property,Pvalue,Pdefault,PType)
                           VALUES (@var1,   @var2, @var3,   @var4)";
    connection.Open(); 
    using (OleDbCommand cmd1 = new OleDbCommand(sql, connection))
    {
        number = dataGridView1.CurrentCell.RowIndex;
        int numr = dataGridView1.Rows.Count;
        cmd1.Parameters.Add("@var1", OleDbType.VarWChar);
        cmd1.Parameters.Add("@var2", OleDbType.VarWChar);
        cmd1.Parameters.Add("@var3", OleDbType.VarWChar);
        cmd1.Parameters.Add("@var4", OleDbType.VarWChar);
        for (int x = 0; x < numr; x++)
        {
            cmd1.Parameters["@var1"].Value = dataGridView1.Rows[x].Cells[0].Value.ToString()));
            cmd1.Parameters["@var2"].Value = dataGridView1.Rows[x].Cells[1].Value.ToString()));
            cmd1.Parameters["@var3"].Value = dataGridView1.Rows[x].Cells[2].Value.ToString()));
            cmd1.Parameters["@var4"].Value = dataGridView1.Rows[x].Cells[3].Value.ToString()));

            // Not needed, already set in the constructor....
            //cmd1.CommandText = sql;
            cmd1.ExecuteNonQuery();
        }
    }
}

As you can see there are other changes I have made. I define the parameters outside the loop without giving them a value, when inside the loop I set the value of the parameters and execute the query. In your code, continuously adding parameters to the same command will result in a series of inserts of the same record (the first block of parameters) for each loop.

Another problem is the double loop on the grid Rows. This will cause duplicated record (if you don't have primary keys to protect you) or an error message if you try to insert a duplicate key in your table.

// First loop over the grid rows.....
for (int ii = 0; ii < dataGridView1.Rows.Count; ii++)
....
     // Second loop over the grid rows.
     for (int x = 0; x < numr; x++)

I think you could remove the internal loop and replace the x variable with the ii variable defined in the external loop.

So in general, I suggest to refactor your code following this schema

Create and open the connection in a using statement
Create and prepare the OleDbCommand in a using statement
Create the parameters needed without values
Start your loop over the rows
if the rows is selected 
    Set each parameter with the data from the selected row
    Execute the query

DataGridView1 to update access table

1 Answers