sql how-to ??

[mitologys]

I have a question.
I am a rookie with sql and i want to know if is possible to have a db (already full) with five colums and a variable number os lines, to read this lines one by one selected by different object´s, to display the information of that line in a determined area of my autoplay project.
Are there any examples for this??
Where can i see more of this??

Can anybody show me whow this is possible
Sorry if my english is bad.

[TJ_Tigger]

There are several great examples that users have posted. If I have read your post correctly, it is very easy to do what it is you have requested. Use the Search function above to search on SQLite and you will find a lot of posts with examples to do what you want. I also believe that there is a training CD Volume 3 that covers topics like this.

Tigg

[mitologys]

already read many thing´s but i still have to big question´s

1 How do i convert a excel file into a db ( i have only reached a xml file with a program made with ams )
and
2 How can i read the information from the db and showing into the project ( the file must go inside the cd )

[TJ_Tigger]

To answer your two questions

1. I would either build an AMS tool to convert an Excel (.csv) to a SQLite database or use something like Sqlitebrowser (search to find site) to import from an Excel spreadsheet. Or if you are really creative, you may be able to use the Luacom plugin to import from Excel directly.

2. Once you have the information in a database, I would use the SQLite.QueryToTable to extract the information I needed and then post that information to the page.



tbReturn = SQLite.QueryToTable("dbname", 'SELECT * FROM Table1 WHERE Company = "'..strCoName..'"');
if tbReturn.Rows > 0 then
Input.SetText("Input1", tbReturn.Data[1]["address"]);
Input.SetText("Input2", tbReturn.Data[1]["city"]);
Input.SetText("Input3", tbReturn.Data[1]["state"]);
Input.SetText("Input4", tbReturn.Data[1]["zip"]);
Input.SetText("Input5", tbReturn.Data[1]["address2"]);
Input.SetText("Input6", tbReturn.Data[1]["phone"]);
end


Or something like that anyway.

Tigg

[TJ_Tigger]

BTW if you don't have the SQL plugin, you could do something very similar by reading in the .csv directly using the "," as a delimiter. There is a great scriplette that will read in a delimited list an put it in a table for you.

[mitologys]

Now i know who to make a db ( done)
Who to read from the tables( one row of the time )

Now i have two problems

1 - when i click on the first time it read all that i want, but if i click on a different button that has as path for another row only at the second time that i click on it is that the new information appears

2 - I want to show a pdf file when i click on a button i hane tried this :
Web.LoadURL("Web1", _SourceFolder .."AutoPlay\\Docs\\Casas\\" .. tblRecordSet.Data[1].PDF);
but doesn´t work

What am i doing wrong ?????

[mitologys]

About question Nº 1 after all it takes +-5 seconds to change information who can i do to fix it ???

[TJ_Tigger]

If the column name in the database is "PDF" then try this


Web.LoadURL("Web1", _SourceFolder .."AutoPlay\\Docs\\Casas\\" .. tblRecordSet.Data[1]["PDF"]);


I don't know why it takes +- 5 seconds to change the information. What version of AMS are you using and what speed of PC are you using it on? Is debugging durned on within your app? Can you post some sample code or a project for us to view and maybe we can help find what is going on.

Tigg

[mitologys]

AMS pro 5.0.2.0

AMD Athlon XP 2000+

Code from the button that must open a pdf ( tried with "" and nothing )
This button is on page 1

Paragraph.SetVisible("r1", false);
Image.SetVisible("mapa1", false);
Image.SetVisible("Foto1", false);
Web.SetVisible("Web1", true);
Web.LoadURL("Web1", _SourceFolder .."AutoPlay\\Docs\\Casas\\" .. tblRecordSet.Data[1]["PDF"]);
Label.SetVisible("mapageral", true);
Image.SetVisible("mapasmall", true);
Button.SetVisible("lista2", true);


Code from page

db = SQLite.Open("AutoPlay\\Docs\\habitacao.db");
Label.SetText("Proprietario", "Proprietário : " .. tblRecordSet.Data[1].Proprietario);
Label.SetText("Pessoa", "Pessoa : " .. tblRecordSet.Data[1].Pessoa);
Label.SetText("Morada", "Morada : " .. tblRecordSet.Data[1].Morada);
Label.SetText("Data", "Data : " .. tblRecordSet.Data[1].Data);
Label.SetText("R", "R " .. tblRecordSet.Data[1].Relatorio);
Image.Load("Foto1", _SourceFolder .."\\Autoplay\\Images\\" .. tblRecordSet.Data[1].Imagem);
Image.Load("mapa1", _SourceFolder .."\\Autoplay\\Images\\" .. tblRecordSet.Data[1].Imagem2);

Code from one od the button´s to choose

tblRecordSet = SQLite.QueryToTable(db, "Select * from habitacao where Relatorio = 1;");
Page.Jump("1");
Image.SetVisible("m1", false);

this button (links) are in several pages, all of them are connected with page 1 by the "object from page

[TJ_Tigger]

Do your images and text load correctly from the table created in the SQLite.QueryToTable action.

[mitologys]

the maximum time is 1 second of delay

[TJ_Tigger]

the maximum time is 1 second of delay

I don't understand this answer. When you perform your query and set the text and images like this?


db = SQLite.Open("AutoPlay\\Docs\\habitacao.db");
Label.SetText("Proprietario", "Proprietário : " .. tblRecordSet.Data[1].Proprietario);
Label.SetText("Pessoa", "Pessoa : " .. tblRecordSet.Data[1].Pessoa);
Label.SetText("Morada", "Morada : " .. tblRecordSet.Data[1].Morada);
Label.SetText("Data", "Data : " .. tblRecordSet.Data[1].Data);
Label.SetText("R", "R " .. tblRecordSet.Data[1].Relatorio);
Image.Load("Foto1", _SourceFolder .."\\Autoplay\\Images\\" .. tblRecordSet.Data[1].Imagem);
Image.Load("mapa1", _SourceFolder .."\\Autoplay\\Images\\" .. tblRecordSet.Data[1].Imagem2);

Does it work?

I see that you are opening your database in the above code and then setting the text/images based on a database query but it doesn't appear that the query to populate the tblRecordSet is happening until a button is pressed. Is the database open when you perform your query?

Tigg

[mitologys]

I have a db with almost 2000 entries and i will make 2000 button´s ( cliking in a button makes a selection in the db

Code
tblRecordSet = SQLite.QueryToTable(db, "Select * from habitacao where Relatorio = 1;");
Page.Jump("1");

After selecting, it changes to the page that as the "print" code and after the page apears it takes the maximum of 1 second to show it all.
If i click on a second button´s it will take +- 5 seconds to change information

This code
db = SQLite.Open("AutoPlay\\Docs\\habitacao.db");
Label.SetText("Proprietario", "Proprietário : " .. tblRecordSet.Data[1].Proprietario);
Label.SetText("Pessoa", "Pessoa : " .. tblRecordSet.Data[1].Pessoa);
Label.SetText("Morada", "Morada : " .. tblRecordSet.Data[1].Morada);
Label.SetText("Data", "Data : " .. tblRecordSet.Data[1].Data);
Label.SetText("R", "R " .. tblRecordSet.Data[1].Relatorio);
Image.Load("Foto1", _SourceFolder .."\\Autoplay\\Images\\" .. tblRecordSet.Data[1].Imagem);
Image.Load("mapa1", _SourceFolder .."\\Autoplay\\Images\\" .. tblRecordSet.Data[1].Imagem2);

works

I have the button´s pages ( all of them open the db ) opening the db, my page 1 has the "object from page" pointed to the first page of button´s

Do you need the program code to see it better ??

[TJ_Tigger]

If you can e-mail or post the project I will see how it works on my end if you want.

Tigg

[mitologys]

Know i have a different problem, i need to open a pdf file using a db and i wrote this

Web.LoadURL("Web1", _SourceFolder .."AutoPlay\\Docs\\Casas\\" .. tblRecordSet.Data[1].PDF);

But it does not work, does anyone has an idea how to fix it ??

[TJ_Tigger]

Mitologys,

Try this.
[code]
Web.LoadURL("Web1", _SourceFolder .."\\AutoPlay\\Docs\\Casas\\" .. tblRecordSet.Data[1].PDF);
[code]

I put the backslashes infront of the AutoPlay directory.

Tigg

[mitologys]

thanks, works just fine