Android Question Database load into listview how?

Discussion in 'Android Questions' started by schemer, Feb 11, 2015.

  1. schemer

    schemer Active Member Licensed User

    Ok, I am a noob at SQLite but managed to create a db. Now even though I have read a lot of stuff I am not sure I found the answer. My db will be "read only" and lots of examples are using the db in the more normal way of adding and deleting records. I will not be adding or deleting records nor will the end user. I also want to be sure I can even use the listview instead of maybe a better choice to load my records and data into. But for starters, how do I load the db and fill the list? I have tried the Dim SQL1 AS SQL etc but I am having trouble reading the data into the listview. What I want to do is be able to click on a list item and in turn access related data from another column to further process.
    Thanks in advance,
  2. lemonisdead

    lemonisdead Well-Known Member Licensed User

  3. Rob Rendle

    Rob Rendle Member Licensed User

    DBUtils is very useful.

    However try something like this (some of this was taken from a help guide posted somewhere on these forums)

    Note that DBFileDir, DBFileName have been set in my process_globals, like this :-

    Dim DBFileName As String                : DBFileName = "rdb.sql"
    Dim DBTableName As String                : DBTableName = "Rec"
    Dim DBFileDir As String                    : DBFileDir = File.DirInternal
    Create two subs (ListViewInit & ListViewFill)

    And in Activity_Create, loadlayout, initialize SQL1 and then underneath put ListViewInit and ListViewFill (example below)

    Then in your Init sub, create the listview (i.e your single line layout color and so on), then in your ListViewFill query your DB and send the string to the ListView.

    Example :-

    Sub Activity_Create(FirstTime As Boolean)
        SQL1.Initialize(DBFileDir, DBFileName, 

    End Sub

    Sub Activity_Resume

    End Sub

    Sub Activity_Pause (UserClosed As Boolean)

    End Sub

    Sub ListViewInit
        rlv.TwoLinesLayout.Label.Left = 
        rlv.TwoLinesLayout.Label.Width = 
        rlv.SingleLineLayout.Label.Color = 
        rlv.SingleLineLayout.Label.TextColor = 
    End Sub

    Sub ListViewFill

    Dim txt As String
    Dim cur As Cursor

        txt = 
    "SELECT Name FROM Rec WHERE Nationality ='Indiann" & currname & "'"

        cur = SQL1.ExecQuery(txt)
    For i = 0 To cur.RowCount -1
            cur.Position = i

    End Sub
    schemer, DonManfred and lemonisdead like this.
  4. schemer

    schemer Active Member Licensed User

    I have looked at DBUtils but it seems I didn't understand what to do in my case as most or all the examples were creating a db, writing to a db, or deleting records etc. Of course I am a noob at database stuff so I may have had the answer in there but couldn't see it. :confused: For some reason I am struggling with database stuff and have given up in the past on trying to figure it out, but then come back to it and try again as I really want to understand and use them. I think the syntax scared me. Anyhow, I am going to learn this stuff if it kills me. ;)
  5. schemer

    schemer Active Member Licensed User

    Thanks for your most informative reply. I will see how it goes in a little while after I test the code. I really appreciate the jump start. Which example in DBUtils is the closest to what I am trying to figure out? And who knows, in the beginning (only have had B4A for about 3 weeks now) I was using the emulators and the more I learn I realize using a real device makes a big difference. Maybe I was closer than I thought (doubt it) and the emulator hosed me. :p From now on I am going to use a device. So that leaves the question...What good it the emulator? Does it have its uses like for layout only?
    Thanks again,
  6. Rob Rendle

    Rob Rendle Member Licensed User

    In all honesty I found the student b4a course pdf the most helpful, there is a good sql example in there. Also, once you're familiar with that, have a look for erels and klaus's sql examples.

    Likewise in reference to not having b4a for long, im still learning myself and I've not used the emulator.

    The app I've been working on is heavily sql dependant so if you have any more questions please do ask. I'm no expert mind you :)
  7. schemer

    schemer Active Member Licensed User

    I appreciate the offer and I will be sure to take you up on it. Sometimes you can read and study until you are blue in the face then all of a sudden it clicks. Still waiting for that part. :D I am working on the example now and have my first error:

    Where is the proper location of the db besides adding it to the files tab? The project folder?
  8. Rob Rendle

    Rob Rendle Member Licensed User

    I'm answering this away from my PC so no code snippets.

    Make sure in process globals you have -

    Dim SQL1 As SQL

    Ok so all files you add to b4a will be in DirAssets once you've compiled. However as that is part of the APK you wouldn't be able to use your app to update your db. So its recommended that you run a copy sub so that when your app is opened, your sql db is copied from assets to either the sdcard or the phones internal memory.

    Search these forums for 'copy from DirAssets to dirinternal' for some good code snippets.

  9. schemer

    schemer Active Member Licensed User

    Yeah, I figured out the missing Dim on the SQL1 already...I am down to the editing of the SELECT statement. I will work on it and try to figure the rest out but will be back later if I don't. Thanks for the added info on the copy sub.
  10. schemer

    schemer Active Member Licensed User

    I am back. ;) I am getting an error here in the
    Sub ListViewFill:

    txt = "SELECT * FROM 'my_names'";

    B4A version 4.30
    Parsing code. Error
    Error parsing program.
    Error description: Input string was not in a correct format.
    Occurred on line: 64
    txt = "SELECT * FROM my_names";

    What is the correct syntax or is the underscore illegal in a column name?

  11. eurojam

    eurojam Well-Known Member Licensed User

    the select statement should be user like this: "SELECT * FROM tablename;" which selects all colums from the table tablename or "SELECT column1, column2 FROM tablename;"
    In your case you wrote: txt = "SELECT * FROM 'my_names'"; you can not quote the tablename like you did: 'my_names'. This will cause an error. Quotes are only used in the WHERE Part if you want to compare strings like: "SELECT * FROM tablename WHERE name='stefan';"

    good luck
    schemer likes this.
  12. Rob Rendle

    Rob Rendle Member Licensed User

    It would be good to know what exactly you're trying to display.

    Usually the query will be something like

    "SELECT * tablenamehere etc etc"

    So in the query I had in my code I wanted to display all names in my table called rec where the column nationality had the word Indiann which ended up like this

    "SELECT Name FROM Rec WHERE Nationality ='Indiann" & currname & "'"

    Sorry I'm away from my PC for the night. I belive in that college b4a course pdf you'll find the exact query that you're looking for.

  13. schemer

    schemer Active Member Licensed User

    Good catch stefan. I missed it by this much! Still does not work but it gets me past the error and then I get a blank screen. So I am getting closer. I will do some more reading as I probably am missing something simple.
  14. schemer

    schemer Active Member Licensed User

    I am just trying to display some text strings. Nothing special, just words. I will look at that college B4A course and do some reading. Thanks for all the help.

    p.s. I found the college b4a course and downloaded it. I had not heard of or seen that before. I will be reading it for sure. Thanks again.
  15. Rob Rendle

    Rob Rendle Member Licensed User

    Look for page 22. It has pretty much exactly what you're trying to do.

    Glad I could be of help.

    schemer likes this.
  16. DonManfred

    DonManfred Expert Licensed User


    txt = "SELECT * FROM 'my_names';"
  17. klaus

    klaus Expert Licensed User

    Did you have a look at chapter 4 SQLite Database in the User's Guide ?
    Did you have a look at the three SQLiteLight examples, links in my signature ?
  18. schemer

    schemer Active Member Licensed User

    Hi Don,
    Tried that and it still does not work. I just woke up after dreaming about db programming all night so maybe today I will have better luck. My problem may end up in my listview setup. I will drink some coffee and walk the dog, and eat some breakfast before I start today. :p
  19. schemer

    schemer Active Member Licensed User

    Hi Klaus,
    I just looked and I already downloaded the examples the other day but did not check them out as they are still zipped. :eek: I will be looking at those and also reading chapter 4 today. I will get this yet. :D
  20. schemer

    schemer Active Member Licensed User

    Well, I am making some progress. It seems I had tried to initialize some stuff like the listview in code so that gave me some trouble. :confused: But I am making progress. Now I just have to figure out why I get this error:

    android.database.sqlite.SQLiteException: no such table: mytable (code 1): , while compiling: SELECT * From 'mytable';

    I may just quickly create a new small database for testing. I also think I have a few questions on making a copy of a database. Can you just copy a database file in Windows and put it in another folder then rename it?
  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