|
|
You're in the
dotnet ado.net
group:Data is different than in database
dotnet ado.net:
2008). I am reading from a query using DataReader (the same problem happens if I use OLEDBDataAdapter). When I run the query in Access, the value for Cash = 830.004999999999, SC = 1692.5 and Profit = -862.495000000001. Cash = SC + Profit. But when I read the value from the program, this is what I got: Cash = 830.000000000489 SC = 1692.5 Profit = -862.499999999511. Why this difference and how can I fix it ? Thank you. Here are the codes: Dim m_cmd As OleDb.OleDbCommand Dim m_dr As OleDb.OleDbDataReader Dim sSQL As String m_cmd = New OleDb.OleDbCommand With m_cmd .Connection = adoConOLE .CommandText = "select Cash,Profit from myQuery where Account = '123'" End With m_dr = m_cmd.ExecuteReader() If m_dr.Read Then sSQL = sSQL & " " & m_dr.Item("Cash") --> this returns 830.000000000489 instead of 830.004999999999 when I run it from Access sSQL = sSQL & " " & m_dr.Item("SC") --> this returns 1692.5, which is the same as when I run it on Access sSQL = sSQL & " " & m_dr.Item("Profit") --> this returns -862.499999999511 instead of -862.495000000001 when I run it from Access End If
floating point numbers are notorious for being off. This can happen even
when you do not use two different programs (.NET versus Access). Not sure why things are so far off, however. What is the data type in Access? -- Gregory A. Beamer MVP, MCP: +I, SE, SD, DBA Subscribe to my blog http://gregorybeamer.spaces.live.com/lists/feed.rss or just read it: http://gregorybeamer.spaces.live.com/ ************************************************* | Think outside the box! | ************************************************* [quoted text, click to view] "fniles" <fniles@pfmail.com> wrote in message news:%230JvyDPkIHA.5820@TK2MSFTNGP04.phx.gbl... >I am using MS Access and VB .NET 2005 (the same problem happens in VB.NET >2008). > I am reading from a query using DataReader (the same problem happens if I > use OLEDBDataAdapter). > When I run the query in Access, the value for Cash = 830.004999999999, SC > = 1692.5 and Profit = -862.495000000001. > Cash = SC + Profit. > But when I read the value from the program, this is what I got: > Cash = 830.000000000489 > SC = 1692.5 > Profit = -862.499999999511. > Why this difference and how can I fix it ? > Thank you. > > Here are the codes: > Dim m_cmd As OleDb.OleDbCommand > Dim m_dr As OleDb.OleDbDataReader > Dim sSQL As String > m_cmd = New OleDb.OleDbCommand > With m_cmd > .Connection = adoConOLE > .CommandText = "select Cash,Profit from myQuery where Account = > '123'" > End With > m_dr = m_cmd.ExecuteReader() > If m_dr.Read Then > sSQL = sSQL & " " & m_dr.Item("Cash") --> this returns > 830.000000000489 instead of 830.004999999999 when I run it from Access > sSQL = sSQL & " " & m_dr.Item("SC") --> this returns 1692.5, > which is the same as when I run it on Access > sSQL = sSQL & " " & m_dr.Item("Profit") --> this > returns -862.499999999511 instead of -862.495000000001 when I run it from > Access > End If > >
The query is very complicated, it refers to another query, who refers to
another query, who refers to another query. But the original table's column where Profit comes from is of type "text" field of 50 character length (I know, I know, why is it a text field ? It was like that from the beginning, and I changed it when we convert to SQL Server, but the original Access database is a text field :( .... ) Is there anything that I can do to fix this data discrepancy issue ? BTW, this problem does not happen in a VB6 program Thank you. "Cowboy (Gregory A. Beamer)" <NoSpamMgbworld@comcast.netNoSpamM> wrote in message news:unLQjSPkIHA.5956@TK2MSFTNGP03.phx.gbl... [quoted text, click to view] > floating point numbers are notorious for being off. This can happen even > when you do not use two different programs (.NET versus Access). > > Not sure why things are so far off, however. What is the data type in > Access? > > -- > Gregory A. Beamer > MVP, MCP: +I, SE, SD, DBA > > Subscribe to my blog > http://gregorybeamer.spaces.live.com/lists/feed.rss > > or just read it: > http://gregorybeamer.spaces.live.com/ > > ************************************************* > | Think outside the box! | > ************************************************* > "fniles" <fniles@pfmail.com> wrote in message > news:%230JvyDPkIHA.5820@TK2MSFTNGP04.phx.gbl... >>I am using MS Access and VB .NET 2005 (the same problem happens in VB.NET >>2008). >> I am reading from a query using DataReader (the same problem happens if I >> use OLEDBDataAdapter). >> When I run the query in Access, the value for Cash = 830.004999999999, SC >> = 1692.5 and Profit = -862.495000000001. >> Cash = SC + Profit. >> But when I read the value from the program, this is what I got: >> Cash = 830.000000000489 >> SC = 1692.5 >> Profit = -862.499999999511. >> Why this difference and how can I fix it ? >> Thank you. >> >> Here are the codes: >> Dim m_cmd As OleDb.OleDbCommand >> Dim m_dr As OleDb.OleDbDataReader >> Dim sSQL As String >> m_cmd = New OleDb.OleDbCommand >> With m_cmd >> .Connection = adoConOLE >> .CommandText = "select Cash,Profit from myQuery where Account >> = '123'" >> End With >> m_dr = m_cmd.ExecuteReader() >> If m_dr.Read Then >> sSQL = sSQL & " " & m_dr.Item("Cash") --> this returns >> 830.000000000489 instead of 830.004999999999 when I run it from Access >> sSQL = sSQL & " " & m_dr.Item("SC") --> this returns 1692.5, >> which is the same as when I run it on Access >> sSQL = sSQL & " " & m_dr.Item("Profit") --> this >> returns -862.499999999511 instead of -862.495000000001 when I run it from >> Access >> End If >> >> > >
If this were merely a pure rounding issue, you can set up comparison to only
look at so many points to the right of the decimal, but there is something more insidious here. And I am not sure, right now, how to solve it. Not sure why it does not happen in VB6, but COM does handle things a bit differently. Somewhere along the way, there is a conversion to floating point. That is where I would look for the solution. Since Access is COM based, you cannot easily debug this one, which would be my other suggestion. There is likely a flip in one of the queries. And, I am not going to beat you up for it being a text field. We all carry garbage that we shouldn't have to as we work on applications. :-) -- Gregory A. Beamer MVP, MCP: +I, SE, SD, DBA Subscribe to my blog http://gregorybeamer.spaces.live.com/lists/feed.rss or just read it: http://gregorybeamer.spaces.live.com/ ************************************************* | Think outside the box! | ************************************************* [quoted text, click to view] "fniles" <fniles@pfmail.com> wrote in message news:eScf2BRkIHA.4684@TK2MSFTNGP06.phx.gbl... > The query is very complicated, it refers to another query, who refers to > another query, who refers to another query. > But the original table's column where Profit comes from is of type "text" > field of 50 character length (I know, I know, why is it a text field ? It > was like that from the beginning, and I changed it when we convert to SQL > Server, but the original Access database is a text field :( .... ) > Is there anything that I can do to fix this data discrepancy issue ? > BTW, this problem does not happen in a VB6 program > > Thank you. > > "Cowboy (Gregory A. Beamer)" <NoSpamMgbworld@comcast.netNoSpamM> wrote in > message news:unLQjSPkIHA.5956@TK2MSFTNGP03.phx.gbl... >> floating point numbers are notorious for being off. This can happen even >> when you do not use two different programs (.NET versus Access). >> >> Not sure why things are so far off, however. What is the data type in >> Access? >> >> -- >> Gregory A. Beamer >> MVP, MCP: +I, SE, SD, DBA >> >> Subscribe to my blog >> http://gregorybeamer.spaces.live.com/lists/feed.rss >> >> or just read it: >> http://gregorybeamer.spaces.live.com/ >> >> ************************************************* >> | Think outside the box! | >> ************************************************* >> "fniles" <fniles@pfmail.com> wrote in message >> news:%230JvyDPkIHA.5820@TK2MSFTNGP04.phx.gbl... >>>I am using MS Access and VB .NET 2005 (the same problem happens in VB.NET >>>2008). >>> I am reading from a query using DataReader (the same problem happens if >>> I use OLEDBDataAdapter). >>> When I run the query in Access, the value for Cash = 830.004999999999, >>> SC = 1692.5 and Profit = -862.495000000001. >>> Cash = SC + Profit. >>> But when I read the value from the program, this is what I got: >>> Cash = 830.000000000489 >>> SC = 1692.5 >>> Profit = -862.499999999511. >>> Why this difference and how can I fix it ? >>> Thank you. >>> >>> Here are the codes: >>> Dim m_cmd As OleDb.OleDbCommand >>> Dim m_dr As OleDb.OleDbDataReader >>> Dim sSQL As String >>> m_cmd = New OleDb.OleDbCommand >>> With m_cmd >>> .Connection = adoConOLE >>> .CommandText = "select Cash,Profit from myQuery where Account >>> = '123'" >>> End With >>> m_dr = m_cmd.ExecuteReader() >>> If m_dr.Read Then >>> sSQL = sSQL & " " & m_dr.Item("Cash") --> this returns >>> 830.000000000489 instead of 830.004999999999 when I run it from Access >>> sSQL = sSQL & " " & m_dr.Item("SC") --> this returns 1692.5, >>> which is the same as when I run it on Access >>> sSQL = sSQL & " " & m_dr.Item("Profit") --> this >>> returns -862.499999999511 instead of -862.495000000001 when I run it >>> from Access >>> End If >>> >>> >> >> > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |