Hi,

I finally figured out how to change a cell's value using ado.net.
However, when I read another cell's value back (that cell's value is
calculated based on the value I just updated), the value I got is
still based on the old value (the value before I update the cell).

I opened the sheet in excel and made sure that the value was changed.
Actually the calculated value displayed in excel was correct.

So I am totally confused on what is wrong with my code?
Here is my code:

//update, it actually worked I think
conn.Open();
using (DbCommand command =
conn.CreateCommand())
{
//first update value
command.CommandText = "update [HP
Calculator$G3:G3] set F1=" + quoteRequest.TotalPrice.ToString();
command.ExecuteNonQuery();
//command.CommandText = "insert into [HP
Calculator APR$A2:A2] values ('cccced')";
//i = command.ExecuteNonQuery();
command.CommandText = "update [HP
Calculator$E4:E4] set F1=" + quoteRequest.Deposit.ToString();
command.ExecuteNonQuery();

command.CommandText = "update [HP
Calculator$G6:G6] set F1=" +
quoteRequest.FinanceQuoteDetail.DocumentationFee.T oString();
command.ExecuteNonQuery();

command.CommandText = "update [HP
Calculator$G7:G7] set F1=" +
Convert.ToString(quoteRequest.FinanceQuoteDetail.A pr.Percent / 100);
command.ExecuteNonQuery();

command.CommandText = "update [HP
Calculator$G11:G11] set F1=" +
quoteRequest.FinanceQuoteDetail.OptionToPurchaseFe e.ToString();
command.ExecuteNonQuery();

command.CommandText = "update [HP
Calculator$G12:G12] set F1=" +
quoteRequest.FinanceQuoteDetail.CreditFacilityFee. ToString();
command.ExecuteNonQuery();

}
// read data: e27 should be calculated base on the updated values (but
when I read it back, it still reflects the old value before the
update)
conn.Open();
using (DbCommand command =
conn.CreateCommand())
{
command.CommandText = "SELECT * FROM [HP
Calculator$e27:e27]";
using (DbDataReader dr =
command.ExecuteReader())
{
while (dr.Read())
{
string hello = dr[0].ToString();
}
}
}

Many THanks

Jerry

I have not used ADO.NET, only ADO and DAO.

I suspect that you cannot use ADO.NET to update values in a workbook that is
already opened by Excel, so you are probably updating a stored workbook
file.

But if you are using ADO.NET to update values stored in a closed workbook
then the workbook will not be calculated by Excel until you open it in Excel
(automatic calc mode or press F9 in Manual calc mode).

Why dont you just use automation instead of ADO to tell Excel to update the
cells, recalculate and then read the recalculated values?

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

"DAXU" wrote in message
...
Hi,

I finally figured out how to change a cell's value using ado.net.
However, when I read another cell's value back (that cell's value is
calculated based on the value I just updated), the value I got is
still based on the old value (the value before I update the cell).

I opened the sheet in excel and made sure that the value was changed.
Actually the calculated value displayed in excel was correct.

So I am totally confused on what is wrong with my code?
Here is my code:

//update, it actually worked I think
conn.Open();
using (DbCommand command =
conn.CreateCommand())
{
//first update value
command.CommandText = "update [HP
Calculator$G3:G3] set F1=" + quoteRequest.TotalPrice.ToString();
command.ExecuteNonQuery();
//command.CommandText = "insert into [HP
Calculator APR$A2:A2] values ('cccced')";
//i = command.ExecuteNonQuery();
command.CommandText = "update [HP
Calculator$E4:E4] set F1=" + quoteRequest.Deposit.ToString();
command.ExecuteNonQuery();

command.CommandText = "update [HP
Calculator$G6:G6] set F1=" +
quoteRequest.FinanceQuoteDetail.DocumentationFee.T oString();
command.ExecuteNonQuery();

command.CommandText = "update [HP
Calculator$G7:G7] set F1=" +
Convert.ToString(quoteRequest.FinanceQuoteDetail.A pr.Percent / 100);
command.ExecuteNonQuery();

command.CommandText = "update [HP
Calculator$G11:G11] set F1=" +
quoteRequest.FinanceQuoteDetail.OptionToPurchaseFe e.ToString();
command.ExecuteNonQuery();

command.CommandText = "update [HP
Calculator$G12:G12] set F1=" +
quoteRequest.FinanceQuoteDetail.CreditFacilityFee. ToString();
command.ExecuteNonQuery();

}
// read data: e27 should be calculated base on the updated values (but
when I read it back, it still reflects the old value before the
update)
conn.Open();
using (DbCommand command =
conn.CreateCommand())
{
command.CommandText = "SELECT * FROM [HP
Calculator$e27:e27]";
using (DbDataReader dr =
command.ExecuteReader())
{
while (dr.Read())
{
string hello = dr[0].ToString();
}
}
}

Many THanks

Jerry