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