Android Tutorial SQLiteDB

Discussion in 'Tutorials & Examples' started by klaus, Jul 9, 2011.

  1. klaus

    klaus Expert Licensed User

    Below you find an application with a SQLite database and a ScrollView based table.
    What the program can do:
    - Read a database, 'Load' button.
    - Edit, modify or delete date sets (single- or multi-selection), 'Edit' button.
    - Edit and modify a single cell, 'Long_Click' on a cell in a selected row.
    - Filter the database, 'Filter' button, Click filter or unfilter, LongClick definition of filter criterias.
    - Sort a column ascending or descending, clickink onto the column header.
    - Create a new database, 'New' button.
    - Scroll the table verticaly with the standard Scrollview, and horizontaly on the blue line between the ScrollView and the toolbox (or with a SeekBar, hidden by default.
    - Change the table setup, alignment, column width, text size, colors, etc.

    What the program connot do:
    - Share multiple tables.

    I post it as a tutorial more to show what could be done, rather than a specific program in Share Your Creations.
    Questions, constructive critics, comments, wishes and bug reports are welcome.

    You can use the source code, or parts of it, for your own applications.

    EDIT: 2012.07.05
    Added a version using the ScrollView2D library.

    EDIT: 2013.02.08
    Amended error reported in post #27
    Updated zip file

    EDIT: 2013.02.09
    Amended error reported in post #30
    Updated zip file

    EDIT: 2013.04.19
    Eliminated error with Null values in a database reported in post #45.
    Updated zip file

    EDIT: 2013.07.01
    Eliminated some errors.
    Removed most of the compiler warnings.

    EDIT: 2014.02.09
    Amended bug reported in post #57
    Missing DBFilePath on btnLoad_Click

    Best regards.

    Attached Files:

    Last edited: Feb 9, 2014
  2. Merlot2309

    Merlot2309 Active Member Licensed User

    Hello Klaus,

    Thank you for this example. I will certainly use parts of it.


  3. devjet

    devjet Member Licensed User

    Hello Klaus,
    thanks for a great sample!!
    I have just done a search in the forum in order to get a bit more acquainted with DB features etc . Your sample is certainly a great help with many features to learn from.

    a) I have been wondering before and here again, in your application you are using buttons as a menu. Is this the preferred way? I would have thought that Activity.AddMenu would be better since it consumes less space on the screen . Is there a advantage, I have not figured out yet?

    b) to scroll a listing sideways is in portrait view is not so convenient,to display info.
    I would prefer a list of only ID ¦ First Name ¦ Last Name ¦ and when tabbed on it the detailed info could be shown bellow in edit controls. How could that be done?

    Thanks again & greetings
    Rockefeller Goldman likes this.
  4. klaus

    klaus Expert Licensed User

    Thank's, I am glad that the program, or parts of it, could be useful for you.

    a) In all my programs (VB Windows desktop, B4PPC, B4A) I have been using buttons instead of menus.
    Because I have:
    - an overview of the program's functions
    - the main functions directly available with one click
    - the whole control on them, hide no available functions, change button appearence according to the chosen function etc.
    With Windows and B4PPC you could have a lot of functions in a relative small space.
    Unfortunately with these 'i-padized' finger driven operating systems, buttons must have a bigger size and need more space, the same for all the other Views. Despite this inconvenient I still prefer buttons.
    The buttons are on the bottom of the screen, in portrait mode, to not hide the screen when touching them, and on the right side in landscape mode for right handers, they could be on the left side for left handers (not implemented yet).
    In Windows and B4PPC programs I used also ToolTips to show with text the function of a button. In B4A I haven't done it yet because the Touch event for buttons doesn't exist (yet?). The MouseDown event displays the Tooltip and MouseUp hides it, but the function is only executed when the mouse up coordinates are insides the button area, this allows to check the functions purpose without executing it.

    Is this the prefered way doing it ?
    For me yes, but that's only my personel feeling. Probably other people prefer menus.
    A weak point in Android is that you cannot change menu items after initialization.

    b) This wouldn't be that complicated. You could have one ScrollView with the list on the bottom of the screen and another Scrollview with the EditText Views on the top of the screen to not hide them with the virtual keyboard. I'm afraid that, depending on the number of fields, the space for the EditText Views would be too small.

    Best regards.
    Rockefeller Goldman likes this.
  5. klaus

    klaus Expert Licensed User

    Hi hackhack,
    Answer to your question in the ScrollView examples summary thread.
    Yes, I rarely write comments in my programs.
    I try using self explaining variable, object and subroutine names and most of the code looks obvious to me so I feel no need for comments. Too many comments, from my point of view, makes programs less readable.

    In the source codes for the Beginner's Guide there are more comments in.

    In what program and what part of it are you missing comments ?

    Do you write comments in your programs ?

    Best regards.
    Last edited: Jul 17, 2019
    Isa Paine likes this.
  6. netchicken

    netchicken Active Member Licensed User

    Hi Klaus, I am getting an error

    Compiling code. Error
    Error parsing program.
    Error description: Unknown type: filedialog
    Are you missing a library reference?
    Occurred on line: 80
    Dim FileDialog1 As FileDialog

    I just loaded it as normal.
  7. Erel

    Erel Administrator Staff Member Licensed User

  8. netchicken

    netchicken Active Member Licensed User

    Thanks you :)
  9. hzimmer3

    hzimmer3 Member Licensed User

    SQLiteDB Example problem

    Hello Klaus,

    I have studied your SQLiteDB_V1_0 example which principally runs on my
    HTC Flyer and is good starting point for tables manipulation.

    However, on my device I cannot find the Menu and New/Load buttons
    and the according functionality as mentioned here in the forum...

    Do I miss some libraries?

    Thanks in advance,

  10. klaus

    klaus Expert Licensed User

    In the lower right corner of the screen you have a button with an up arrow (left arrow in landscape).
    Clicking on this button shows a second toolbox with more functions, 'New' is there.

    Best regards.
  11. hzimmer3

    hzimmer3 Member Licensed User

    Missing Buttons in SQLiteDB example

    Hello Klaus,

    the arrow button does not show up in the bottom right corner on my device (HTC Flyer 7" tablet with Android 2.3.3).

    Also when editing an entry, the button panel shows up within the data panel right below the address edit.text view. The same happens for the button panel when specifying the filter criteria, the button panel appears just below the city edit.text view...

    I have added layout variants for 7" tablets in both landscape/portrait (800x400) orientation in the designer for edit, filter and main but with no effect...

  12. klaus

    klaus Expert Licensed User

    Hi Helmut,

    I haven't tried the program with big screens.

    What is the resolution of your screen ?
    800 / 400 looks strange, isn't it 800 / 480 ?
    I looked at the specifications of the HTC Flyer in the Interner and there the resolution is 1024 / 600 ?

    Best regards.
  13. hzimmer3

    hzimmer3 Member Licensed User

    Hello Klaus,

    yes - the resolution of my device is 1024x600.
    I mixed up the SQLiteDB example with the SQLExample, in
    the SQLiteDB, the button is there, however in landscape the
    views are not correctly oriented and also the scscroll view
    behaves strange.
    I am looking into this currently.

    By the way, German special vocals (ä, ü, ö) are not represented
    correctly (as ?), what can I do to fix this?
    Do I need a different character set?

  14. klaus

    klaus Expert Licensed User

    What does happen ?
    On my device 800/480/240 it works OK.
    Could you post a screenshot of the problem.

    Did you load a database. If yes this one must be saved with UTF-8 codepage.

    Best regards.
  15. hzimmer3

    hzimmer3 Member Licensed User

    Orientation problem in landscape SQLiteDB

    Hello Klaus,

    In Landscape, the button panel is above the scroll view
    within the list and moved to the right ...

    I wonder how I can make a screenshot from the device screen?

  16. hzimmer3

    hzimmer3 Member Licensed User

    Orientation problem in landscape SQLiteDB

    Hello Klaus,

    here comes a screenshot from the HTC Flyer in landscape with
    SQLiteDB running...


    Attached Files:

  17. klaus

    klaus Expert Licensed User

    Hello Helmut,

    I see now, it will need a layout variant for big screens.
    I will look at it in the next days.

    Best regards.
  18. Taha

    Taha Member Licensed User

    SQlite converter

    Can't find any free software for SQLite conversion from Access, can you recommend any? Sorry for off-topic.
  19. klaus

    klaus Expert Licensed User

    You could have a look at this post.
    And also here.

    Best regards.
  20. TomDuncan

    TomDuncan Active Member Licensed User



    Hi after I installed the extra libraries I get an error
    pnlHeader.Left = -Value in line 1158
    "Object should be initialized first"
    any ideas.

    Also as a new person to A4B (I use Delphi in the PC world)
    why do you create all your windows in code not in the designer.
    Also how can you visualize what the screen will look like as well.
    I find it so much easier to see the visual look and feel of the project first.

    Most of my applications are database aware types and it would be so nice to have things like dbgrid and other dbaware components to use.

  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