Newbie Questions

Re: Checking input against DB for duplicates

Patrick Halstead — Thu, 30 Nov 2006 00:57:46 GMT

You're going to have to write some code that queries the DB and checks for the value. We are about to release our new Database Accelerator Suite (Basic version is FREE) which you could use to do this *WITHOUT HAVING TO WRITE CODE*. The suite will go live in 3-4 weeks. In the meantime, please send me e-mail if you'd like to install a beta version.

If you would rather not install our cool new Database Accelerator Suite, you will have to write some custom code to query SQL for the value. Around this code, you'll want to lock the DB. It's probably 20-30 lines of code, but more significantly, it will require full trust and hardcoding of the connection string in the solution. I recommend against doing those things because they make the form a) harder to deploy and b) more costly to maintain. Nevertheless, we have done it before and you *can* do it. If you need a code snippet, we'd be happy to spend a couple hours helping you write it. You can purchase a block of time in the store.

Cheers!

Re: Checking input against DB for duplicates

mcalicea — Wed, 29 Nov 2006 13:03:43 GMT

Thanks for the idea, but in my case, the number is generated prior to being input into the form. That is why I want to have it checked against the database, in case someone "fat-fingers" the entry and accidentally enters a duplicate number in. Right now, SQL kills it after the submit button is pressed, but I would like to have the error found and notify the user before it gets to that point. I have little to no experience programming with JScript or VBScript at this time. Its on my list of things to do.

Re: Checking input against DB for duplicates

Patrick Halstead — Wed, 29 Nov 2006 08:18:26 GMT

Hi Mcalicea and welcome to our forum!
Most of the time, people ask how they can set it up to have the DB automatically create the ID when an entry is added. It's probably better to have the DB do this especially if you have multiple writers since there will be some lag between submit and validation. You could have a situation where just after the check succeeded someone else added a row with the same ID. At any rate, best practice is to have the number generated server-side, since that's where the locking happens. Please search for "database row auto ID" or something like that and let me know if you don't find something.

Checking input against DB for duplicates

mcalicea — Tue, 28 Nov 2006 15:36:37 GMT

I'm sure its been asked before, but I can't find it here.
What I have is a SQL 2000 DB with a single table. I have a form made for the initial entry of data into the DB. The first field on this form is also my Primary key and is unique to every row. I am using the Data validation to ensure the format of this field is correct and that part works. I need it to also check the DB to make sure there is not already an entry in the table with that unique number. I tried creating a secondary data connection and comparing the two, but it doesn't work. It never seems to find a match. I tried creating a rule but that didn't work either. Please help.