Just another quickie

[dulux1309]

Is there a quick method of returning the contents of a query back to an object? At the moment, I am querying the database for all rows and would like to print the results to a box on the layout as opposed to a debug window, although code to do this would also be appreciated. Once I have this handled, I'll be out of your hair for a while, I promise...

[Worm]

You can use QueryToTable, then use:

TableName.Data[Row]["FieldName"]

to display the info held in that field

Member_num = Input.GetText ("Input1");

--if Member_num is a text field use this
tblResult=SQLite.QueryToTable(db,"SELECT * FROM MEMBERS WHERE Member_num = '" .. Member_num .. "'")

--if Member_num is a numeric field use this
tblResult=SQLite.QueryToTable(db,"SELECT * FROM MEMBERS WHERE Member_num = " .. Member_num)

if tblResult ~= nil then
for Row=1, tblResult.Rows do
     Dialog.Message("Row"..nRow, tblResult.Data[n]["Member_num"] .. "     " .. tblResult.Data[n]["first_name"] .. " " .. tblResult.Data[n]["last_name"])
end
end

[dulux1309]

Great - thanks for the effort. I'm nearly there!

I have used your code, but am getting an error stating that I am trying to concatenate a null value in 'Row' - is this because there is nothing in the database?

I have included the code again if it makes things easier. I really appreciate the help.

--GET USER INPUTS
member_num = Input.GetText ("Input1");
first_name = Input.GetText ("Input2");
last_name = Input.GetText ("Input3");

--OPEN HANDLE TO DATABASE
db = SQLite.Open("retain\\databases\\retain.db");
--syntax from Worm -write from inputted fields to database..
SQLite.Query(db,"insert into Members values('".. member_num .."','".. first_name .."','".. last_name .."')");

--CREATE QUERY IDENTIFIERS
querynum = "select * from MEMBERS WHERE member_num = "..member_num;
querytxt = "select * from MEMBERS WHERE last_name = '"..last_name.."'";
queryall = "select * from MEMBERS";

--READ QUERY RESULTS INTO TABLE
tblResult = SQLite.QueryToTable(db,queryall);
if tblResult ~= nil then
for Row=1, tblResult.Rows do
Dialog.Message("Row"..nRow, tblResult.Data[n]["member_num"].." "..tblResult.Data[n]["first_name"].." "..tblResult.Data[n]["last_name"]);
end
end
-------------------------------------------------------

I am also intending as the next step to read the returned data back into the input boxes (calling up the record). Can this be done?

Thanks again, you guys are sound...
SQLite.Close(db);

[Worm]

Sheesh, can you tell I'm not testing this code

--GET USER INPUTS 
member_num = Input.GetText ("Input1"); 
first_name = Input.GetText ("Input2"); 
last_name = Input.GetText ("Input3"); 

--OPEN HANDLE TO DATABASE 
db = SQLite.Open("retain\\databases\\retain.db"); 
--syntax from Worm -write from inputted fields to database.. 
SQLite.Query(db,"insert into Members values('".. member_num .."','".. first_name .."','".. last_name .."')"); 

--CREATE QUERY IDENTIFIERS 
querynum = "select * from MEMBERS WHERE member_num = "..member_num; 
querytxt = "select * from MEMBERS WHERE last_name = '"..last_name.."'"; 
queryall = "select * from MEMBERS"; 

--READ QUERY RESULTS INTO TABLE 
tblResult = SQLite.QueryToTable(db,queryall); 
if tblResult ~= nil then 
     for n=1, tblResult.Rows do 
          Dialog.Message("Row"..n, tblResult.Data[n]["member_num"].." "..tblResult.Data[n]["first_name"].." "..tblResult.Data[n]["last_name"]); 
     end 
end

[Worm]

To set the Query results back into the Input objects.

--This sets the input boxes to the values in the
--first record of the query results
Input.SetText("Input1", tblReturn.Data[1]["member_num"]);
Input.SetText("Input2", tblReturn.Data[1]["first_name"]);
Input.SetText("Input3", tblReturn.Data[1]["last_name"]);

--This sets the input boxes to the values in the
--second record of the query results
Input.SetText("Input1", tblReturn.Data[2]["member_num"]);
Input.SetText("Input2", tblReturn.Data[2]["first_name"]);
Input.SetText("Input3", tblReturn.Data[2]["last_name"]);

[dulux1309]

You're gonna have to send me your bank details...

I'll play with this - once again you're saving my bacon. Do you know any good resourses for sqlite? I know most of it is just sql, which I am learning at the moment, but I understand there are some differences...

Thanks

[dulux1309]

I'm now having a problem with the line below -

Input.SetText("Input4", tblResult.Data[1]["..first_name.."]);

It produces an error regarding argument 2 not being a string.

When I try the line below instead, I get the result, but I also get the tblResult.Data[1] before it.

Input.SetText("Input4", "tblResult.Data[1]["..first_name.."]");

Any ideas?

[Worm]

Don't enclose tblResult in quotes.

Input.SetText("Input4", tblResult.Data[1]["first_name"]);

[dulux1309]

That doesn't work. I get the '2nd argument not a string' message.

If I try and move the quote marks around, I get numerous results, but never just the actual returned data...

[Worm]

You must have something wrong then.

I threw this together real quickly, I'm better with samples

[dulux1309]

Thanks, I'll pick it over. This has me baffled for the moment!

[Worm]

On parts of this I'm kind of stabbing in the dark because I don't know the structure of your table. If you posted the structure of the table (it's field names and types) I could offer code that was geared toward your table. As it is now, I'm guessing that the structure is like this,

    Field Name             Type
Member_num             integer primary key
first_name                  text 
last_name                  text

[dulux1309]

That's it in a nutshell - same fields and types. I understand that by assigning an Integer as a primary key it becomes an autonumber in Sqlite. You have the table dead right, though.

[dulux1309]

I have cut and pasted the code into my project and am getting the same error message about the 2nd argument not being a string on the InputSetText - should I start a ticket about this?

[Worm]

Well that's not my call...

My guess is that the record is nil or the field is nil or something along that line. Without having the actual Database, and code, I can't tell.

Make sure you do something like this before setting the text
[code]
if tblResult ~= nil then
Input.SetText("Input1", tblReturn.Data[1]["member_num"]);
Input.SetText("Input2", tblReturn.Data[1]["first_name"]);
Input.SetText("Input3", tblReturn.Data[1]["last_name"]);
else
Dialog.Message("Error", "The query did not return any results.")
end