excel sheet dirty read and dirty write?
 

Hello,

I use ado.net to write certain cell in an excel sheet and then read
some value back. The read value is calculated based on the written
cell.
I can write to the sheet without any exception thrown, but when I try
to read it back, the value I read is the old one, not the value it
should be (the value should be calculated based on the written value I
provided).

Strange thing is that if I put a break point between read and write,
when my code breaks, I just open the sheet using excel (I can see the
values have been updated) and click save in excel. Then the value I
read will contain correct written values.

So I guess it is some dirty read or dirty write thing.

Can someone help me?

Many Thanks


My code to write and read excel :
DbProviderFactory factory =
DbProviderFactories.GetFactory("System.Data.OleDb" );
FinanceQuoteResponse response=null;
using (DbConnection conn = factory.CreateConnection())
{
conn.ConnectionString = this.ConnString;

using (DbCommand command = conn.CreateCommand())
{
//first update value
StringBuilder sb = new StringBuilder("update
[LP Calculator APR$] set ");
sb.AppendFormat("{0}={1},", "G3",
request.TotalPrice);
sb.AppendFormat("{0}={1},", "E4",
request.Deposit);
sb.AppendFormat("{0}={1},", "G6",
request.FinanceQuoteDetail.DocumentationFee);
sb.AppendFormat("{0}={1},", "G7",
request.FinanceQuoteDetail.Apr);
sb.AppendFormat("{0}={1},", "G11",
request.FinanceQuoteDetail.OptionToPurchaseFee);
sb.AppendFormat("{0}={1}", "G12",
request.FinanceQuoteDetail.CreditFacilityFee);


command.CommandText = sb.ToString();

conn.Open();
command.ExecuteNonQuery();
//now calculate
command.CommandText = "SELECT * FROM [LP
Calculator APR$E5:E27]";

DbDataAdapter adapter =
factory.CreateDataAdapter();

adapter.SelectCommand = command;

DataSet output = new DataSet();

adapter.Fill(output);
foreach (DataRow row in output.Tables[0].Rows)
{
//FinanceQuoteResponse response=new
FinanceQuoteResponse
}

}



Jerry

excel sheet dirty read and dirty write?
 

Hi Jerry,

I comparable situations I always set Excel to Manual calculation and issue a Calculate from the macro after writing to the work
book. Then I can read the calculated data from code immediately following the Calculate.

I've seen some complaints here that the code might continue while calculation is still in progress. I've never experienced that
myself. Maybe in those cases calculation was set to Manual too, but I don't know.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"DAXU" wrote in message ...
| Hello,
|
| I use ado.net to write certain cell in an excel sheet and then read
| some value back. The read value is calculated based on the written
| cell.
| I can write to the sheet without any exception thrown, but when I try
| to read it back, the value I read is the old one, not the value it
| should be (the value should be calculated based on the written value I
| provided).
|
| Strange thing is that if I put a break point between read and write,
| when my code breaks, I just open the sheet using excel (I can see the
| values have been updated) and click save in excel. Then the value I
| read will contain correct written values.
|
| So I guess it is some dirty read or dirty write thing.
|
| Can someone help me?
|
| Many Thanks
|
|
| My code to write and read excel :
| DbProviderFactory factory =
| DbProviderFactories.GetFactory("System.Data.OleDb" );
| FinanceQuoteResponse response=null;
| using (DbConnection conn = factory.CreateConnection())
| {
| conn.ConnectionString = this.ConnString;
|
| using (DbCommand command = conn.CreateCommand())
| {
| //first update value
| StringBuilder sb = new StringBuilder("update
| [LP Calculator APR$] set ");
| sb.AppendFormat("{0}={1},", "G3",
| request.TotalPrice);
| sb.AppendFormat("{0}={1},", "E4",
| request.Deposit);
| sb.AppendFormat("{0}={1},", "G6",
| request.FinanceQuoteDetail.DocumentationFee);
| sb.AppendFormat("{0}={1},", "G7",
| request.FinanceQuoteDetail.Apr);
| sb.AppendFormat("{0}={1},", "G11",
| request.FinanceQuoteDetail.OptionToPurchaseFee);
| sb.AppendFormat("{0}={1}", "G12",
| request.FinanceQuoteDetail.CreditFacilityFee);
|
|
| command.CommandText = sb.ToString();
|
| conn.Open();
| command.ExecuteNonQuery();
| //now calculate
| command.CommandText = "SELECT * FROM [LP
| Calculator APR$E5:E27]";
|
| DbDataAdapter adapter =
| factory.CreateDataAdapter();
|
| adapter.SelectCommand = command;
|
| DataSet output = new DataSet();
|
| adapter.Fill(output);
| foreach (DataRow row in output.Tables[0].Rows)
| {
| //FinanceQuoteResponse response=new
| FinanceQuoteResponse
| }
|
| }
|
|
|
| Jerry

excel sheet dirty read and dirty write?
 

Hi,

I just tried to update one cell and read that cell back directly. But
still not working.
The data I read back is still the old one. If I put a break point
between update and read and open excel and click save. Then I can read
the data I just updated.

So so strange.

Many Thanks

Jerry

On 21 Feb, 09:58, "Niek Otten" wrote:
Hi Jerry,

I comparable situations I always set Excel to Manual calculation and issue a Calculate from the macro after writing to the work
book. Then I can read the calculated data from code immediately following the Calculate.

I've seen some complaints here that the code might continue while calculation is still in progress. I've never experienced that
myself. Maybe in those cases calculation was set to Manual too, but I don't know.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"DAXU" wrote in ...

| Hello,
|
| I use ado.net to write certain cell in an excel sheet and then read
| some value back. The read value is calculated based on the written
| cell.
| I can write to the sheet without any exception thrown, but when I try
| to read it back, the value I read is the old one, not the value it
| should be (the value should be calculated based on the written value I
| provided).
|
| Strange thing is that if I put a break point between read and write,
| when my code breaks, I just open the sheet using excel (I can see the
| values have been updated) and click save in excel. Then the value I
| read will contain correct written values.
|
| So I guess it is some dirty read or dirty write thing.
|
| Can someone help me?
|
| Many Thanks
|
|
| My code to write and read excel :
| DbProviderFactory factory =
| DbProviderFactories.GetFactory("System.Data.OleDb" );
| FinanceQuoteResponse response=null;
| using (DbConnection conn = factory.CreateConnection())
| {
| conn.ConnectionString = this.ConnString;
|
| using (DbCommand command = conn.CreateCommand())
| {
| //first update value
| StringBuilder sb = new StringBuilder("update
| [LP Calculator APR$] set ");
| sb.AppendFormat("{0}={1},", "G3",
| request.TotalPrice);
| sb.AppendFormat("{0}={1},", "E4",
| request.Deposit);
| sb.AppendFormat("{0}={1},", "G6",
| request.FinanceQuoteDetail.DocumentationFee);
| sb.AppendFormat("{0}={1},", "G7",
| request.FinanceQuoteDetail.Apr);
| sb.AppendFormat("{0}={1},", "G11",
| request.FinanceQuoteDetail.OptionToPurchaseFee);
| sb.AppendFormat("{0}={1}", "G12",
| request.FinanceQuoteDetail.CreditFacilityFee);
|
|
| command.CommandText = sb.ToString();
|
| conn.Open();
| command.ExecuteNonQuery();
| //now calculate
| command.CommandText = "SELECT * FROM [LP
| Calculator APR$E5:E27]";
|
| DbDataAdapter adapter =
| factory.CreateDataAdapter();
|
| adapter.SelectCommand = command;
|
| DataSet output = new DataSet();
|
| adapter.Fill(output);
| foreach (DataRow row in output.Tables[0].Rows)
| {
| //FinanceQuoteResponse response=new
| FinanceQuoteResponse
| }
|
| }
|
|
|
| Jerry

excel sheet dirty read and dirty write?
 

Post your code.

In the meantime, follow this link for a checklist "not calculating":

http://xldynamic.com/source/xld.xlFAQ0024.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"DAXU" wrote in message ...
| Hi,
|
| I just tried to update one cell and read that cell back directly. But
| still not working.
| The data I read back is still the old one. If I put a break point
| between update and read and open excel and click save. Then I can read
| the data I just updated.
|
| So so strange.
|
| Many Thanks
|
| Jerry
|
| On 21 Feb, 09:58, "Niek Otten" wrote:
| Hi Jerry,
|
| I comparable situations I always set Excel to Manual calculation and issue a Calculate from the macro after writing to the
work
| book. Then I can read the calculated data from code immediately following the Calculate.
|
| I've seen some complaints here that the code might continue while calculation is still in progress. I've never experienced
that
| myself. Maybe in those cases calculation was set to Manual too, but I don't know.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "DAXU" wrote in ...
|
| | Hello,
| |
| | I use ado.net to write certain cell in an excel sheet and then read
| | some value back. The read value is calculated based on the written
| | cell.
| | I can write to the sheet without any exception thrown, but when I try
| | to read it back, the value I read is the old one, not the value it
| | should be (the value should be calculated based on the written value I
| | provided).
| |
| | Strange thing is that if I put a break point between read and write,
| | when my code breaks, I just open the sheet using excel (I can see the
| | values have been updated) and click save in excel. Then the value I
| | read will contain correct written values.
| |
| | So I guess it is some dirty read or dirty write thing.
| |
| | Can someone help me?
| |
| | Many Thanks
| |
| |
| | My code to write and read excel :
| | DbProviderFactory factory =
| | DbProviderFactories.GetFactory("System.Data.OleDb" );
| | FinanceQuoteResponse response=null;
| | using (DbConnection conn = factory.CreateConnection())
| | {
| | conn.ConnectionString = this.ConnString;
| |
| | using (DbCommand command = conn.CreateCommand())
| | {
| | //first update value
| | StringBuilder sb = new StringBuilder("update
| | [LP Calculator APR$] set ");
| | sb.AppendFormat("{0}={1},", "G3",
| | request.TotalPrice);
| | sb.AppendFormat("{0}={1},", "E4",
| | request.Deposit);
| | sb.AppendFormat("{0}={1},", "G6",
| | request.FinanceQuoteDetail.DocumentationFee);
| | sb.AppendFormat("{0}={1},", "G7",
| | request.FinanceQuoteDetail.Apr);
| | sb.AppendFormat("{0}={1},", "G11",
| | request.FinanceQuoteDetail.OptionToPurchaseFee);
| | sb.AppendFormat("{0}={1}", "G12",
| | request.FinanceQuoteDetail.CreditFacilityFee);
| |
| |
| | command.CommandText = sb.ToString();
| |
| | conn.Open();
| | command.ExecuteNonQuery();
| | //now calculate
| | command.CommandText = "SELECT * FROM [LP
| | Calculator APR$E5:E27]";
| |
| | DbDataAdapter adapter =
| | factory.CreateDataAdapter();
| |
| | adapter.SelectCommand = command;
| |
| | DataSet output = new DataSet();
| |
| | adapter.Fill(output);
| | foreach (DataRow row in output.Tables[0].Rows)
| | {
| | //FinanceQuoteResponse response=new
| | FinanceQuoteResponse
| | }
| |
| | }
| |
| |
| |
| | Jerry
|

excel sheet dirty read and dirty write?
 

On 21 Feb, 10:17, "Niek Otten" wrote:
Post your code.

In the meantime, follow this link for a checklist "not calculating":

http://xldynamic.com/source/xld.xlFAQ0024.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"DAXU" wrote in ...

| Hi,
|
| I just tried to update one cell and read that cell back directly. But
| still not working.
| The data I read back is still the old one. If I put a break point
| between update and read and open excel and click save. Then I can read
| the data I just updated.
|
| So so strange.
|
| Many Thanks
|
| Jerry
|
| On 21 Feb, 09:58, "Niek Otten" wrote:
| Hi Jerry,
|
| I comparable situations I always set Excel to Manual calculation and issue a Calculate from the macro after writing to the
work
| book. Then I can read the calculated data from code immediately following the Calculate.
|
| I've seen some complaints here that the code might continue while calculation is still in progress. I've never experienced
that
| myself. Maybe in those cases calculation was set to Manual too, but I don't know.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "DAXU" wrote in ...
|
| | Hello,
| |
| | I use ado.net to write certain cell in an excel sheet and then read
| | some value back. The read value is calculated based on the written
| | cell.
| | I can write to the sheet without any exception thrown, but when I try
| | to read it back, the value I read is the old one, not the value it
| | should be (the value should be calculated based on the written value I
| | provided).
| |
| | Strange thing is that if I put a break point between read and write,
| | when my code breaks, I just open the sheet using excel (I can see the
| | values have been updated) and click save in excel. Then the value I
| | read will contain correct written values.
| |
| | So I guess it is some dirty read or dirty write thing.
| |
| | Can someone help me?
| |
| | Many Thanks
| |
| |
| | My code to write and read excel :
| | DbProviderFactory factory =
| | DbProviderFactories.GetFactory("System.Data.OleDb" );
| | FinanceQuoteResponse response=null;
| | using (DbConnection conn = factory.CreateConnection())
| | {
| | conn.ConnectionString = this.ConnString;
| |
| | using (DbCommand command = conn.CreateCommand())
| | {
| | //first update value
| | StringBuilder sb = new StringBuilder("update
| | [LP Calculator APR$] set ");
| | sb.AppendFormat("{0}={1},", "G3",
| | request.TotalPrice);
| | sb.AppendFormat("{0}={1},", "E4",
| | request.Deposit);
| | sb.AppendFormat("{0}={1},", "G6",
| | request.FinanceQuoteDetail.DocumentationFee);
| | sb.AppendFormat("{0}={1},", "G7",
| | request.FinanceQuoteDetail.Apr);
| | sb.AppendFormat("{0}={1},", "G11",
| | request.FinanceQuoteDetail.OptionToPurchaseFee);
| | sb.AppendFormat("{0}={1}", "G12",
| | request.FinanceQuoteDetail.CreditFacilityFee);
| |
| |
| | command.CommandText = sb.ToString();
| |
| | conn.Open();
| | command.ExecuteNonQuery();
| | //now calculate
| | command.CommandText = "SELECT * FROM [LP
| | Calculator APR$E5:E27]";
| |
| | DbDataAdapter adapter =
| | factory.CreateDataAdapter();
| |
| | adapter.SelectCommand = command;
| |
| | DataSet output = new DataSet();
| |
| | adapter.Fill(output);
| | foreach (DataRow row in output.Tables[0].Rows)
| | {
| | //FinanceQuoteResponse response=new
| | FinanceQuoteResponse
| | }
| |
| | }
| |
| |
| |
| | Jerry
|

Hi Neik,

My code is already posted.

excel sheet dirty read and dirty write?
 

Sorry about that! I should have read better.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"DAXU" wrote in message ...
| On 21 Feb, 10:17, "Niek Otten" wrote:
| Post your code.
|
| In the meantime, follow this link for a checklist "not calculating":
|
| http://xldynamic.com/source/xld.xlFAQ0024.html
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "DAXU" wrote in ...
|
| | Hi,
| |
| | I just tried to update one cell and read that cell back directly. But
| | still not working.
| | The data I read back is still the old one. If I put a break point
| | between update and read and open excel and click save. Then I can read
| | the data I just updated.
| |
| | So so strange.
| |
| | Many Thanks
| |
| | Jerry
| |
| | On 21 Feb, 09:58, "Niek Otten" wrote:
| | Hi Jerry,
| |
| | I comparable situations I always set Excel to Manual calculation and issue a Calculate from the macro after writing to the
| work
| | book. Then I can read the calculated data from code immediately following the Calculate.
| |
| | I've seen some complaints here that the code might continue while calculation is still in progress. I've never experienced
| that
| | myself. Maybe in those cases calculation was set to Manual too, but I don't know.
| |
| | --
| | Kind regards,
| |
| | Niek Otten
| | Microsoft MVP - Excel
| |
| | "DAXU" wrote in ...
| |
| | | Hello,
| | |
| | | I use ado.net to write certain cell in an excel sheet and then read
| | | some value back. The read value is calculated based on the written
| | | cell.
| | | I can write to the sheet without any exception thrown, but when I try
| | | to read it back, the value I read is the old one, not the value it
| | | should be (the value should be calculated based on the written value I
| | | provided).
| | |
| | | Strange thing is that if I put a break point between read and write,
| | | when my code breaks, I just open the sheet using excel (I can see the
| | | values have been updated) and click save in excel. Then the value I
| | | read will contain correct written values.
| | |
| | | So I guess it is some dirty read or dirty write thing.
| | |
| | | Can someone help me?
| | |
| | | Many Thanks
| | |
| | |
| | | My code to write and read excel :
| | | DbProviderFactory factory =
| | | DbProviderFactories.GetFactory("System.Data.OleDb" );
| | | FinanceQuoteResponse response=null;
| | | using (DbConnection conn = factory.CreateConnection())
| | | {
| | | conn.ConnectionString = this.ConnString;
| | |
| | | using (DbCommand command = conn.CreateCommand())
| | | {
| | | //first update value
| | | StringBuilder sb = new StringBuilder("update
| | | [LP Calculator APR$] set ");
| | | sb.AppendFormat("{0}={1},", "G3",
| | | request.TotalPrice);
| | | sb.AppendFormat("{0}={1},", "E4",
| | | request.Deposit);
| | | sb.AppendFormat("{0}={1},", "G6",
| | | request.FinanceQuoteDetail.DocumentationFee);
| | | sb.AppendFormat("{0}={1},", "G7",
| | | request.FinanceQuoteDetail.Apr);
| | | sb.AppendFormat("{0}={1},", "G11",
| | | request.FinanceQuoteDetail.OptionToPurchaseFee);
| | | sb.AppendFormat("{0}={1}", "G12",
| | | request.FinanceQuoteDetail.CreditFacilityFee);
| | |
| | |
| | | command.CommandText = sb.ToString();
| | |
| | | conn.Open();
| | | command.ExecuteNonQuery();
| | | //now calculate
| | | command.CommandText = "SELECT * FROM [LP
| | | Calculator APR$E5:E27]";
| | |
| | | DbDataAdapter adapter =
| | | factory.CreateDataAdapter();
| | |
| | | adapter.SelectCommand = command;
| | |
| | | DataSet output = new DataSet();
| | |
| | | adapter.Fill(output);
| | | foreach (DataRow row in output.Tables[0].Rows)
| | | {
| | | //FinanceQuoteResponse response=new
| | | FinanceQuoteResponse
| | | }
| | |
| | | }
| | |
| | |
| | |
| | | Jerry
| |
|
| Hi Neik,
|
| My code is already posted.

excel sheet dirty read and dirty write?
 

On 21 Feb, 11:19, "Niek Otten" wrote:
Sorry about that! I should have read better.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"DAXU" wrote in ...

| On 21 Feb, 10:17, "Niek Otten" wrote:
| Post your code.
|
| In the meantime, follow this link for a checklist "not calculating":
|
| http://xldynamic.com/source/xld.xlFAQ0024.html
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "DAXU" wrote in ...
|
| | Hi,
| |
| | I just tried to update one cell and read that cell back directly. But
| | still not working.
| | The data I read back is still the old one. If I put a break point
| | between update and read and open excel and click save. Then I can read
| | the data I just updated.
| |
| | So so strange.
| |
| | Many Thanks
| |
| | Jerry
| |
| | On 21 Feb, 09:58, "Niek Otten" wrote:
| | Hi Jerry,
| |
| | I comparable situations I always set Excel to Manual calculation and issue a Calculate from the macro after writing to the
| work
| | book. Then I can read the calculated data from code immediately following the Calculate.
| |
| | I've seen some complaints here that the code might continue while calculation is still in progress. I've never experienced
| that
| | myself. Maybe in those cases calculation was set to Manual too, but I don't know.
| |
| | --
| | Kind regards,
| |
| | Niek Otten
| | Microsoft MVP - Excel
| |
| | "DAXU" wrote in ...
| |
| | | Hello,
| | |
| | | I use ado.net to write certain cell in an excel sheet and then read
| | | some value back. The read value is calculated based on the written
| | | cell.
| | | I can write to the sheet without any exception thrown, but when I try
| | | to read it back, the value I read is the old one, not the value it
| | | should be (the value should be calculated based on the written value I
| | | provided).
| | |
| | | Strange thing is that if I put a break point between read and write,
| | | when my code breaks, I just open the sheet using excel (I can see the
| | | values have been updated) and click save in excel. Then the value I
| | | read will contain correct written values.
| | |
| | | So I guess it is some dirty read or dirty write thing.
| | |
| | | Can someone help me?
| | |
| | | Many Thanks
| | |
| | |
| | | My code to write and read excel :
| | | DbProviderFactory factory =
| | | DbProviderFactories.GetFactory("System.Data.OleDb" );
| | | FinanceQuoteResponse response=null;
| | | using (DbConnection conn = factory.CreateConnection())
| | | {
| | | conn.ConnectionString = this.ConnString;
| | |
| | | using (DbCommand command = conn.CreateCommand())
| | | {
| | | //first update value
| | | StringBuilder sb = new StringBuilder("update
| | | [LP Calculator APR$] set ");
| | | sb.AppendFormat("{0}={1},", "G3",
| | | request.TotalPrice);
| | | sb.AppendFormat("{0}={1},", "E4",
| | | request.Deposit);
| | | sb.AppendFormat("{0}={1},", "G6",
| | | request.FinanceQuoteDetail.DocumentationFee);
| | | sb.AppendFormat("{0}={1},", "G7",
| | | request.FinanceQuoteDetail.Apr);
| | | sb.AppendFormat("{0}={1},", "G11",
| | | request.FinanceQuoteDetail.OptionToPurchaseFee);
| | | sb.AppendFormat("{0}={1}", "G12",
| | | request.FinanceQuoteDetail.CreditFacilityFee);
| | |
| | |
| | | command.CommandText = sb.ToString();
| | |
| | | conn.Open();
| | | command.ExecuteNonQuery();
| | | //now calculate
| | | command.CommandText = "SELECT * FROM [LP
| | | Calculator APR$E5:E27]";
| | |
| | | DbDataAdapter adapter =
| | | factory.CreateDataAdapter();
| | |
| | | adapter.SelectCommand = command;
| | |
| | | DataSet output = new DataSet();
| | |
| | | adapter.Fill(output);
| | | foreach (DataRow row in output.Tables[0].Rows)
| | | {
| | | //FinanceQuoteResponse response=new
| | | FinanceQuoteResponse
| | | }
| | |
| | | }
| | |
| | |
| | |
| | | Jerry
| |
|
| Hi Neik,
|
| My code is already posted.

Hi,
Can anyone help?