I couldn't update data from excel file from oledb C#

Question

I have a big excel document, so I decided to use an OleDbconneciton to open and edit it. It is not problem when I open it. I just use Select command just like SQL, but when I try to change data from some column, I get an error -> Data type mismatch in criteria expression. That is a sample of update string generator:

 this._updateCommand = string.Format("UPDATE [{0}${1}{2}:{1}{2}] SET F{3} = ?", workSheet, ((DATA_COL)this._cellIndex).ToString(), this._rowIndex, "1");

On export it gets:

"UDPATE [worksheet$B1:B1] SET F1 = ?"

Nextstep:

            Task task = Task.Run(() => {String connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; Mode=ReadWrite; Data Source={0}; Extended Properties='Excel 8.0; HDR=NO; IMEX=3;'", this.WorkBook);
            for (int i = 0; i < this.editCollection.Count; i++)
            {
                OleDbConnection connection = new OleDbConnection(connectionString);
                try
                {                        
                    OleDbCommand command = new OleDbCommand(editCollection[i]._updateCommand, connection);
                    command.Parameters.AddWithValue("@thisData", editCollection[i]._value);
                    command.CommandType = CommandType.Text;
                    connection.Open();
                    command.ExecuteNonQuery();                        
                }                                    
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
            editCollection.Clear();
        });

So, what I did wrong?

Hey Vadim, your posted code looks ok. The problem seems to be some invalid DataType. To figure this out it would be nice to see your update command. You try to pass a value which Excel can't work with or to pass it in an Excel-Cell which has a other Format defined. — Sebi, Feb 27 '17 at 07:08
Hey Sebi. Maybe I used a wrong connection string? According roles, using IMEX parameter in connection string allows to represent any type of data to string data type... — XMIII, Feb 27 '17 at 08:21

score 0 · Answer 1 · edited May 23 '17 at 11:53

you should try changing you IMEX to 2 or 0. So far I didn't see IMEX=3 mentions in the docs. Is it something new or do you have a typo? Here is what the MS doc says about IMEX values:

0 is Export mode - use for writing-to/insertion-into Excel file
1 is Import mode - use for reading from Excel file
2 is Linked mode (full update capabilities)

See this answer for some more detail about IMEX setting for writing to excel: https://stackoverflow.com/a/42413128/5857386 .

HTH

I couldn't update data from excel file from oledb C#

1 Answers1