Android Question Loading only selected rows on B4XTable from SQLite

S

Setlodi

Active Member
Hi fellows

I have a database with 24 fields but I only want to load a few selected fields to B4XTable.

B4X: 
    B4XTable1.AddColumn("Name", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("Surname", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("Address1", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("Address2", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("Town", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("Province", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("Cell Number", B4XTable1.COLUMN_TYPE_TEXT)

    dialog.Initialize(Activity)
    InputTemplate.Initialize
    
    Dim Data As List
    Data.Initialize
    
    Dim rs As ResultSet = sql.ExecQuery("SELECT Trip_No, Date, Time, Driver_Name, Driver_Surname, Driver_ID_No, Driver_License_Code, Driver_PrDP_Status, Vehicle_Make, Vehicle_Reg_No, Departure, Destination, Contact_Name, Contact_No, Luggage_Amt, Rank_Manager_Fee, Passenger_Name, Passenger_Surname, Passenger_Address_1, Passenger_Address_2, Passenger_Town, Passenger_Province, Passenger_Cell_No, No_of_Passengers FROM Passenger_Table")
    Do While rs.NextRow
        Dim row(24) As Object
        row(0) = rs.GetString("Trip_No")
        row(1) = rs.GetString("Date")
        row(2) = rs.GetString("Time")
        row(3) = rs.GetString("Driver_Name")
        row(4) = rs.GetString("Driver_Surname")
        row(5) = rs.GetString("Driver_ID_No")
        row(6) = rs.GetString("Driver_License_Code")
        row(7) = rs.GetString("Driver_PrDP_Status")
        row(8) = rs.GetString("Vehicle_Make")
        row(9) = rs.GetString("Vehicle_Reg_No")
        row(10) = rs.GetString("Departure")
        row(11) = rs.GetString("Destination")
        row(12) = rs.GetString("Contact_Name")
        row(13) = rs.GetString("Contact_No")
        row(14) = rs.GetString("Luggage_Amt")
        row(15) = rs.GetString("Rank_Manager_Fee")
        row(16) = rs.GetString("Passenger_Name")
        row(17) = rs.GetString("Passenger_Surname")
        row(18) = rs.GetString("Passenger_Address_1")
        row(19) = rs.GetString("Passenger_Address_2")
        row(20) = rs.GetString("Passenger_Town")
        row(21) = rs.GetString("Passenger_Province")
        row(22) = rs.GetString("Passenger_Cell_No")
        row(23) = rs.GetString("No_of_Passengers")
        
        Data.Add(row)
    Loop
    rs.Close
    B4XTable1.SetData(Data)

I want to load only from row 16 to row 22, to my B4XTable with the same columns. How can I do it?
 
Solution
aeric
Setlodi said:
Hi. I only wanted to show a few rows out of the database...
Click to expand...
Please clarify what you mean by row.

Case 1:
If you named your column as row then is that mean you only want to show:
B4X: 
row(16)
row(17)
row(18)
row(19)
row(20)
row(21)
row(22)

Case 2:
If let say the resultset returns 30 rows, you want to ignore row 1 to 15 and row 23 to 30 then you can use if-else to skip them when using Data.Add(row).
B4X: 
Do While rs.NextRow
    count = count + 1
    If count > 15 and count < 23 Then
        row(0) = ...
        row(1) = ...
        .
        .
        .
        row(23) = ...
        Data.Add(row)
    End If
Loop

I think it is more likely Case 1.
Then declare something like this:
B4X: 
Dim row(7) As object
row(0) =...
Sort by date Sort by votes
J

JohnJ

Member
Licensed User
Longtime User
Setlodi said:
Hi fellows

I have a database with 24 fields but I only want to load a few selected fields to B4XTable.

B4X: 
    B4XTable1.AddColumn("Name", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("Surname", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("Address1", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("Address2", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("Town", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("Province", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("Cell Number", B4XTable1.COLUMN_TYPE_TEXT)

    dialog.Initialize(Activity)
    InputTemplate.Initialize
   
    Dim Data As List
    Data.Initialize
   
    Dim rs As ResultSet = sql.ExecQuery("SELECT Trip_No, Date, Time, Driver_Name, Driver_Surname, Driver_ID_No, Driver_License_Code, Driver_PrDP_Status, Vehicle_Make, Vehicle_Reg_No, Departure, Destination, Contact_Name, Contact_No, Luggage_Amt, Rank_Manager_Fee, Passenger_Name, Passenger_Surname, Passenger_Address_1, Passenger_Address_2, Passenger_Town, Passenger_Province, Passenger_Cell_No, No_of_Passengers FROM Passenger_Table")
    Do While rs.NextRow
        Dim row(24) As Object
        row(0) = rs.GetString("Trip_No")
        row(1) = rs.GetString("Date")
        row(2) = rs.GetString("Time")
        row(3) = rs.GetString("Driver_Name")
        row(4) = rs.GetString("Driver_Surname")
        row(5) = rs.GetString("Driver_ID_No")
        row(6) = rs.GetString("Driver_License_Code")
        row(7) = rs.GetString("Driver_PrDP_Status")
        row(8) = rs.GetString("Vehicle_Make")
        row(9) = rs.GetString("Vehicle_Reg_No")
        row(10) = rs.GetString("Departure")
        row(11) = rs.GetString("Destination")
        row(12) = rs.GetString("Contact_Name")
        row(13) = rs.GetString("Contact_No")
        row(14) = rs.GetString("Luggage_Amt")
        row(15) = rs.GetString("Rank_Manager_Fee")
        row(16) = rs.GetString("Passenger_Name")
        row(17) = rs.GetString("Passenger_Surname")
        row(18) = rs.GetString("Passenger_Address_1")
        row(19) = rs.GetString("Passenger_Address_2")
        row(20) = rs.GetString("Passenger_Town")
        row(21) = rs.GetString("Passenger_Province")
        row(22) = rs.GetString("Passenger_Cell_No")
        row(23) = rs.GetString("No_of_Passengers")
       
        Data.Add(row)
    Loop
    rs.Close
    B4XTable1.SetData(Data)

I want to load only from row 16 to row 22, to my B4XTable with the same columns. How can I do it?
Click to expand...
Not sure what type of database you have but a SQLite database has a RowID option which can be used in a where clause.
Such as "where rowid between 16 and 22
 
Upvote 0
P

PaulMeuris

Well-Known Member
Licensed User
You can try this:
B4X: 
   Dim rs As ResultSet = sql.ExecQuery("SELECT Passenger_Name, Passenger_Surname, Passenger_Address_1, Passenger_Address_2, Passenger_Town, Passenger_Province, Passenger_Cell_No FROM Passenger_Table")
The resultset will now have only 7 strings in each row which is the number of columns you have in the B4XTable1.
 
Upvote 0
J

JohnJ

Member
Licensed User
Longtime User
Setlodi said:
Hi fellows

I have a database with 24 fields but I only want to load a few selected fields to B4XTable.

B4X: 
    B4XTable1.AddColumn("Name", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("Surname", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("Address1", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("Address2", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("Town", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("Province", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("Cell Number", B4XTable1.COLUMN_TYPE_TEXT)

    dialog.Initialize(Activity)
    InputTemplate.Initialize
   
    Dim Data As List
    Data.Initialize
   
    Dim rs As ResultSet = sql.ExecQuery("SELECT Trip_No, Date, Time, Driver_Name, Driver_Surname, Driver_ID_No, Driver_License_Code, Driver_PrDP_Status, Vehicle_Make, Vehicle_Reg_No, Departure, Destination, Contact_Name, Contact_No, Luggage_Amt, Rank_Manager_Fee, Passenger_Name, Passenger_Surname, Passenger_Address_1, Passenger_Address_2, Passenger_Town, Passenger_Province, Passenger_Cell_No, No_of_Passengers FROM Passenger_Table")
    Do While rs.NextRow
        Dim row(24) As Object
        row(0) = rs.GetString("Trip_No")
        row(1) = rs.GetString("Date")
        row(2) = rs.GetString("Time")
        row(3) = rs.GetString("Driver_Name")
        row(4) = rs.GetString("Driver_Surname")
        row(5) = rs.GetString("Driver_ID_No")
        row(6) = rs.GetString("Driver_License_Code")
        row(7) = rs.GetString("Driver_PrDP_Status")
        row(8) = rs.GetString("Vehicle_Make")
        row(9) = rs.GetString("Vehicle_Reg_No")
        row(10) = rs.GetString("Departure")
        row(11) = rs.GetString("Destination")
        row(12) = rs.GetString("Contact_Name")
        row(13) = rs.GetString("Contact_No")
        row(14) = rs.GetString("Luggage_Amt")
        row(15) = rs.GetString("Rank_Manager_Fee")
        row(16) = rs.GetString("Passenger_Name")
        row(17) = rs.GetString("Passenger_Surname")
        row(18) = rs.GetString("Passenger_Address_1")
        row(19) = rs.GetString("Passenger_Address_2")
        row(20) = rs.GetString("Passenger_Town")
        row(21) = rs.GetString("Passenger_Province")
        row(22) = rs.GetString("Passenger_Cell_No")
        row(23) = rs.GetString("No_of_Passengers")
       
        Data.Add(row)
    Loop
    rs.Close
    B4XTable1.SetData(Data)

I want to load only from row 16 to row 22, to my B4XTable with the same columns. How can I do it?
Click to expand...
You should also select only the columns that you want in the table as in "select f1,f4,f5"
 
Upvote 0
aeric

aeric

Expert
Licensed User
Longtime User
Setlodi said:
Hi. I only wanted to show a few rows out of the database...
Click to expand...
Please clarify what you mean by row.

Case 1:
If you named your column as row then is that mean you only want to show:
B4X: 
row(16)
row(17)
row(18)
row(19)
row(20)
row(21)
row(22)

Case 2:
If let say the resultset returns 30 rows, you want to ignore row 1 to 15 and row 23 to 30 then you can use if-else to skip them when using Data.Add(row).
B4X: 
Do While rs.NextRow
    count = count + 1
    If count > 15 and count < 23 Then
        row(0) = ...
        row(1) = ...
        .
        .
        .
        row(23) = ...
        Data.Add(row)
    End If
Loop

I think it is more likely Case 1.
Then declare something like this:
B4X: 
Dim row(7) As object
row(0) = rs.GetString("Passenger_Name")
row(1) = rs.GetString("Passenger_Surname")
row(2) = rs.GetString("Passenger_Address_1")
row(3) = rs.GetString("Passenger_Address_2")
row(4) = rs.GetString("Passenger_Town")
row(5) = rs.GetString("Passenger_Province")
row(6) = rs.GetString("Passenger_Cell_No")
Data.Add(row)
You don't need to SELECT unnecessary columns/fields in the SQL query.
 
Upvote 0
Solution
You must log in or register to reply here.

Similar Threads

R
  • Locked
  • Question
Android Question B4ATable Set Data
Replies
8
Views
685
Erel
Erel
Sabotto
  • Question
Android Question Formatting B4XTable column numbers. Works with B4J but not with B4A
Replies
2
Views
702
Mahares
M
M
  • Question
Android Question B4XTable-Setup Table with Fewer Columns Using QueryBuilder Without Redefining Columns
Replies
4
Views
601
Erel
Erel
R
  • Question
Android Question B4XTable why 1 to 2 out of 0?
Replies
11
Views
2K
RB Smissaert
R
Erel
  • Article
Android Tutorial [B4X] B4XTable - Load data from SQL database
Replies
4
Views
23K
Erel
Erel
Top