sqlLite love

[Bruce]

Working on my first sqlite database and need a little help (Thanks for the video Tigg)…

On Page preload I have:

db = SQLite.Open("AutoPlay\\Docs\\accounts.db");
SQLite.Query(db, "CREATE TABLE accounts(ID integer primary key, who text, login text, pass text, notes text)", nil);


function PopLBFromDB()
--select all entries found in the accounts table
tbReturn = SQLite.QueryToTable(db, "SELECT * FROM accounts");

--Clear all items from listbox
ListBox.DeleteItem("who_what_List", LB_ALLITEMS);

--Do the following if there are entries returned
if tbReturn and tbReturn.Rows > 0 then
for i,v in tbReturn.Data do
--Add each item returned from the above query to the listbox
ListBox.AddItem("ListBox1", tbReturn.Data[i]["name"], tbReturn.Data[i]["ID"]);
end
end
end

function Enclose (strText)
--returns the suppied string as a quoted string
return string.format("%q", strTest);
end

function AddtoDB()
local strWho = input.GetText("who_what_Input")
local strLog = input.GetText("login_Input")
local strPass = input.GetText("passsword_Input")
local strNote = input.GetText("notes_Input")

--Check to see if the above entries are valid
if strWho ~= "" and strLog ~= "" and strPass ~= "" and strNote ~= "" then
SQLite.Query(db, "Insert into accounts(name, login, password) values9".. Enclose(strWho)..","..Enclose(strLog)..","..Enclos e(strPass)..","..Enclose(strNotes)..")",nil);
--Clear the inputs
function ClearInputs()
input.SetText("who_what_Input","");
input.SetText("login_Input","");
input.SetText("passsword_Input","");
input.SetText("notes_Inpu","");
end

else
Dialog.Message("Error", "Please provide an Account name, Login and password before trying to add an entry");
end
end

On Page show I have:

PopLBFromDB();

On my “Add” Button on click…

AddtoDB();
PopLBFromDB();

I keep getting an error message when clicking the Add button:

On Click, Line 27: attempt to index global ‘input’ (a nil value)

[jfxwave]

All of your input needs to be Input (caps)
like:
local strWho = Input.GetText("who_what_Input")
local strLog = Input.GetText("login_Input")
local strPass = Input.GetText("passsword_Input")
local strNote = Input.GetText("notes_Input")

and so on

[Bruce]

That helped! Thank you. I'm now geting:

"Please provide an Accoount name, Login and password before trying to add an entry" even though I have something in every input box.

db = SQLite.Open("AutoPlay\\Docs\\accounts.db");
SQLite.Query(db, "CREATE TABLE accounts(ID integer primary key, accounts text, login text, pass text, notes text)", nil);


function PopLBFromDB()
--select all entries found in the accounts table
tbReturn = SQLite.QueryToTable(db, "SELECT * FROM accounts");

--Clear all items from listbox
ListBox.DeleteItem("who_what_List", LB_ALLITEMS);

--Do the following if there are entries returned
if tbReturn and tbReturn.Rows > 0 then
for i,v in tbReturn.Data do
--Add each item returned from the above query to the listbox
ListBox.AddItem("who_what_List", tbReturn.Data[i]["name"], tbReturn.Data[i]["ID"]);
end
end
end

function Enclose (strText)
--returns the suppied string as a quoted string
return string.format("%q", strTest);
end

function AddtoDB()
local strWho = Input.GetText("account_Input")
local strLog = Input.GetText("login_Input")
local strPass = Input.GetText("passsword_Input")
local strNote = Input.GetText("notes_Input")

--Check to see if the above entries are valid
if strWho ~= "" and strLog ~= "" and strPass ~= "" then
SQLite.Query(db, "Insert into accounts(name, login, password) values9".. Enclose(strAccounts)..","..Enclose(strLog)..","..E nclose(strPass)..","..Enclose(strNotes)..")",nil);
--Clear the inputs
function ClearInputs()
Input.SetText("account_Input","");
Input.SetText("login_Input","");
Input.SetText("passsword_Input","");
Input.SetText("notes_Inpu","");
end

else
Dialog.Message("Error", "Please provide an Accoount name, Login and password before trying to add an entry");
end
end

[Dermot]

Try making the variables global instead of local. Also there are a couple of mistakes here.

SQLite.Query(db, "Insert into accounts(name, login, password) values[COLOR="Red"]9[/COLOR]".. Enclose(strAccounts)..","..Enclose(strLog)..","..E[COLOR="Red"]_[/COLOR]nclose(strPass)..","..Enclose(strNotes)..")",nil);

[Bruce]

This works!

db = SQLite.Open("AutoPlay\\Docs\\accounts.db");
SQLite.Query(db, "CREATE TABLE accounts(ID integer primary key, accounts text, login text, pass text, notes text)", nil);


function PopLBFromDB()
--select all entries found in the accounts table
tbReturn = SQLite.QueryToTable(db, "SELECT * FROM accounts");

--Clear all items from listbox
ListBox.DeleteItem("who_what_List", LB_ALLITEMS);

--Do the following if there are entries returned
if tbReturn and tbReturn.Rows > 0 then
for i,v in tbReturn.Data do
--Add each item returned from the above query to the listbox
ListBox.AddItem("who_what_List", tbReturn.Data[i]["name"], tbReturn.Data[i]["ID"]);
end
end
end

function Enclose (strTest)
--returns the suppied string as a quoted string
return string.format("%q", strTest);
end

function AddtoDB()
local strAccount = Input.GetText("account_Input")
local strLog = Input.GetText("login_Input")
local strPass = Input.GetText("password_Input")
local strNote = Input.GetText("notes_Input")


--Dialog.Message("values", strAccounts.."\r\n"..strLog.."\r\n"..strPass);

--Check to see if the above entries are valid
if strAccount ~= "" and strLog ~= "" and strPass ~= "" then
SQLite.Query(db, "Insert into accounts(accounts, login, pass, notes) values(".. Enclose(strAccount)..","..Enclose(strLog)..","..En close(strPass)..","..Enclose(strNote)..")",nil);

--Clear the inputs
function ClearInputs()
Input.SetText("account_Input","");
Input.SetText("login_Input","");
Input.SetText("password_Input","");
Input.SetText("notes_Input","");
end

else
Dialog.Message("Error", "Please provide an Accoount name, Login and password before trying to add an entry. All must be filled in.");
end
end