sql select sum (column) not correct sum?

[marsias]

this give me not the corrected sum(42), suppose to be 43,91

Gold_gr is a text field column which has numbers

Using conn As New SQLiteConnection(connection)
    conn.Open()
    Dim query As String = "SELECT SUM(Gold_gr) from users"
    Using command As New SQLiteCommand(query, conn)
        TextBox6.Text = Convert.ToString(command.ExecuteScalar())
    End Using

[.paul.]

Text fields aren’t number fields. It looks like the sql is trying to convert the text to numbers, but because they aren’t valid decimal numbers (7.32 instead of 7,32) it isn’t interpreting it correctly.

[Red.x]

Dim connectionString As String = "Your_Connection_String_Here"
Dim query As String = "SELECT SUM(ColumnName) FROM YourTableName"
Dim total As Decimal

Using connection As New SqlConnection(connectionString)
    Dim command As New SqlCommand(query, connection)
    connection.Open()
    total = Convert.ToDecimal(command.ExecuteScalar())
End Using

Console.WriteLine("Total Sum: " & total)

[.paul.]

Dim connectionString As String = "Your_Connection_String_Here"
Dim query As String = "SELECT SUM(ColumnName) FROM YourTableName"
Dim total As Decimal

Using connection As New SqlConnection(connectionString)
    Dim command As New SqlCommand(query, connection)
    connection.Open()
    total = Convert.ToDecimal(command.ExecuteScalar())
End Using

Console.WriteLine("Total Sum: " & total)

command.ExecuteScalar() Returns an Object. Converting it to Decimal at that point wouldn’t fix the problem.
I’m still fairly sure it’s because of CultureInfo Decimal formatting…

[jmcilhinney]

Gold_gr is a text field column which has numbers

Why? Do you have control of the database? If so, you should change that.

Failing that, you will need to actually convert the text to a number explicitly before summing, instead of expecting the system to do it for you and then complaining that it doesn't do it how you want. The first thing you should do is search for information on how to convert text to a number in SQLite.

[jmcilhinney]

This question really has nothing to do with VB.NET and is purely related to SQL and SQLite, so it has been moced to the Database Development forum.

[Zvoni]

Yepp.
Definitely the Column as Text issue instead of Datatype REAL

6 x 7.32 = 43.92
6 x 7 = 42