Android Question SQLite / WebView number format

D

Declan

Well-Known Member
Licensed User
Longtime User
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:
B4X: 
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
 
Last edited:
Sort by date Sort by votes

Erel

B4X founder
Staff member
Licensed User
Longtime User
This line in DBUtils.ExecuteHtml gets the value from the database and appends it to the html string:
B4X: 
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:
B4X: 
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
 
Upvote 0
D

Declan

Well-Known Member
Licensed User
Longtime User
The column is always column 3 (fourth column)(Total_Retail) in the query:
B4X: 
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 :
B4X: 
    '                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
?????
 
Last edited:
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Declan said:
as per the above post.
However, this has no effect on the format of the number values.
Click to expand...

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

B4X: 
'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))

 
Upvote 0
D

Declan

Well-Known Member
Licensed User
Longtime User
@Mahares
Many Thanks.
I have included your suggested solution using a shortened version of my table and edited your code accordingly:
B4X: 
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 :
B4X: 
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?
 
Upvote 0
D

Declan

Well-Known Member
Licensed User
Longtime User
@Mahares.
GOT IT
Thanks
B4X: 
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
 
Upvote 0
You must log in or register to reply here.

Similar Threads

L
  • Question
Android Question format edittext
Replies
6
Views
949
lolo32
L
Bug? IllegalArgumentException
Replies
2
Views
1K
wimpie3
A
  • Question
Android Question I write a test program to show that same SQLite files, some file can’t be read well. I need help.
Replies
6
Views
860
Computersmith64
B
  • Question
Android Question DBUtils.CopyDBFromAssets function
Replies
4
Views
2K
Bucky108
B
  • Question
Android Question OkHttp - Set parameter with Inline Java
Replies
2
Views
2K
M. Giray Ozkan
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…