We help IT Professionals succeed at work.

How do I fix 'cannot expand named range' exception when using OleDB and C# to insert data into an Excel spreadsheet.

Giawa
Giawa asked
on
4,087 Views
Last Modified: 2013-11-15
My code is generating the following string to insert data into the Excel spreadsheet.  Can you see what is causing the exception "OleDBException" with the message "Cannot expand named range." to be thrown?

INSERT INTO [Sheet1$B2:B2](F1) VALUES(34.5)

I've also tried putting square brackets around F1 like so: INSERT INTO [Sheet1$B2:B2]([F1]) VALUES(34.5)

I am also curious why if I just pass B2: INSERT INTO [Sheet1$B2](F1) VALUES(34.5) I will get an error "Microsoft Jet Database Engine could not find the object 'Sheet1$B2'".

I am using a normal spreadsheet with two columns and many rows.  All of the cells are unlocked and filled with numbers (except the topmost cell of each column, which has a title).
Comment
Watch Question

Author

Commented:
Here's the method in question.  The Logger will return:

Error > InsertQuery exited with error: Cannot expand named range.
Error > Write to Sheet1$B2:B2 failed.

when the method is called as follows:

t_db.WriteDB(34.5, "Sheet1", "B2:B2");
        public bool InsertQuery(string DatabaseAddress, object Value)
        {
            try
            {
                if (o_connection == null) return false;
                SelectQuery(DatabaseAddress);
                DataTable t_table = GetTable();
                o_update = new OleDbCommand(@"INSERT INTO " + DatabaseAddress +
                    "(" + t_table.Columns[0].ColumnName + ") VALUES(" + Value.ToString() + ")", o_connection);
                o_update.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Logger.Instance.WriteLine(WarningLevel.Error, "InsertQuery exited with error: " + e.Message);
                return false;
            }
            return true;
        }

Open in new window

Author

Commented:
Forgot to attach the WriteDB method.  This will basically concatenate Sheet and Position with some formatting.  I think that's all the really relevant code.  GetTable() just gets the cell the user is looking for so that I can get the ColumnName for the INSERT statement.
        public void WriteDB(object Value, string Sheet, string Position)
        {
            if (!e_DB.InsertQuery("[" + Sheet + "$" + Position + "]", Value))
                Logger.Instance.WriteLine(WarningLevel.Error, "Write to " + Sheet +
                    "$" + Position + " failed.");
        }

Open in new window

Author

Commented:
It would seem that I need to read some more, and think about what I am doing.  I didn't need to use INSERT, instead I need to use UPDATE.  Anyways, I got it working.  I'll still toss out points to whoever can answer my question, because I'll probably need INSERT eventually anyways.

Another side question, when using UPDATE I have to pass

$B1:C2 to update cell B2, $B2:C3 to update cell B3, $A1:B2 to update A2 and so on.  Is there a reason for this?


        public bool UpdateQuery(string DatabaseAddress, object Value)
        {
            try
            {
                if (o_connection == null) return false;
                SelectQuery(DatabaseAddress);
                DataTable t_table = GetTable();
                o_update = new OleDbCommand(@"UPDATE " + DatabaseAddress +
                    " SET " + t_table.Columns[0].ColumnName + " = " + Value.ToString(),
                    o_connection);
                o_update.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Logger.Instance.WriteLine(WarningLevel.Error, "InsertQuery exited with error: " + e.Message);
                return false;
            }
            return true;
        }

Open in new window

Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Can you not use B1:B2 (rather than B1:C2) to update B2? 

Author

Commented:
Hi rorya,

You are correct, B1:B2 will update B2.  How would I update B1?  B0 cannot be used, since it doesn't exist.  I've tried B:B1, B0:B1, etc.  Can I just not update the first row?  Thanks for your insight.

Author

Commented:
The reason I was getting the original error was because I was trying to insert a new value into a cell that already contained a value.  OleDB apparently doesn't know how to shift all the cells down to insert a cell in that way, so I have to insert a value into the first empty cell of the column.  This seems to work  :D

Still curious about how to edit the first row of numbers.
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
You can't as far as I know. In order to be able to update, you have to have headers (or Excel has to believe you do). As such, you cannot update the header row. I guess it might be possible to ue ADOX separately to change the field 'names' and update the first row that way, but I wouldn't bother!

Author

Commented:
Okay, sounds good.  My final question is this:

When I use my Insert command and then open my excel file, excel shows errors in the boxes, as it doesn't know if the cells data is a number or a string.  I have tried using

o_update.Parameters.Add("@Value", OleDbType.Double).Value = Value;

But excel still can't tell if it is a number or string...  Any ideas?
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
What kind of error is it showing?

Author

Commented:
There is a small green triangle in the corner of each window that I wrote to.  If I click that cell there is a yellow box and an exclamation mark (warning?) that shows up.  If I click the warning icon a drop-down menu appears which reads:

Number Stored as Text
Convert to Number
Help on this Error
---
Ignore Error
Edit in Formula Bar
---
Error Checking Options
Show Formula Auditing Toolbar
Grand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
At first glance it looks like I'm doing the same thing.  I tried changing

o_update.Parameters.Add("@Value", OleDbType.Double).Value = Value;

to

o_update.Parameters.Add("@Value", OleDbType.Numeric).Value = Value;

as the article suggested, but to no avail.  I'll take another stab at this when I get home from work.  Thanks for your help.

Author

Commented:
Best answer.  Thanks!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.