How to check for duplicate usernames?

[Bob Sneidar]

Ick! Do stuff that causes SQL to throw errors. I suppose that is a way to do it, but a simple select will do it cleanly. Keeps the SQL error logs clean too. If multiuser, lock the SQL database first, then check, insert if query returns 0 records, unlock the SQL database. 

A method I use that works generically for ALL my columns is when I first open a table for query, I use SHOW CREATE TABLE. The result can then be searched with lineOffset and wholeMatches to find the column I am verifying to see if it has the word UNIQUE in it. (You can also check for default values to pre-populate, etc). If lineoffset is not 0, I call a validation routine which requeries the table for a duplicate before inserting/updating. 

If you want to get really fancy, you can have 3 functions: preval, rtval (real time for validation upon closefield) and postval. Preval can be called prior to opening the form for creating new records, so you can populate with default values, rtval can be called upon closeField for things like uppercase, titlecase, formatPhone, etc, and postVal prior to saving the data back to the database for things like not empty (NOT NULL), lookups like zip validation, etc. 

These validations are not difficult to write, and implement, make your database app much more polished. I have the validations in the stack script or a library stack so I can call them from anywhere. I have a behavior script for all my form cards so I only have to edit once for all my forms. (THANK YOU LIVECODE!) 

Bob


On Aug 4, 2011, at 8:53 AM, Jeff Massung wrote:

> Instead of checking for a username, mark the UserName field in the table as
> being UNIQUE. This will cause the DB to error out of a transaction that
> attempts to insert the same UserName into the table multiple times. Then
> catch that.
> 
> Jeff M.
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode