Storing 1.00008E-07 in a decimal datatype using VB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Storing 1.00008E-07 in a decimal datatype using VB

Author

Topic

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-08-22 : 20:03:26

Hi, I have writen a program in VB that uses sql server.

I have run into the following circumstance (this is an example)

Dim f as single

f = 10000.0000001 - 10000

f has its value stored in scientific notation, I.e:

Now, I have a column in a table which has the decimal data type (with 4 digits after the point).

When I save this value to my column, I get the error:

"Error converting data type varchar to numeric"

I think it is because f is stored as a string (I may be wrong).

One way to get around this problem is to round f to 4 decimal points before storing it.
I.e. f = round(f,4)

My question is, has anyone solved this problem in a better manner? I don't want to
go to all my variables and round them before storing them in my table.

Thanks.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-23 : 03:39:49

would this help?

select cast(f as numeric(9,4))

Go with the flow & have fun! Else fight the flow :)

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-08-23 : 10:57:32

I would be the same as rounding the value before inserting it into the table. I want to know if there is a better way to do this.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-23 : 11:26:42

MSDN:
The Single data type can be converted to the Double or Decimal data type without encountering a System.OverflowException error.
i'm guessing you passing it as a string.

how do you pass the value to the database? stored procedure, direct sql statement? is it in .net?

Go with the flow & have fun! Else fight the flow :)

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-08-23 : 19:28:38

Hi, Yes, it seems the single would be changed to a decimal.

The problem is:

Lets say I do the following in VB

MsgBox 10 - 10.01

I get a message box displaying a value in scientific notation
I.e. -9.9999 E-3

Now, when I save this value it gives me the error.

I am wondering, am I passing a string now that the value is in
this format. It would seem strange if this was the case so I
would like to know exactly what is happening.

Am I making sense?

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-08-23 : 20:54:09

By the way, I am making my sql entry via the code within VB.

Now, from what I recall floating point values have rounding errors
because of the limitations of the storeage in memory.

Should I basically do my calculations for monatory values in
floating point and then convert it to decimal when I enter it into
my table? Does anyone know what I am saying, because I'm not
sure if I understand the specifics to explain this correctly.

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-08-23 : 20:59:15

Heck, here is a thought, should I change all the variables the
represent currency in my VB program from data type single to
data type currency?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-23 : 21:15:07

SQLerror -- you need to do a lot of reading up on data types, and how something is displayed on the screen versus stored internally in memory.

First off -- YES! store your currency data in a decimal format -- the money datatype in SQL is perfect. The CURRENCY datatype in VB is not to shabby as well.

I hope you don't WANT 10-10.1 to be stored -9.9999 E-3? Wouldn't maybe -.01 be a little better? if you declare your variables properly in VB and, then that expression will return the value you need.

In VB, look at this result of this:

CCur(10) - CCur(10.01)

Now look at:

CSng(10) - CSng(10.01)

and

Ccur(10) - CSng(10.01)

In SQL, if you use stored procedures with parameters of the proper type, and in VB if you store and convert values always into the proper type, you should rarely have conversion errors. but if you let VB or SQL Server convert values implicitly then you will probably run into lots of issues.

- Jeff

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-08-23 : 21:29:40

Hi, ya, I do have some reading to do.

Hmm, so I guess when you do a Ccur(10) - CSng(10.01),
VB converts the value to a currency datatype?
(or maybe it first converts the single to a currency and then
does the subtraction)

I think I will switch all my monetary variables to currency.

Am I thinking on the right track?

thanks for your help and insite.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-23 : 23:13:18

I think I will switch all my monetary variables to currency.

Am I thinking on the right track?

yes :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources