Problem When VB save in DB

[sacramento]

Hi to all:

This peace of code put one value in DB:

 Dim con1 As New OleDb.OleDbConnection(String.Format("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " & drive & IDempresa))
        Dim sSQL As String

        valorcolocardebito = FormatNumber(valorcolocardebito, 2)

        If valorcolocardebito = 0 Then
            valorcolocardebito = 0
        End If


        sSQL = "UPDATE balancetegeral SET numeroconta = @contaacolocar, "
        sSQL = sSQL & "mensaisdebito = @valorcolocardebito "
        sSQL = sSQL & "WHERE numeroconta = @contaacolocar"

        Dim command As New OleDb.OleDbCommand(sSQL, con1)

        With command.Parameters
            .AddWithValue("@contaacolocar", contaacolocar)

            If valorcolocardebito = 0 Then
                .AddWithValue("@mensaisdebito", DBNull.Value)
            Else
                .AddWithValue("@mensaisdebito", valorcolocardebito)
            End If
        End With

        con1.Open()
        command.ExecuteNonQuery()
        con1.Close()

I had run the code step by step and he do a strange,better, very strange thing...
The value puted in valorcolocardebito = 263.733,69 and when I confirm this value in DB i have 263.733,70...
More surprise is because he make this for an values and for other save very well...
This could be surealistic,but it is that I have and sincerely i don't know way this happen...

Can anyone give me an suggestion?

Thanks

[si_the_geek]

What data type is the variable valorcolocardebito?

What data type is the field it is being put in to?

[sacramento]

What data type is the variable valorcolocardebito?

Dim valorcolocardebito As Double

What data type is the field it is being put in to?

In Db the field are number

[si_the_geek]

Ok, well in that case using AddWithValue is OK.

The problem is almost certainly this line:

        valorcolocardebito = FormatNumber(valorcolocardebito, 2)

Assuming FormatNumber is similar to the VB6 version, it returns a String formatted in a particular way... but you then store the result in a Double, so it needs to be converted - and that can go wrong based on various factors (including Regional Settings).

Due to issues like that, you should not convert numbers to Strings unless you are displaying them, instead you should just treat them as numbers. If you want to alter the number in some way, use numeric functions and operators (such as / and * ).

[sacramento]

Hi:

This is very weard,in fact...do you know why in some records i have the write values,and other's the record round to the next number?

I had delete the line you are suggested and the problem remain...

Then i have put in DB type field Currency and the value appears correct...

Well if the routine save in the same way all the times,that's it's a error that something it's wrong in the code,but in this case save well in a few records and save bad in other's records, what's goin on!!!!!!

[si_the_geek]

Can you give some examples of "right" and "wrong" values?


You said the DB field was a Number, but that is not the actual data type - the real data type (which may be Integer/Double/...) is listed in one of the properties.

If you had an Integer based one, it would only save the whole numbers.

[sacramento]

HI:

Right Values - the code save in DB 263.733,69

Wrong values - the code save in DB 263.733,70

Like I said...sometimes the code save the right values and other times wrong values...do you believe in this!!!!!!!!


Thanks

[si_the_geek]

That level of rounding is not surprising if you are using a floating-point data type (such as Double or Single). Floating-point numbers are designed for a high level of speed, at the expense of a small loss of accuracy.

If you want full accuracy, Currency (which is stored as a scaled integer) is the kind of thing you should be using.

[sacramento]

Hi:

That's that i had do...I had change for currency in DB


Thanks a lot