ListView & Database

[diddly101]

Hey all,

Im still new to this whole app creation world, so thought i might be able tog et some help as i cant find and tutorials thus far on my specific problem.

What i am trying to do is create a list, that when the list item is pressed it opens a new layout that displays a picture, the name of the Event (same as list item), date, time, location & Info.

I have created simliar sorts of programs, although creation in B4a is a little different which is making it a little harder.

My thoughts would be to create a database table firstly with all of those headings as column name

Sub CreateTable
SQL1.ExecNonQuery("DROP TABLE IF EXISTS EventsTable")
SQL1.ExecNonQuery("CREATE TABLE table1 (Date DATE , Time TIME, Name TEXT, Location Text, Co-Ords Text, Information TEXT, Picture Text)")
End Sub

Then from that i would populate the table

Sub FillTable
SQL1.ExecNonQuery("INSERT INTO EventsTable VALUES(28/11/2012, 08:40, 'Welcoming', 'Main Grounds', 'XX XX', ' event where everyone is welcomed to rottenest and info is given out to all, Welcoming.jpg)")
End Sub

Then once the database is created i would create the designer with a ListView named ListV which i would populate with the names of the instances that have been inserted into the database

Sub Populating_List
Try
For i = 1 To SQL1.ExecQuerySingleResult("SELECT count(*) FROM EventsTable")
ListV.AddSingleLine(SQL1.ExecQuerySingleResult("SELECT Name FROM EventsTable"))
Next
SQL1.TransactionSuccessful
Catch
Log(LastException.Message)
End Try

End Sub



well thats the plan anywayz, although i have run into a few syntax errors as i am still not use to system as of yet, so would anyone be able to give me a hand (mainly in the populating the listView) or if you have a better suggestion as how to tackle the problem feel free to add

kindest regards
luke

 

[klaus]

It would be much easier if you posted your project as a zip file (IDE menu Files / Export As Zip) .
So we could see what you have done and how and give concrete advices.

Looking at your code:
SQLite doesn't have DATE nor TIME data type.
You should change this line:

SQL1.ExecNonQuery("CREATE TABLE table1 (Date DATE , Time TIME, Name  TEXT, Location Text, Co-Ords Text, Information TEXT, Picture Text)")

To

SQL1.ExecNonQuery("CREATE TABLE table1 (Date INTEGER , Time INTEGER, Name  TEXT, Location Text, Co-Ords Text, Information TEXT, Picture Text)")

and use Date / Time functions.
Have a look
here Datatypes In SQLite Version 3
and here: SQLite Query Language: Date And Time Functions

To populate the ListView you should use a Cursor.

Have a look at chapters SQL and DBUtils in the User's Guide.

Best regards.

 

[diddly101]

the file

Hey ill attach the code that i have thus far, the main problem i am having is with the selection of the listview item and then using that item's name to query the database and retrieve information to put into the next page. i put a small comment on lines 52 and 55 in refference to this

 

[klaus]

Here you are.
There were many errors in your code.
One example:
- In the main layout you define a ListView with the name List and List EventName.
- In the program you Dim ListV as ListView
- For the ItemClick event you use ListView1_ItemClick
Three different names for the same purpose !?

I didn't comment all the changes I leave it up to you to look at the differences.

Best regards.

 

[diddly101]

Thank you so much Klaus, much appreciated

kindest regards
luke