Android Tutorial SQLiteDB

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 SQLiteDB_2D_v_1_2.zip

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

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

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.

 

[Merlot2309]

Hello Klaus,

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

:sign0098:

Greets,
Helen.

 

[devjet]

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

 

[klaus]

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.

 

[klaus]

Hi hackhack,
Answer to your question in the ScrollView examples summary thread.

Hm, do you never write comments when you program?

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.

 

[netchicken]

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.

 

[Erel]

You should download the Dialogs library and check it in the libraries tab.
http://www.b4x.com/forum/additional...ing-additional-libraries-index.html#post39672

After copying it to the additional libraries folder, right click inside the libraries tab and press Refresh.

 

[netchicken]

Thanks you

 

[hzimmer3]

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,

Helmut
Cologne/Germany

 

[klaus]

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.

 

[hzimmer3]

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...

Helmut
Cologne/Germany

 

[klaus]

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.

 

[hzimmer3]

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?

Helmut
Cologne/Germany

 

[klaus]

...the SQLiteDB, the button is there, however in landscape the
views are not correctly oriented and also the scscroll view
behaves strange.

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

By the way, German special vocals (ä, ü, ö) are not represented ...

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

Best regards.

 

[hzimmer3]

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?

Helmut

 

[hzimmer3]

Orientation problem in landscape SQLiteDB

Hello Klaus,

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

Helmut

 

[klaus]

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.

 

[Taha]

SQlite converter

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

 

[klaus]

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

Best regards.

 

[TomDuncan]

error

:sign0104:

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.

Tom