Android Question Inserting data into array

[microbox]

Hi everyone!
I'm trying to get data from database table and using cursor to hold the records.
My data in table record is like..

P1
P2
P3
P4
P5
P6

There are 2 features I need to implement when adding new records containing with "S"
Example of records

P7S
P8S
P9S
P1
P2
P3
P4
P5
P6

1. When adding data contain with "S" should be in the top most record. (I think I got this)
2. When there already record contain with "S" it should be sorted by its numeric value (difficult for me)
Note that the numeric value between P and S is incremental.
This is how I derive #1 requirement, it might be lengthy(hope there is a better way).

Sub Button1_Click
'=================Exploring Array===============================

Dim Result,sizeIndex As String
Dim Cursor1 As Cursor
Dim SenrCtr As Int = 0
'del = DBUtils.ExecuteMap(SQL, "SELECT qp ,status  FROM Quep",Null)
Cursor1 = SQL.ExecQuery("SELECT qp ,status FROM Quep")                '' Query from Table
sizeIndex =  Cursor1.RowCount                                         '' indetify the size of records from table
For i = 0 To Cursor1.RowCount - 1                                    '' Iterate all records
        Cursor1.Position = i                                                 '' Get Cursor position
        Result = Cursor1.GetString("qp")                              '' Get values in column qp
        Msgbox(Cursor1.GetString("qp")   ,"Total " & sizeIndex)       '' Display in Messagebox
        If Result.Contains("S") Then                                  '' Test if it contains specific value
        SenrCtr = SenrCtr + 1                                         '' Identify number of 'S' in record
        End If
  Next 
      
    ''--------------------------------------------------------------
If SenrCtr > 0 Then
       ''--------
    Else 
        Msgbox(sizeIndex & " Getting ready to insert", "")
        Dim myarray1(sizeIndex+1) As String
        Dim i As Int     
        For i = 0 To sizeIndex
            If i = 0 Then
            myarray1(i) = "MyInsert"
            Msgbox(i & " " & myarray1(i),"")
            Else     
            Cursor1.Position = i-1
            myarray1(i) = Cursor1.GetString("qp") 
            Msgbox(i & " " & myarray1(i),"")
            End If
        Next
        Cursor1.Close
        ''----------------Display The array!---------
        For i = 0 To sizeIndex
        Msgbox(i & " New added data in Array " & myarray1(i)   ,"")
        Next
End If
End Sub

Hope anyone can help me out how to solve it.


Thank you in advance

 

[Erel]

1. Use Log instead of Msgbox.
2. Use List instead of arrays.
Create two lists. One for records with S and one for records without S. Then sort the first list and add all the items from the first list to the second list.

 

[microbox]

1. Use Log instead of Msgbox.
2. Use List instead of arrays.
Create two lists. One for records with S and one for records without S. Then sort the first list and add all the items from the first list to the second list.

Thanks Erel..I will try it.

 

[sorex]

you could do most of the logic on the SQL side so that you just need to read in the data and it's all placed in the right order.

 

[microbox]

@sorex
Thanks for the tip...
@Erel
I got it working now.

 

[microbox]

Oops I spoke too soon, I'm still having issue with sorting data with numeric value between P and S ...works well if I have only 1 numeric(ex. P9S) value between P and S, not good when more than 1 digit(ex. P231S).
This is the result I have.

P14S
P23S
P27S
P37S
P9S
P7
P8
P10
P13
P33

My code..

Sub SortPayment
    Dim Result,sizeIndex As String
    Dim Cursor1 As Cursor
    '#table1 - express/seniors
    '#table2 - ordinary line
    Dim table1,table2 As List
    table1.Initialize:table2.Initialize
    'del = DBUtils.ExecuteMap(SQL, "SELECT qp ,status  FROM Quep",Null)
    Cursor1 = SQL.ExecQuery("SELECT qp ,status FROM Quep")                '' Query from Table
    sizeIndex =  Cursor1.RowCount                                         '' indetify the size of records from table
     For i = 0 To Cursor1.RowCount - 1                                    '' Iterate all records
            Cursor1.Position = i                                          '' Get Cursor position
            Result = Cursor1.GetString("qp")                              '' Get values in column qp
            'Msgbox(Cursor1.GetString("qp")   ,"Total " & sizeIndex)       '' Display in Messagebox
            If Result.Contains("S") Then                                  '' Test if it contains specific value within the item
            'SenrCtr = SenrCtr + 1
            table1.Add(Result)
            Else                                                               '' Identify number of Seniority in the table
            table2.add(Result)
            End If
      Next           
        ''--------------------------------------------------------------
           'EMPTY CONTENT OF TABLE
           DBUtils.ExecuteMap(SQL, "DELETE from Quep",Null)
        ''--------------------------------------------------------------
        table1.Sort(True)
         For i=0 To table1.Size-1
         Log(table1.Get(i) & " Got it here1")
         DBUtils.ExecuteMap(SQL, "Insert into Quep(qp,status)values('" & table1.Get(i) & "',0) ",Null)
         Next   
         For i=0 To table2.Size-1
         Log(table2.Get(i) &" Got it here2")
         DBUtils.ExecuteMap(SQL, "Insert into Quep(qp,status)values('" & table2.Get(i) & "',0) ",Null)
         Next
End Sub

I appreciate any inputs.

 

[sorex]

I guess you will need to modify the strings a bit so that they become

P0014S
P0023S
P0027S
P0037S
P0009S
P0007
P0008
P0010
P0013
P0033

which can easily be achieved with mid/right string funtions.

 

[Straker]

You have a simple sorting problem.
P10 comes before P7 just because '1' is smaller than 7

You can store in your list only the numeric values, sort the list and the add the 'P' (or 'P' + 'S') in the query.

 

[microbox]

Thanks everyone for the advice...I got it working.