SQLite - Checking for a duplicate entry before adding it

[buchacho]

I have a function that will add a new entry to a table in my SQLite database. I would like to add to the function some kind of SQL query that I use to check to see if I am adding a duplicate entry before doing so. Is there a query I can perform to search the table based on a few columns to return the number of matches?

[bangkok]

Are you saying that you are able to do an INSERT in SQL... but you don't know how to do a... SELECT ? !


Anyway. Make a simple SELECT on your column, before to do the INSERT, to check.

Code: Select all

put "SELECT * from mytable where name='"&newEntry&"'"

Some people would advise you to put your insert function in a TRY CATCH structure.

http://forums.runrev.com/phpBB2/viewtop ... f=7&t=9418

You can even add some "fuzyness" (because a "duplicate" for human, is not a always a "duplicate" for computer... spelling mistakes, typos etc.) with "LIKE" (and wildcard %), in order to make a warning for the user before the insert.

Code: Select all

put "SELECT * from mytable where name like '%"&newentry&"%'" into tSQL

[buchacho]

Are you saying that you are able to do an INSERT in SQL... but you don't know how to do a... SELECT ? !

Not exactly I like to ask here since I am not too familiar with SQL and am looking for efficient ways to input user data. It is very time consuming to design the user input aspect with LiveCode to ensure input meets strict rules for multiple fields and drop-down menus...

I found this function to be useful, where tSQL is the SELECT argument:

Code: Select all

put revQueryDatabase(tDatabaseID, tSQL) into theCursor
put revNumberOfRecords(theCursor) into numRecords