Hi,
I did go back and change the setup of the database so that description is now the primary key and descriptionid is a unique index which is the foreign key in the transactions table.
Erm.. You did this in the database? Purely for the sake of providing a quick lookup based on a description, you made a descriptive field the primary key? I really wouldnt have done that, but hey.. it's your app..
.Find and .Select are dataset methods that work without ever accessing the database. If youre prepared to go to the lengths of editing the database, then you should consider what is to do the work and how you can realise a multi tier security model out of this..
Just think about the logic of this for a moment; if you have a descriptive column that you assert will be unique and hence sufficient to identify exactly one row, then why do you have another column that is unique, and is called somethingID indicating it is an ID field.. So given that it is unique, it can be used to ID one row, so whats the point of the other column? Why do you have two independently unique columns on a single table? It could indicate a normalisation error or flaw in the database design.
There's a can of worms here, and I dont like it..
But now you have made me think?
That's actually quite a compliment
I usually code a so in this scenario if a user tries to enter a duplicate entry I catch it in an if staement and provide a friendly message telling them their mistake.
Erm. I usually configure the back end so a duplicate entry is impossible to achieve, and I dont run a check for everything before I try to achieve it because I would spend my entire coding life writing check routines.
But are you saying I should let the try/catch block trap it instead?
Yes! In most things in life, we try a dangerous thing with knowledge of how to handle things that go wrong. It is rare that things go wrong, and as a result we achieve better performance.
Suppose we are coding a routine that inputs a value into a table
A PK exists ensuring no duplicate rows
We can do (pseudocode):
SELECT COUNT(*) FROM table WHERE pk = whatever
If count > 0 then
MessageBox.Show("That record already exists")
Else
INSERT INTO table VALUES(1,2,3)
or we can just try it:
Try
INSERT INTO table VALUES(1,2,3)
Catch
MessageBox.Show("That record already exists")
In the first case, the db scans the table and retrieves the count. lets say this takes 1 second
Then your app decides it is safe to insert
The insert command scans the table to make sure it is safe to do the insert
When it is safe, the insert is done
2 table scans are needed for this. OK, they are quick because PK accesses are quick, but youre senselessly doubling up what the database does!
In the second, we jsut insert. The db scans and checks and then does the insert. SOME of the time an error occurs, not all. By assuming that we will mostly be successful we dont waste time doing scans for safety that arent required
In doing so does this not just display a message that most users would not understand?
Um, Try/Catch blocks themselves NEVER show a message by default. You and only you, the programmer, shows info to the user. If your complaint is of the complexity of the message you show, then show something simpler.
How the hell did I ever get into the awful way of coding it all manually?
Tradition?
Interestingly, it is still all coded manually.. Its just that the manual code is generated automatically.. Kinda like having your passenger change gear for you in the car doesnt mean you have an automatic transmission, but it does mean you dont have the bother of doing it
