show returned data from query into fields

[francof]

ciao all, first of all sorry for my rough english.
yes I now, I'm completely ignorant of databases. after long research about how put my data into fields on the card I resolved so:

(the db file is a .mdb, I open it in ODBC mode.)

below the query that return 3 fields of the table TBLDISTILLATI. the fields are: DESCRIZIONE, ZONAPROD, MATERIAPRIMA

Code: Select all

put "SELECT DESCRIZIONE, ZONAPROD, MATERIAPRIMA from TBLDISTILLATI WHERE DISTILLATO = '"& gSelezione &"' " into tSQL

then I show data:

Code: Select all

    set ItemDel to tab
    put item 1 of tRecords into field "fldDescr"
    put item 2 of tRecords into field "lblZonaProd"
    put item 3 of tRecords into field "lblMatPrima"

Hit I the nail on the head? or there is a better and formal way?

I come from Visual Basic where, in this case, I could refer directly to the names of the fields of the DB specified in the query.

thanks for your attention
franco

[Klaus]

Buongiorno Franco,

yes, that is the way to go

You could use a repeat loop however like this:

Code: Select all

...
set ItemDel to TAB
put "fldDescr,lblZonaProd,lblMatPrima" into tFields
repeat with i = 1 to 3
    put item i of tRecords into fld (item i of tFields)
end repeat
...

Best

Klaus

[francof]

Gutenmorgen Klaus, grazie 1000! you solve my doubt.

I am glad that my attempt has proved to be accurate, at least in concept. certainly yours way it's more smart and efficient. so, I think that I will use it.
in case of a lot of data the populating loop is certainly to be preferred.

one more question: there is some tutorial or guide about databases management? (e.g. queries with variables, filling fields with returned data, ect.) all I found on LiveCode Lessons refers to very simple situations of limited use in the real world.

thanks again for your help
franco

[Klaus]

one more question: there is some tutorial or guide about databases management? (e.g. queries with variables, filling fields with returned data, ect.)
all I found on LiveCode Lessons refers to very simple situations of limited use in the real world.

sorry, but I don't know.

Just ask here

[francof]

Hi again Klaus,
unfortunately I got this error
" card "crdDistillati": execution error at line 65 (Chunk: no such object), char 1 "

at line

Code: Select all

put item i of tRecords into fld (item i of tFields)

something is missing?

franco

[Klaus]

Hi Fanco,

oops, yes, sorry, my fault, forgot the new itemdel!

Do this:

Code: Select all

...
set ItemDel to TAB
put "fldDescr,lblZonaProd,lblMatPrima" into tFields
replace COMMA with TAB in tFields
repeat with i = 1 to 3
    put item i of tRecords into fld (item i of tFields)
end repeat
...

Best

Klaus

[francof]

ciao Klaus,
don't be ..... now it's working fine.if we ever meet, remember that, you got a free beer

franco

[SparkOut]

If Klausimausi ever meets up with everyone who says that, he'd better take very good care of his liver!

[Klaus]

If Klausimausi ever meets up with everyone who says that, he'd better take very good care of his liver!

[francof]

If Klausimausi ever meets up with everyone who says that, he'd better take very good care of his liver!

ah ah! I just said "A" (one) beer, and anyway I'll make a sacrifice

ciao all
franco

[SparkOut]

I meant there are a LOT of people who have said they will buy A beer for Klaus.

[francof]

I meant there are a LOT of people who have said they will buy A beer for Klaus.

Hi SparkOut,
excuse me. now I understand, as I said my English is very bad.

[AxWald]

Hi,

one more question: there is some tutorial or guide about databases management? (e.g. queries with variables, filling fields with returned data, ect.) all I found on LiveCode Lessons refers to very simple situations of limited use in the real world.

As you write in your first post you're working with a .mdb. So you might have MS Access at hand and can use its query builder.
Building complex queries using the design view (or even using the wizards) is quite easy, and then you switch view to SQL - and voilá, nice juicy SQL code to copy out ;-)

This is a very handy thing, and since Access can connect to nearly every database engine (via ODBC) you can prepare your SQL statements there at first, check the results, and then copy the tested code.
For sure, some DBs will need minor changes in the syntax, but that's nothing a good text editor cannot do for you ;-)

But it's priceless when the SQL statements grow longer, writing multi-line code with many parentheses and single/ double quotes, inserted variables, line breaks - without knowing beforehand if you used the correct join ...

If I remember correctly, Access comes with an example database (northwind.mdb) that is quite useful to learn the basics of using structured data in a relational environment ;-)
There's alternatives, but I have yet to find a tool that makes it such simple to create even the most esoteric queries.

For sure, if you don't have it already, Access is quite a bummer when it comes to buying it. For this, I'd look at EBay for the 2003 version, comes for quite small money often & is quite good (Careful, there's MS Office versions that doesn't include Access!).

Have fun!

[francof]

Hi AxWald,
thanks for your reply.

.....
But it's priceless when the SQL statements grow longer, writing multi-line code with many parentheses and single/ double quotes, inserted variables, line breaks - without knowing beforehand if you used the correct join ...
....

yes, this is half of the job.

although I do not use it too much i've the office 2007 version. curiously the query builder button it's always grayed out.
I tried to turn-on it unsuccessfully.

franco

[AxWald]

Hiya!

[...] curiously the query builder button it's always grayed out.
I tried to turn-on it unsuccessfully.

Don't you get something like this?

The query builder in Access

Then you might have an Access Runtime version :/

(The upper part is where you can show tables, link them at your desire, designate the fields you want to use in your query, add critera, sorts, and can do calculations.
Then you test it, and if OK you switch view to the lower part: Nicely automagically created SQL code ;-) )

Have fun!