Android Question Fill Menu With Distinct Sqlite Query Results

Discussion in 'Android Questions' started by BitsAndBytes, Jun 7, 2017.

  1. BitsAndBytes

    BitsAndBytes Active Member Licensed User

    Code:
    Sub acActionBarLight_MenuItemClick (Item As ACMenuItem)
       
    Dim strQuery As String
       
    Dim cur As Cursor
       
    Dim value As String
         
    Dim acPopUp As ACPopupMenu 
         acPopUp.Initialize(
    "acPopUp", dummyPanel)       
         strQuery = 
    "SELECT DISTINCT(DATE) AS DT  FROM TABLE_RECORDS"
         cur =  sqlDB.ExecQuery(strQuery)   
          
    For i = 0 To cur.RowCount - 1
           cur.Position = i 
           value = cur.GetString(
    "DT")
           acPopUp.AddMenuItem(i, 
    DateTime.GetYear(value), Null)
          
    Next  
         acPopUp.Show
    End Sub
    The code above works the problem is that it loads on the menu all the years including those with 2 or more occurencies. How i will load only the distinct values of the query? Thank you!
     
  2. eps

    eps Well-Known Member Licensed User

  3. BitsAndBytes

    BitsAndBytes Active Member Licensed User

    This piece code extract the years , the DATE is a database field which stores DATE. My problem is that this code extract all the years as 2014 , 2014 , 2015 2015 , 2017 and not 2014 , 2015, 2017
     
  4. RauchG

    RauchG Active Member Licensed User

    Create a query to your table.

    " strftime('%Y', yourtable.yourdatefield) AS yearsort,"

    yearsort shows only 2014, 2015, 2016...

    Greetings
    RauchG
     
    BitsAndBytes and DonManfred like this.
  5. BitsAndBytes

    BitsAndBytes Active Member Licensed User

    Code:
    Sub acActionBarLight_MenuItemClick (Item As ACMenuItem)
       
    Dim strQuery As String
       
    Dim cur As Cursor
       
    Dim year As String
       
    Dim list As List 
       
    list.Initialize
         
    Dim acPopUp As ACPopupMenu 
         acPopUp.Initialize(
    "acPopUp", dummyPanel)       
         strQuery = 
    "SELECT strftime('%Y', TABLE_RECORDS.DATE) AS YEAR  FROM TABLE_RECORDS WHERE FARM_KEY=?"
         cur =  sqlDB.ExecQuery2(strQuery, 
    Array As String (Module.intContactCode)) 
         
    For i = 0 To cur.RowCount - 1
           cur.Position = i
           year = cur.GetString(
    "YEAR")
           acPopUp.AddMenuItem(i, year, 
    Null)
         
    Next
         acPopUp.Show
    End Sub
    Does not work , if i have 3 records 01/01/2015, 01/01/2016, 01/01/2017 This fills the action menu with 1698,1698,1698 :(
     
  6. Mahares

    Mahares Well Known Member Licensed User

    Use the SUBSTR function like this:
    SELECT DISTINCT SUBSTR(DATE,7) AS DT FROM TABLE_RECORDS
     
    BitsAndBytes likes this.
  7. eps

    eps Well-Known Member Licensed User

    Code:
    SELECT DISTINCT strftime('%Y', startdate) y FROM event
     
    BitsAndBytes likes this.
  8. BitsAndBytes

    BitsAndBytes Active Member Licensed User

    ..again it retuns me 1698 as result ..
     
  9. eps

    eps Well-Known Member Licensed User

    What is the DateFormat you are using?

    I use the following in SQL:

    CCYY-MM-DD

    and define

    Code:
    DateTime.DateFormat= "yyyy-MM-dd"
     
    BitsAndBytes likes this.
  10. Mahares

    Mahares Well Known Member Licensed User

    Did you try the code I have for you in post #6. It works if your dates are stored as 01/01/2015, 01/01/2016, 01/01/2017
     
    eps likes this.
  11. RauchG

    RauchG Active Member Licensed User

    Can you upload your project?

    Greetings
    RauchG
     
    BitsAndBytes likes this.
  12. BitsAndBytes

    BitsAndBytes Active Member Licensed User

    I am using dateformat like "dd-MM-yy"
     
  13. eps

    eps Well-Known Member Licensed User

    Yes, but you are storing it in SQLite in an 'invalid' way - well as far as SQLite is concerned... It likes dates to be CCYY-MM-DD.

    Sorry!

    You might be able to put a View in place to give you the correct date format - this is why strftime doesn't work, your dates are stored as DD/MM/CCYY, it's not expecting that.

    Either store them differently or use Mahares' solution.

    If you store them 'correctly' - for SQLite you would be able to extract them to DD/MM/CCYY for display purposes.
     
    lemonisdead and BitsAndBytes like this.
  14. RauchG

    RauchG Active Member Licensed User

    SQLite does not recognize "dd-MM-yy" as a date. Take "yyyy-MM-dd".

    Greetings
    RauchG
     
    BitsAndBytes and lemonisdead like this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice