ComboBox SQLite Question

[almulder]

I have a database that a user enters in info, then they have the option to retrieve it and edit it. But I have several ComboBox's that I use for faster entry that the user selects from.

Problem is I can't figure out how to get it to populate the data once it is retrieved and have it appear as the selected item from the drop down.

Note: I am storing the text of the Combobox in the sqlite db

Is there an easy way or will I have to figure out some sort of fancy if then statement (Help neede if that is the case)

Additionally, I have it so they can enter in their own data into the ComboBox, is there a way to make it so the data is then save as part of the combobox for future use? (So it then become one of the choices)

Here is my code

--sListBox is the list of all the Games listed they can select from to edit
--I-Genre1 is the ComboBox that is in question
--genrel is the column in the sqlite db table
--tbreturn is the row being selected for the sqlite db table

function PopFields(sListBox)
	--Initialize local variables;
	local tbSel = ListBox.GetSelected(sListBox);
	local nRID = 0;
	--Check to see that an item was selected
	if tbSel then
		--Retrieve the data portion from the list box, this contains 
		--the Record ID from the database
		sID = ListBox.GetItemData(sListBox, tbSel[1]);
		--Ensure that the variable is a number by using String.ToNumber
		nID = String.ToNumber(sID);
		--Now query the database using that Record ID
		local tbReturn = SQLite.QueryToTable(db, "SELECT * FROM games where ID = " .. nID);
		--If data is returned set populate the fields with the 
		--appropriate information from the database
		
		if tbReturn and tbReturn.Rows > 0 then
				Input.SetText("I-Name", tbReturn.Data[1]["name"]);
-------
		ComboBox.SetSelected("I-Genre1", tbReturn.Data[1], genre1)
-------
		Input.SetText("I-Size", tbReturn.Data[1]["size"]);
		Paragraph.SetText("I-Requi", tbReturn.Data[1]["requi"]);
		Paragraph.SetText("I-Notes", tbReturn.Data[1]["notes"]);
		Input.SetText("I-Loc", tbReturn.Data[1]["loc"]);
			--Set the nRID variable to the Record ID of the selected entry
			nRID = nID;
		else
			--There was an error in retrieving the record from the database
			Dialog.Message("Error", "There was an error retrieving your information");
		end
	else
		--No items were selected in the listbox.
		Dialog.Message("Error", "No item was selected.");
	end
	--return the blnEdit variable.
	return nRID;
end

[almulder]

Ok so I figured the best way to try and accomplish what I want is with a if then statement, but I need some help with the proper code.

adding this into above code to replace "ComboBox.SetSelected("I-Genre1", tbReturn.Data[1], genre1)"


The code below i know is not correct, but I am hoping someone can help. (it was just an idea of how it could run with correct code)

genrelist = ComboBox("genre1")  -- add all combo box items into genrelist var

for i , e in pairs(genrelist) do
    if genrelist = tbReturn.data(1), genre1 then
         ComboBox.SetSelected("I-Genre1"), i)
    end
end

[almulder]

Here is the code I have so far. but i have 1 issue I believe (hopefuly)

Below is the full function I have setup.

function PopFields(sListBox)
	--Initialize local variables;
	local tbSel = ListBox.GetSelected(sListBox);
	local nRID = 0;
	--Check to see that an item was selected
	if tbSel then
		--Retrieve the data portion from the list box, this contains 
		--the Record ID from the database
		sID = ListBox.GetItemData(sListBox, tbSel[1]);
		--Ensure that the variable is a number by using String.ToNumber
		nID = String.ToNumber(sID);
		--Now query the database using that Record ID
		local tbReturn = SQLite.QueryToTable(db, "SELECT * FROM gamestbl where ID = " .. nID);
		--If data is returned set populate the fields with the 
		--appropriate information from the database
		
		if tbReturn and tbReturn.Rows > 0 then
		Input.SetText("I-Name", tbReturn.Data[1]["name"]);

----------------------------------------------------------------------
		ComboBox.SetSelected("I-Genre1", -1)
		genrelist = ComboBox.GetItemData("genre1", selected)
			-- Step through each item in the table
		for nIndex, tItemInfo in genrelist do
			if 	genrelist[nIndex].Data == tbReturn.Data[1]["genre1"] then  --<<<----- I believe this is the part that is the issue  "tbReturn.Data[1]["genre1"]"
			ComboBox.SetSelected("I-Genre1", nIndex);
			end
		end
----------------------------------------------------------------------

		if tbReturn.Data[1].single == 1 then
				CheckBox.SetChecked("I-Single", true)
			else
				CheckBox.SetChecked("I-Single", false)
		end
		if multi == 1 then
				CheckBox.SetChecked("I-Multi", true)
			else
				CheckBox.SetChecked("I-Multi", false)
		end
			
		Input.SetText("I-Size", tbReturn.Data[1]["size"]);
		Input.SetText("I-Requi", tbReturn.Data[1]["requi"]);
		Input.SetText("I-Notes", tbReturn.Data[1]["notes"]);
		Input.SetText("I-Loc", tbReturn.Data[1]["loc"]);
			--Set the nRID variable to the Record ID of the selected entry
			nRID = nID;
		else
			--There was an error in retrieving the record from the database
			Dialog.Message("Error", "There was an error retrieving your information");
		end
	else
		--No items were selected in the listbox.
		Dialog.Message("Error", "No item was selected.");
	end
	--return the blnEdit variable.
	return nRID;
end

[almulder]

Solved!

Wow. I finally figured it out after a lot of trial and error. For reference see code below.

		ComboBox.SetSelected("I-Genre1", -1)
		--genrelist = ComboBox.GetItemData("I-Genre1", selected)
		 genrelist = ComboBox.GetCount("I-Genre1")
			-- Step through each item in the table
			matchfound = 0
		for nIndex = 0, genrelist, 1 do
			Label.SetText("DebugLabel",tbReturn.Data[1]["genre1"])
			if 	ComboBox.GetItemText("I-Genre1",nIndex) == tbReturn.Data[1]["genre1"] then
				ComboBox.SetSelected("I-Genre1", nIndex);
				matchfound = 1
			end
		end
		if matchfound == 0 then
-- This commented out section below is for when there is no matching result, but you want it added into the list temporarily.  I changed my mind and went with a alert instead. Left this here for reference.
--			genrelist = genrelist + 1
--			ComboBox.AddItem("I-Genre1",  tbReturn.Data[1]["genre1"],"")
--			ComboBox.SetSelected("I-Genre1", genrelist);
			Dialog.Message("Alert!", "There is an issue retriving Genre's, \n Please update Genre information",  MB_OK,MB_ICONEXCLAMATION);
		
		end