Help with SQLite function

[Jonas DK]

Hi

Can anyone see what I'm missing here.
I'm stock on this one...
If any one could't help it would be much apriciaded..

I Have 2 functions function one calls function 2 in the end, the problem as I see it is on function 2

This is the first function:

function BetalEnhed(strEnhed, strDato)
strListPay = "Nej";
local tbCPR ={};
local tbMedlem = SQLite.QueryToTable(db, "SELECT * FROM tMedlem where Enhed = "..Enclose(Encrypt(strEnhed)));
	if tbMedlem and tbMedlem.Rows > 0 then
		for i,v in tbMedlem.Data do
			--Add each item returned from the above query to the table
			Table.Insert(tbCPR, i, Decrypt(tbMedlem.Data[i]["CPR"]));
		end
	else Dialog.Message("ERROR", "Der opstod en fejl i betaling", MB_OK, MB_ICONINFORMATION, MB_DEFBUTTON1);
	end
	if tbCPR and Table.Count(tbCPR) > 0 then
	v = Table.Count(tbCPR);
		for i,v in tbCPR do
			SQLite.Query(db, "Insert into tBetaling(CPR, Betaling, Paied) values(".. Enclose(Encrypt(tbCPR[i]))..", "..Enclose(Encrypt(strDato))..", "..Enclose(Encrypt(strListPay)).." )", nil);
			--ListBox.AddItem("ListBox1", MyDecrypt(tbMedlem.Data[i]["Fornavn"]).." "..MyDecrypt(tbMedlem.Data[i]["Efternavn"]), MyDecrypt(tbMedlem.Data[i]["CPR"]));
		end
	else Dialog.Message("ERROR", "Der opstod en fejl i betaling", MB_OK, MB_ICONINFORMATION, MB_DEFBUTTON1);
	end
	MissingPaymentToLB(strListPay);
end

This one seams to work as I don't get any errors

Function 2:

function MissingPaymentToLB(strListPay)
local tbPayment = SQLite.QueryToTable(db, "SELECT * FROM tBetaling WHERE Paied = "..Enclose(Encrypt(strListPay)));
ListBox.DeleteItem("ListBox1", -1);
ListBox.SetUpdate("ListBox1", false);
	if tbPayment and tbPayment.Rows > 0 then
	local tbCPR ={};
		for i,v in tbPayment.Data do
			Table.Insert(tbCPR, i, Decrypt(tbPayment.Data[i]["CPR"]));
		end
		if Table.Count(tbCPR) > 0 then
			for i,v in tbCPR do
			local tbCPRList	SQLite.QueryToTable(db, "SELECT * FROM tMedlem Where CPR = "..Enclose(Encrypt(tbCPR[i])));
			ListBox.AddItem("ListBox1", Decrypt(tbCPRList.Data[i]["Fornavn"]).." "..Decrypt(tbCPRList.Data[i]["Efternavn"]), Decrypt(tbCPRList.Data[i]["CPR"])) 
			end
		end
	end
	ListBox.SetUpdate("ListBox1", true);
end

On this one I get an error saying "attempt to index local 'tbCPRList' (a nil value)

I've been going over this some times now and I think I've gone blind to the problem... maybe some fresh eyes can see where I'm going wrong.

Cheers,
Jonas

[TJ_Tigger]

Is the query returning anything at all or is there an error with the query? This is the query I am referring to in your second function.

local tbCPRList	SQLite.QueryToTable(db, "SELECT * FROM tMedlem Where CPR = "..Enclose(Encrypt(tbCPR[i])));

If this is nill then it seems that your query might be malformed or missing a parameter. I would suggest enabling debugging from the Edit Preferences menu and watching the information as it scrolls by or adding some error checking code.

Here are some suggestions

To make sure that the information is actually available you can do this.

local tbCPRList	SQLite.QueryToTable(db, "SELECT * FROM tMedlem Where CPR = "..Enclose(Encrypt(tbCPR[i])));
if tbCPRList and tbCPRList.Rows > 0 then
     --the table exists and has rows
     ListBox.AddItem("ListBox1", Decrypt(tbCPRList.Data[i]["Fornavn"]).." "..Decrypt(tbCPRList.Data[i]["Efternavn"]), Decrypt(tbCPRList.Data[i]["CPR"])) 
else
     --either the table did not exist or returned 0 rows

end

or you can try this

-- Test for error
error = Application.GetLastError();
if (error ~= 0) then
	Dialog.Message("Error", _tblErrorMessages[error], MB_OK, MB_ICONEXCLAMATION);
end

The second suggestion should help in providing you with information as to why your query fails while the first checks to make sure information is available before trying to process said data.

HTH
Tigg

[Jonas DK]

Thanks Tigg

I actually put it aside a watched a cartoon (actually The Tigger Movie)

And I now looked at it again in conjunchen to what you just wrote and
I found the problem.

This is the original postet script:

local tbCPRList	 SQLite.QueryToTable(db, "SELECT * FROM tMedlem Where CPR = "..Enclose(Encrypt(tbCPR[i])));
			ListBox.AddItem("ListBox1", Decrypt(tbCPRList.Data[i]["Fornavn"]).." "..Decrypt(tbCPRList.Data[i]["Efternavn"]), Decrypt(tbCPRList.Data[i]["CPR"])) 
			end

And this is the changed and working one:

local tbCPRList	= SQLite.QueryToTable(db, "SELECT * FROM tMedlem Where CPR = "..Enclose(Encrypt(tbCPR[i])));
			ListBox.AddItem("ListBox1", Decrypt(tbCPRList.Data[1]["Fornavn"]).." "..Decrypt(tbCPRList.Data[1]["Efternavn"]), Decrypt(tbCPRList.Data[1]["CPR"])) 
			end

Notis the difference?

In the first bit

local tbCPRList	 SQLite.QueryToTable(db, "SELECT * FROM tMedlem Where CPR = "..Enclose(Encrypt(tbCPR[i])));

I added an = between the tbCPRList and the SQLite.QueryToTable
This fixed the error in my first post but created a new one in the secound bit

ListBox.AddItem("ListBox1", Decrypt(tbCPRList.Data[1]["Fornavn"]).." "..Decrypt(tbCPRList.Data[1]["Efternavn"]), Decrypt(tbCPRList.Data[1]["CPR"])) 
			end

Where I then changed the i the the table index with 1
Because the table it reads from contains multible indexes and the table returned from the query will always only contain 1 index number since the table is created over and over agin in the loop and it can only find the CPR field once in the SQLite table since it is unique.

So thanks for the help tigg not only with this but if it wassent for your TiggTv I would never have gotten this far using SQLite.

This is my first SQLite project, and quite a handfull using multible tables to store data in...

I've gone from knowing nothing about SQLite or SQL to now being able to make an app like this i just a week. Fantastic

Looking forward to more TiggTV.

Cheers,
Jonas

[TJ_Tigger]

How easy it is to overlook a little thing like that.

Looking forward to more TiggTV.

Me too. I have about 4 shows with code ready to be recorded. I just need to find the time to do it. More on SQL. I also have several show ideas in the bag ready to go. I hope to get those out in the new year.

Tigg