excel sheet dirty read and dirty write?

D

DAXU

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
 
N

Niek Otten

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

| 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
 
D

DAXU

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

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


| 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
Click to expand...
 
N

Niek Otten

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

| 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
|
| > 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
| >
| >
| > | 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
|
 
D

DAXU

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


| 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
|
| > 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
| >
| >
| > | 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
|
Click to expand...

Hi Neik,

My code is already posted.
 
N

Niek Otten

Sorry about that! I should have read better.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

DAXU

Sorry about that! I should have read better.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| > 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
| >
| >
| > | 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
| > |
| > | > 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
| > | >
| > | >
| > | > | 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.
Click to expand...

Hi,
Can anyone help?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

No... while trying to update to Excel file 0
Why calculated cell's value not updated when I change cell valueusing ado.net? 1
How to fatch Range's from an excel sheet when the file is on netwo 0
Importing Data from Access Query to Excel Sheet 4
Array to read data from one sheet and write to another if it meets criteria 2
Read Excel data from VB.net using range object - prob with 'undefi 0
run report on company intranet and import to excel 0
The Print # command print not all rows from excel sheet to file 17

Top