Android Question SQLite / WebView number format

[Declan]

I am reading data from a SQLite database and displaying in a WebView.
Is it possible to format the number?
eg.
format 38805.5 TO 38,805.00

I am using the following code to query the SQLite table and display in the WebView:

Sub ShowSalesBySalesPerson(MyStart As String, MyEnd As String)
    Dim Query As String
    Query = "SELECT DISTINCT(Sales_Person) As [Sales Person], Store AS [Store], SUM(Units_Sold) As [Units Sold], SUM(Total_Retail) As [Total Retail] FROM sales_end_user WHERE Sale_Date BETWEEN '" & MyStart & "' AND '" & MyEnd & "' GROUP BY Sales_Person ORDER BY Store"
    wbvQuery.LoadHtml(ExecuteHtml(SQL1, Query, Null, 0, True))  
    wbvQuery.Visible=True
End Sub

Another problem I have is that if a number is large, it displays as:
3.17888e+06

 

[Erel]

This line in DBUtils.ExecuteHtml gets the value from the database and appends it to the html string:

sb.Append(cur.GetString2(i))

If you know that column 1 (second column) is a number and should be formatted then you can change the code to:

If i = 1 Then
sb.Append(NumberFormat(cur.GetDouble2(i), 0, 2)) 'use NumberFormat2 for more options
Else
sb.Append(cur.GetString2(i))
End If

 

[Declan]

The column is always column 3 (fourth column)(Total_Retail) in the query:

Query = "SELECT DISTINCT(Sales_Person) As [Sales Person], Store AS [Store], SUM(Units_Sold) As [Units Sold], SUM(Total_Retail) As [Total Retail] FROM sales_end_user WHERE Sale_Date BETWEEN '" & MyStart & "' AND '" & MyEnd & "' GROUP BY Sales_Person ORDER BY Store"

I have changed the code in DBUtils.ExecuteHtml to :

    '                sb.Append(cur.GetString2(i))
                    If i = 3 Then
                        sb.Append(NumberFormat(cur.GetDouble2(i), 0, 2)) 'use NumberFormat2 for more options
                    Else
                        sb.Append(cur.GetString2(i))
                    End If

as per the above post.
However, this has no effect on the format of the number values.

Also with the formatting:
If the value is greater than 6 digits, I get:
2.8955e+06
or
3.10149e+06
?????

 

[Mahares]

as per the above post.
However, this has no effect on the format of the number values.

I could not get it to format properly per @Erel 's suggestion either. So, these are the steps I coded to make it work:
1. Create a list of the data from the original table and formatted column in question using Numberformat2
2. Create a temp table and loaded it with data from the list.
3. Load temp table onto a webview

'Create a list of the data from the original table and formatted column in question using Numberformat2
    Dim cursor1 As Cursor
    Dim MyList As List 
    MyList.Initialize
    txt="SELECT ID, COUNTRY, Feld_006 AS POP FROM  Abrechnung"
    cursor1=SQL1.ExecQuery(txt)
    For i=0 To cursor1.RowCount-1
        cursor1.Position=i
        Dim MY_POP As String = NumberFormat2(cursor1.GetDouble("POP"), 1,2, 2,True)           
        MyList.Add(cursor1.GetInt("ID") & TAB & cursor1.GetString("COUNTRY") & TAB & MY_POP )
    Next

    'Create a temp table and loaded it with data from the list
    SQL1.ExecNonQuery("DROP TABLE IF EXISTS temptable")
    txt="CREATE TABLE IF NOT EXISTS  temptable (ID INTEGER PRIMARY KEY, COUNTRY TEXT, BUDGET TEXT)"
    SQL1.ExecNonQuery(txt)  
    For i=0 To MyList.Size -1
        Dim CC() As String=Regex.Split(TAB,MyList.Get(i))
        SQL1.ExecNonQuery2("INSERT INTO temptable VALUES(?,?,?)", CC)
    Next
   
    'Load temp table onto a webview
    Dim Query As String ="SELECT  * FROM temptable"
    wvquery.LoadHtml(DBUtils.ExecuteHtml(SQL1, Query, Null, 0, True))

 

[Declan]

@Mahares
Many Thanks.
I have included your suggested solution using a shortened version of my table and edited your code accordingly:

Sub ShowSalesBySalesPerson(MyStart As String, MyEnd As String)
    Dim cursor1 As Cursor
    Dim MyList As List
    Dim txt As String
    MyList.Initialize
'    txt="SELECT ID, COUNTRY, Feld_006 AS POP FROM  Abrechnung"
    txt = "SELECT Units_Sold, Total_Retail AS POP FROM sales_end_user"
    cursor1=SQL1.ExecQuery(txt)
    For i=0 To cursor1.RowCount-1
        cursor1.Position=i
        Dim MY_POP As String = NumberFormat2(cursor1.GetDouble("POP"), 1,2, 2,True)          
        MyList.Add(cursor1.GetInt("Units_Sold") & TAB & MY_POP )
    Next
   
    'Create a temp table and loaded it with data from the list
    SQL1.ExecNonQuery("DROP TABLE IF EXISTS temptable")
    txt="CREATE TABLE IF NOT EXISTS  temptable (Units_Sold TEXT, Total_Retail TEXT)"
    SQL1.ExecNonQuery(txt) 
    For i=0 To MyList.Size -1
        Dim CC() As String=Regex.Split(TAB,MyList.Get(i))
        SQL1.ExecNonQuery2("INSERT INTO temptable VALUES(?,?)", CC)
    Next
   
    'Load temp table onto a webview
    Dim Query As String ="SELECT  * FROM temptable"
    wbvQuery.LoadHtml(DBUtils.ExecuteHtml(SQL1, Query, Null, 0, True))
    wbvQuery.Visible=True
End Sub

I have left the code in DBUtils.ExecuteHtml to :

sb.Append(cur.GetString2(i))

or should I change to Erel's suggestion?

However, when I compile the app in debug, I get no errors but the WebView (wbvQuery) loads blank with no data?

 

[Declan]

@Mahares.
GOT IT
Thanks

Sub ShowSalesBySalesPerson(MyStart As String, MyEnd As String)
    Dim cursor1 As Cursor
    Dim MyList As List
    Dim txt As String
    MyList.Initialize
    txt = "SELECT Sales_Person, Store, SUM(Units_Sold), Sum(Total_Retail) AS POP FROM sales_end_user  WHERE Sale_Date BETWEEN '" & MyStart & "' AND '" & MyEnd & "'  GROUP BY Sales_Person ORDER BY Store"
    cursor1=SQL1.ExecQuery(txt)
    For i=0 To cursor1.RowCount-1
        cursor1.Position=i
        Dim MY_POP As String = NumberFormat2(cursor1.GetDouble("POP"), 1,2, 2,True)          
        MyList.Add(cursor1.GetString("Sales_Person") & TAB & cursor1.GetString("Store") & TAB & cursor1.GetString("SUM(Units_Sold)") & TAB &  MY_POP )
    Next
   
    'Create a temp table and loaded it with data from the list
    DBUtils.DropTable(SQL1, "temptable")
    LOCALCreateSalesByPersonTempTable
    For i=0 To MyList.Size -1
        Dim CC() As String=Regex.Split(TAB,MyList.Get(i))
        SQL1.ExecNonQuery2("INSERT INTO temptable VALUES(?,?,?,?)", CC)
    Next
   
    'Load temp table onto a webview
    Dim Query As String ="SELECT  * FROM temptable"
    wbvQuery.LoadHtml(DBUtils.ExecuteHtml(SQL1, Query, Null, 0, True))
    wbvQuery.Visible=True
End Sub