SQLite 3: how to prevent a double entry to be added to the database?

[gvanassche]

Dear all,

I'm creating a tool that collects URLs. But I don't want to store them twice in the db. To prevent this from happening, this is what I made:

for row in db:nrows("SELECT url FROM URLTABLE") do
	if url ~= newurl then
		num=num+1
	else
		Dialog.Message("Sorry", "you already submitted this URL before.", MB_OK, MB_ICONEXCLAMATION);
		preventadding=1
	end	
end

I think that I use the completly wrong way to retrieve the URLs that are stored in the db, but I have no clue.

I tried this as well:

local url = SQLite.QueryToTable(db, "SELECT url FROM ML")
if url and url.Rows then
	for i,v in url.Rows do
		Dialog.TimedMessage("url", tblRS, 100, MB_ICONNONE);
	end
end

... but the SQLite.QueryToTable does not seem to work.

I'm really not good with this....
Can anyone give me a hint or correct my code?

thanks

gert

[Sakuya]

SELECT `url` FROM `URLTABLE` WHERE `url` = 'example';

Check if that returns a row.

[Dermot]

It really helps to take a look at the documentation and the sample.

for row in db:nrows("SELECT url FROM URLTABLE") do
	if row.url ~= newurl then
		num=num+1
	else
		Dialog.Message("Sorry", "you already submitted this URL before.", MB_OK, MB_ICONEXCLAMATION);
		preventadding=1
	end	
end

[Dermot]

SELECT `url` FROM `URLTABLE` WHERE `url` = 'example';

Check if that returns a row.

Yes that is a better solution rather than looping through all records, although it should be.

SELECT url FROM URLTABLE WHERE url = 'example';

[gvanassche]

Thanks, guys!
Really appreciated!

[RizlaUK]

create the table as unique and if sqlite throws a error (30019) while adding the row then the the item already exists

local sName = Input.GetText("Input_Name");
local sEmail = Input.GetText("Input_Email");
local sNotes = Input.GetText("Input_Notes");
local bIsActive = CheckBox.GetChecked("CheckBox_IsActive");



if sName == "" then
	Dialog.Message("Notice", "You must enter a user name.");
else
	if sEmail == "" then
		Dialog.Message("Notice", "You must enter a valid email.");
	else
		
		if gAddUserFlag == "ADD" then
		
			SQLite.Query(db, "INSERT INTO Users VALUES (NULL, '"..sName.."','"..sEmail.."','"..sNotes.."','"..tostring(bIsActive).."')", nil);
		
		elseif gAddUserFlag == "EDIT" then
		
			SQLite.Query(db, "INSERT OR REPLACE INTO Users VALUES ("..gEditUserID..", '"..sName.."','"..sEmail.."','"..sNotes.."','"..tostring(bIsActive).."')", nil);
			
		end
		err = Application.GetLastError();	
		if err == SQLite.OK then
		
			gAddUserFlag = "ADD"
			gEditUserID=0
			DialogEx.Close(1);
		else
			if err == 30019 then
				Dialog.Message("Error: ("..err..")","User already exists", MB_OK, MB_ICONEXCLAMATION);
			else
				Dialog.Message("Error: ("..err..")",SQLite.GetErrorString(err), MB_OK, MB_ICONEXCLAMATION);
			end
		end
		
	end
end