Android Question Clear data in SQLLite table [Solved]

Discussion in 'Android Questions' started by Roger Daley, Oct 10, 2019 at 3:08 AM.

  1. Roger Daley

    Roger Daley Well-Known Member Licensed User

    High all,

    I am loading a CSV file into a SQLLite table, the code below works well for appending data to the existing data but i want to give the user the option to replace the existing data. It may require data to be cleared before loading new data or possibly there is an overwrite option.
    I see 3 options:
    1. Drop the table and recreate it. [Brutal]
    2. Delete each record by iteration. [Tedious]
    3. Overwrite existing data?

    Is there some way [DBUtils??] to clear data from the table in the db?

    Regards Roger

    Sub BtnImpSiteList_click
    ' This routine:
        ' Checks PERMISSION_WRITE_EXTERNAL_STORAGE.  Creates folder ABT if required
        ' Checks for the file "sitelist.csv" in the directory File.DirRootExternal&"/ABT/", "sitelist.csv".
    'Close Menu
        PnlMenu.Left = -PnlMenu.Width

    Wait For Activity_PermissionResult (Permission As String, Result As Boolean)
    If Permission =  "android.permission.WRITE_EXTERNAL_STORAGE" And Result =  True  Then
    If File.Exists(File.DirRootExternal&"/ABT/""") = False Then File.MakeDir(File.DirRootExternal&"/ABT/""")
    End If
    ' If the file is not found the user briefly sees a short "File not found" message.
        ' If the file is found the user is given the choice to append this file to the existing sites db, replace the existing data or cancel the operation.  WIP
        ' If the user chooses to proceed the routine will check the file for valid data and no futher action is required by the user.   WIP
    If File.Exists(File.DirRootExternal&"/ABT/""sitelist.csv") = False Then
    "File 'sitelist.csv' not found",1000,Gravity.TOP,0,25%y)
            ' First record may be heading.
            ' Each record check first colum for text.  If no text assume end of list and exit.
            ' If text is found check the next colum for a valid number -180 to +180.  If true check 3rd colum for valid number -90 to +90.
            ' Write 3 colums

    Private su As StringUtils
    Private Table As List
            Table = su.LoadCSV(
    Private Table2 As List
    Private Items() As String
    Private TestTrue As Boolean
    For i = 0 To Table.Size - 1
                TestTrue = 
                Items = Table.Get(i)
    'checks for blank in first colum? if so loading is complete
                If Items(0) = "" Then Exit
    'Next     check if second colum is valid Longitude   -180 to 180. 
                If IsNumber (Items(1)) Then
    If Items(1) < -180  Or Items(1) > 180 Then TestTrue = False
                    TestTrue = 
    End If
    'Next     check if second colum is valid Latitude  -90 to 90.     
                If IsNumber (Items(2)) Then
    If Items(2) < -90 Or Items(2)> 90 Then TestTrue = False
                    TestTrue = 
    End If

    If TestTrue Then
    Private m As Map
    "SiteName", Items(0))
    "Longitude", Items(1))
    "Latitude", Items(2))
    End If
    "sites", Table2)
    'Display site list
            wbvTable.Left = 0
            lblSelectedItem.Left = 
            BtnSitesBack.Left = 
            pnlToolBox.Left = 
            BtnSunDial.Visible = 
            SitesFlag = 
    "Upload complete",1000,Gravity.TOP,0,25%y)
    End If
    End Sub
    Last edited: Oct 10, 2019 at 3:14 AM
  2. mangojack

    mangojack Well-Known Member Licensed User

    I have not tried it , but there is a Truncate command.

    The TRUNCATE TABLE command deletes the data inside a table, but not the table itself.

    EDIT ** It would appear that TRUNCATE is NOT a SQLite Keyword / action.
    Last edited: Oct 10, 2019 at 6:02 AM
    Roger Daley likes this.
  3. mangojack

    mangojack Well-Known Member Licensed User

    I have just realized you are using DBUtils ... so unsure if there is any way to clear a db Table from there.

    For your knowledge ... the DELETE keyword without any Trigger / Where clause has the same result as TRUNCATE , ie:

    It will delete all records and clear / reset the internal record ID's

    SQL1.ExecNonQuery("DELETE FROM MyTable")
    Roger Daley likes this.
  4. Roger Daley

    Roger Daley Well-Known Member Licensed User

    Thanks mangojack,

    Delete looks good. I'm on the road at the moment but I will give it a try when I get back and report on the results.

    Regards Roger
  5. Roger Daley

    Roger Daley Well-Known Member Licensed User


    Your suggestion worked perfectly. I inserted the line into the code above just before the DBUtils.insertmap line and it did the job.

    I also tried
    Private MP As Map
    but the program did not like the empty map. Perhaps I have the wrong syntax.

    Thanks again
    Regards Roger
  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