"Index" table with SQLite ?

[bonbon]

I'm just getting started with RunRev & SQLite. I've set up a simple table in an SQLite db (using SQLite Expert Personal), and I used the Database Query Builder to set up a query. I've put a couple of typ-in fields onto a stack, and linked them to the table, and I also set up first, prev, next and last buttons, linking them to the appropriate actions from the Database part of the inspector. So far, so good - it works.

A couple of questions:

- I would like to set up a scrollable, read-only grid on the same form, showing the key values from this table. The idea is to be able to navigate to the record you want by clicking a row in the table. I tried this using a table object, but if I navigate using the buttons on the form, the data gets corrupted - I get duplicate key values appearing.

- I would also like to assign keyboard values to the buttons: PageDown to the "Next" button, End to the "Last" button, and so on.

Any pointers would be much apprectated.

Thanks,

Pete

[bangkok]

You should have a look to the stack MySQL from Troz :

http://www.troz.net/Rev/tutorials/MySQL.rev.gz

You can use it with the public MySQL server from RunRev , for a live demo :

database name : "test"
login : "example"
password : "example"
address : "runrev.com"

This stack will give you ideas (and scripts easy to re-use) to do what you want, directly through scripts instead of using the Database manager.

First it lists the tables that are available on the server.
You click on one, then its list the structure of the table chosen, etc.

The syntax is the same between MySQL and SQL lite.

-so basically, first you have to make a SELECT theKey FROM myTable, to display all the keys of your table into one field

-then you put a script into the field to catch the key when you click on one line, in order to make a second SELECT (to display the record chosen)

Code: Select all

on mouseUp
  put the selectedText of me into theRec
end mouseUp

-then you make the second SELECT :
SELECT * FROM myTable WHERE theKey = theRec

Et voila.

The Database Manager is a nice idea, but I've found much easier (and powerful) to use scripts.

[bonbon]

Thanks for the pointer - I'll get cracking on that.

[bangkok]

There is another solution (i forgot), if your database is not too big : the "hidden columns".

Principle :

1-you put all the content of your table into one field,
SELECT * FROM myTable
but with only the first column (with the keys) visible. The others are hidden on the right (through Property Inspector -> Table - > Tab Stops : type a high value in pixels)

2-a script in the field allows to display the hidden columns datas in proper field, when you click on a line.

Code: Select all

 on mouseUp
 set the itemdelimiter to tab
  put the selectedText of me into theRec
  put item 2 of theRec into field "name"
  put item 3 of theRec into field "address"
  put item 4 of theRec into field "city"
end mouseUp