Link to home
Start Free TrialLog in
Avatar of Giawa
Giawa

asked on

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

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).
Avatar of Giawa
Giawa

ASKER

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

Avatar of Giawa

ASKER

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

Avatar of Giawa

ASKER

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

Avatar of Rory Archibald
Can you not use B1:B2 (rather than B1:C2) to update B2? 
Avatar of Giawa

ASKER

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.
Avatar of Giawa

ASKER

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.
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!
Avatar of Giawa

ASKER

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?
What kind of error is it showing?
Avatar of Giawa

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Giawa

ASKER

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.
Avatar of Giawa

ASKER

Best answer.  Thanks!