sqlite help needed

[RizlaUK]

ok, iv broken out the sqlite plugin after watching tigg's video tuts and already iv hit a road block

i want to only add a item to the database if it dosent already exist, so like no duplicate entrys can be made based on a listbox items text,

so how would i compair a string of text to either a database column or the listbox name (as thay will both contain the same data) and only add the string to the database if the same string isent already present


this is what iv come up with, and guess what.....it dosent work, lol
isCB = Clipboard.IsTextAvailable();
if isCB then
cbText = Clipboard.GetText();
if cbText ~= tbReturn.Data["data"] then
AddtoDB()
end
end

am i even half on the right track, do i need a loop, if so, how do i format it

thanks in advance for any help

[TJ_Tigger]

Where do you not want to have the duplicate? Is this in the SQLite database or in the listbox? If it is the listbox you can loop through the listbox and compare the text.

strcompare = "texttocheck";
bnew = true;
for x = 1, ListBox.GetCount do
--select item one
--get the text to compare
--compare it to strcompare
if it matches then bnew = false end
end

or something like that anyway.

For SQLite on the version that IR uses you cannot define a column as being unique so you what you can do is do a query for the text you have and if nothing is returned then you can add the file. Depending on your structure you can also perform an INSERT or REPLACE on the data and it willl either insert a new record or replace the one that is there.

HTH
Tigg

[Dermot]

Actually you can make a column unique in the version that AMS uses.

SQLite.Query(db, "CREATE TABLE Notes(NoteID integer primary key, Note text UNIQUE)")

If you try saving a duplicate it will return error number 30019.

[RizlaUK]

Hi Tigg, well it dosent realy matter what i query with, the database or the listbox because thay both cotain the same data,

maybe i should explain a little bet better, i want to get text from the clipboard and enter it in to the database and the listbox, but i want to fire this in a page timer, so i want to set the condition that if the text is already in the listbox then dont add it to the database

iv got all my other functions working ok and i have a button that adds the data to both the DB and the LB, but as i say i want it to fire in page timer so searching the listbox is the way to go i think or it just keeps adding the same text over and over again

i'll have a crack at it with the code you provided and see if i can make a working function

[RizlaUK]

ah, dermot, we both posted at the same time, ill try both methoods and see what i get

[RizlaUK]

well stone the crows, that was easy it works like a dream now, thanks Dermot

i now copy any text and stright away its added to the DB and LB only once, great job, this little app is nearly finished already

between Tigg and Dermot iv learned so much about the sqlite plugin today, i wonder where users like myself would end up without such a great support team

To all the guys that make this forum such a great support site, THANK YOU (you know who you are ;) )

[TJ_Tigger]

Actually you can make a column unique in the version that AMS uses.

SQLite.Query(db, "CREATE TABLE Notes(NoteID integer primary key, Note text UNIQUE)")

If you try saving a duplicate it will return error number 30019.

Good to know. I had not played much with UNIQUE on the SQLite so I didn't know how well it worked with the table being stored as ascii. I know that the definations for the columns doesn't do much other than provide a guideline for columns with the one exception of integer primary key.