Android Question [SOLVED] B4XTable ExecNonQuery2 Select

[makis_best]

Hi all

On this thread https://www.b4x.com/android/forum/threads/b4x-b4xtable-delete-row.103582/#content
show how we can use ExecNonQuery2 to do a delete.

That means we can execute select, insert and updates,... correct?

Is there any example how we can do it?

Thank you

 

[Mahares]

That means we can execute select, insert and updates,... correct?

Yes. See this thread for a code snippet example:
https://www.b4x.com/android/forum/threads/b4xtable-add-lines-rows.104549/#post-655142

 

[makis_best]

Yes. See this thread for a code snippet example:
https://www.b4x.com/android/forum/threads/b4xtable-add-lines-rows.104549/#post-655142

Thank you @Mahares

So if I want a simple select then I can do

B4XTable1.sql1.ExecQuery("SELECT * FROM table1")

Correct?

 

[Erel]

What do you want to do with the result of the select query? If you want to show it in the table then call Table.CreateDataView instead.

 

[makis_best]

What do you want to do with the result of the select query? If you want to show it in the table then call Table.CreateDataView instead.

I want to do calculations based of user input.
I Can't do it?
I need to careful for something?
If I want to specify a column name at Select script I use column ID?

 

[jimmyF]

I want to do calculations based of user input.

This is how I do it:

I have two columns, "OrderTotal" and "Tips"
I placed two Labels, lblTips and lblOrders, on the form.
(Look in your B4XTable Example in the ShowDialog sub for a hint)

This is what I do in the B4XTable1_DataUpdated sub:

    Dim rs As ResultSet = B4XTable1.sql1.ExecQuery("Select SUM(c7) From Data")
    If rs.NextRow Then
        If rs.GetString(rs.GetColumnName(0)) <> Null Then
            totalOrders = rs.GetString(rs.GetColumnName(0))
        Else
            Return
        End If
    
        Dim rs As ResultSet = B4XTable1.sql1.ExecQuery("Select SUM(c8) From Data")
        If rs.NextRow Then
            totalTips = rs.GetString(rs.GetColumnName(0))
        End If
    End If

   lblTips.Text = "Tips: $" & NumberFormat(totalTips,1,2)
   lblOrders.Text = "Orders: $" & NumberFormat(totalOrders,1,2)

 

[makis_best]

@jimmyF Nice example....

Thank you

 

[Mahares]

This is what I do in the B4XTable1_DataUpdated sub

Nice example Jimmy: Could you have combined both transactions in one like this:

Dim rs As ResultSet = B4XTable1.sql1.ExecQuery("Select SUM(c7) as SC7, SUM(c8) as SC8 From Data")
   If rs.NextRow Then
            lblTips.Text = "Tips: $" & NumberFormat(rs.GetDouble("SC7"),1,2)
            lblOrders.Text = "Tips: $" & NumberFormat(rs.GetDouble("SC8"),1,2)
    End If

 

[jimmyF]

Could you have combined both transactions in one like this:

Yes, I could have if I wasn't so lazy.
I added the second transaction a few days later and couldn't be bothered to optimize it at the time. App is still in development.
Good point, though!