Some help with SQLite3 needed

[irix]

Hi all,

I'm currently working on a database driven tool to enter my video collection.
I want to describe what kind of video is on the video tape, this should also be searchable.

I've got it working in one way: I can enter all data and all data gets written into the database. I can also update the data.
But as there is very little documentation on SQLite3 I'm stuck at the moment.
What I want is as soon as I type a tape number in the tape number input field, let's say I want to edit the tape number 25, so I type in 25, click on the button next to it and it should get all records for tape 25 and show this in the correct fields.

I've got this going (in a dialog just to see what's going on):

bandnummer = Input.GetText("Bandnummer");
BandSelect = String.ToNumber(bandnummer)
Resultaat = db:exec("select * FROM videobanden WHERE bandnummer = "..BandSelect);
Dialog.Message("Test", Resultaat, MB_OK, MB_ICONNONE)

I've made sure the Bandnummer input only accepts numbers by having an input mask.
Resultaat always gives me 1
Nothing else

I still need to fill about 80 other items from this database, but I can't even get 1 to work. Any help on this will be appreciated!

It needs to set some checkmarks (or not) (the database contains either 0 or 1 in that fields), it needs to fill 4 input fields per line (maximum of 20 lines) so there is a lot going on. How can I get what I need like in:

Loop from 1 to 20
Get value from database field x, put that value in my program in field x
Get value from database field y, put that value in my program in field y

I know the looping stuff, as it does work for entering the data into the database (min, max, count) which might not be the most efficient way, it's the only way I know.

Bottom line: I need to read the data from the db if that record exists and fill this in all fields on the page.

[irix]

Still looking for help. Any help or documentation is welcome. Thanks!

[Ulrich]

db:exec() returns a Cursor. You cannot show a Cursor directly, instead use cursorname:fetch() and get the results as a table.

The documentation for LuaSQL can be found here. There are some examples as well.

If you require more specific help, please post the exported project (*.apz), containing the database and your current code.

Ulrich

[Ulrich]

I attached a sample project, which performs a simple query on a SQLite3 database, and displays the results in a Grid object. You can see how you make a connection, perform a query, and fetch the results.

As SQLite3 does not use ANSI for storing text fields, you may need to convert the data before you can display the text in AutoPlay Media Studio. For this, I used my Unicode plugin in this sample.

Data displayed without conversion:


Data properly converted:


Ulrich

[irix]

That's great!
The grid will come in handy when I want to search, but how can I extract every single line in order to fill multiple inputs in the application?

So separate fields for name, address, city and country, not in a grid.

[irix]

Maybe a grid is the way to go, but can I put ticks or images in the grid (I have 2 fields per row that have to be filled by checkboxes)
Can I make a grid writable so I can enter data and save the data to the database with a button?

[Shrek]

You could with the http://www.memoryex.net/MemoryEx plugin use the Windows ListView control and have images (icons) for each cell. If you have GridEx you could achieve the same result.

[irix]

But will that fill the grid with new information as well?

This is a small portion of what I have now, this is only the fill in/change screen.



It's a test version, no layout made yet, just functionality.

[Shrek]

nothings automatic until you make it so, you'll need to start a timer and check for changes then apply the changes.

[irix]

It shouldn't check for changes, I just want to fill stuff in, then press a button to save changes or new records.