Oledb excel update is not working

Question

1.00/5 (1 vote)

See more:

In the excel sheet, I have more than 1 lac rows. Am trying to update specific cell and row. It's working and does the update only up to certain row but when I try A106880 row it's saying ' Make sure the object exists and that you spell its name and the path name correctly.'

C#

string sConnection = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=No;IMEX=1""", path);
OleDbConnection _Connection = new OleDbConnection(sConnection);
_Connection.Open();
string cmd = string.Format("Update [{0}$A106880:A106880] set F1 = 12", sheetname);
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand(cmd, connection);
if (myCommand != null) myCommand.ExecuteNonQuery();
_Connection.Close();

Thanks in advance,

Posted 7-Feb-16 17:15pm

kvchennai

Updated 7-Feb-16 17:16pm

v2

Add a Solution

Comments

[no name] 8-Feb-16 0:34am

At which line it is throwing error, please provide details.

kvchennai 8-Feb-16 2:06am

here
myCommand.ExecuteNonQuery();

or if I have update query to update specific cell without using header column. Let say by using row no. like A106880...

kvchennai 8-Feb-16 2:07am

like this "Update [sheetname$] set F5 = 12 where rowno. = 12400"

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2016-02-07T21:26:00

Solution 1

You have two different spellings for your connection object, which one is correct?

C#

OleDbConnection _Connection = new OleDbConnection(sConnection);
_Connection.Open();   // ***** _Connection
string cmd = string.Format("Update [{0}$A106880:A106880] set F1 = 12", sheetname);

System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand(cmd, connection);   // ***** connection

Posted 7-Feb-16 21:26pm

Richard MacCutchan

Comments

kvchennai 8-Feb-16 5:49am

Actually I have copied that code from two functions, that's why spelling mistake.

Just now I got to know that problem with excel limitation. Old version (xls) supports max. 65536 rows and new format (xlsx) support 1048576 rows. Now issue is, even with the new version I couldn't able to pass more than 65536 like [{0}$A65537:A65537] due to limitation but it can be possible when I use 'where' condition. But 'where' takes more time to update doc. Please help me or how to use row no. in 'where' condition instead of column name and value

Richard MacCutchan 8-Feb-16 9:09am

If you have problems with one function then why show code from another? If you expect people to help you, then please ensure you post accurate details of the problem, so we don't waste time with such answers.

As to the addressing problem, I have never tried this so cannot comment further. I would suggest studying the Excel documentation.