Giawa

asked on 7/22/2008

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).

DatabasesMicrosoft ExcelC#

Last Comment

Giawa

8/22/2022 - Mon

Giawa

7/22/2008

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

Giawa

7/22/2008

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

Giawa

7/22/2008

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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

Rory Archibald

7/22/2008

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

Giawa

7/22/2008

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.

Giawa

7/22/2008

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.

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Rory Archibald

7/22/2008

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!

Giawa

7/22/2008

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?

Rory Archibald

7/22/2008

What kind of error is it showing?

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

Giawa

7/22/2008

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

Rory Archibald

7/22/2008

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Giawa

7/22/2008

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.

Giawa

7/28/2008

ASKER

Best answer. Thanks!

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.