I am trying to update a single cell of an Excel sheet. Note, in my example code below I am using variables for the column and row because this will change according to other parts of my program, but for debugging purposes I have set them to A and 1 respectively.
Right now I am getting an error saying "Syntax error (missing operator) in query expression '06:31 PM'.
Please don't simply suggest I parameterize because I know this will not fix my problem and I am not worried about injection.
Thanks in advance everyone.
string newColumn = "A";
string newRow = "1";
string worksheet2 = strMonth;
var cnnStr2 = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"", textBox1.Text);
var cnn2 = new OleDbConnection(cnnStr2);
cnn2.Open();
string sql2 = String.Format("UPDATE [{0}$] SET {1}{2}={3}", worksheet2, newColumn, newRow, dtpTime.Text.Substring(0, 8));
OleDbCommand objCmdSelect = new OleDbCommand(sql2, cnn2);
objCmdSelect.ExecuteNonQuery();
cnn2.Close();
EDIT: Based on the link Gerhard suggested, I have changed my command text to the following:
string sql2 = String.Format("UPDATE [{0}${1}{2}:{1}{2}] SET F1='{3}'", worksheet2, newColumn, newRow, dtpTime.Text.Substring(0, 8));
This now produces the error "Operation must use an updateable query." Not sure what to do now. The {1}{2}:{1}{2} gives the single cell range on sheet {0}$ and F1 is the default column name given if there are no column names which I have specified in my connection string with HDR=NO. Lastly the {3} is the substring which I am updating the cell with and is in single quotes simply because that's what everyone seems to do. If I remove the single quotes it just produces a different error.
My edited command string is correct. However to get rid of the new error, I had to take out "IMEX = 1" in the connection string. Not 100% sure why this makes it work, but it does.
One thing I've noticed while looking up things about using oledb commands is that many, like me, seem to be confused with what each part of the command is really doing. I still don't fully understand some of the special things you can add to it like "WHERE", but I'd like to add a bit here to help anyone who finds this question and is having similar problems.
In a SELECT statement, if you wanted to select a single cell, you could use something like this:
string commandString = String.Format("SELECT * FROM [{0}${1}{2}:{1}{2}]", worksheetName, column, row);
"SELECT * FROM" will select everything from "worksheetName". You then have to follow the name of your worksheet with "$". Next, "{1}{2}:{1}{2}" is the range of cells which you are selecting. Here, by making the column and row on both sides of the colon the same, you are selecting a range of a single cell. For example, "E3:E3" would select only what is in cell E3.
In an UPDATE statement, if you wanted to update a single cell, you could use something like:
string commandString = String.Format("UPDATE [{0}${1}{2}:{1}{2}] SET F1='{3}'",worksheetName, column, row, data);
The only difference to note here is the "SET F1='{3}'". This will set the cell you specified in your range to what's in the data variable. Note the use of "F1". This is simply a default that is used when "HDR=NO" is specified in your connection string, in other words, there are no column headers or names. If you had column headers you would use that in place of F1. It is important to understand here that F1 does NOT correspond to cell F1 in any way.
Im spent a weekend with this problem. Finally, i could resolve. I changed the connection string to:
Microsoft.Jet.OLEDB.4.0;Data Source='path to file';Extended Properties='Excel 8.0'
WITH OUT IMEX or HDR options.
Previously, I checked and changed the cellprotection check in excel file. I hope this help others.
