A simple solution would be (not the best; but, one that works)... Run a ExecuteScalar and return the Count(*) for what you are about to insert. If it returns 0 then you know it doesn't exist.
User don't really like what the database spits back at them, need to dummy it down sometimes. Even though the messages says "Unique constraint failed..."
Maybe "real help" to you means "do my work for me, by giving me a solution I can paste into my code and forget about"
I think that "real help" means "teach them the proper way to do something so they dont have to ask again"
It's up to you.. I dont give answers to problems like yours, I teach you how to solve your own problems.. If you dont want to learn, and just want the answer, then I'm glad you found it elsewhere
A simple solution would be (not the best; but, one that works)... Run a ExecuteScalar and return the Count(*) for what you are about to insert. If it returns 0 then you know it doesn't exist.
User don't really like what the database spits back at them, need to dummy it down sometimes. Even though the messages says "Unique constraint failed..."
If you have good exception handling that is somewhat "user friendly" you should be ok. Or... you check for a specifiic exception and handle it cleanly.
The point was raised recently at work, about what exception handling should be done
A stored procedure was attempting an operation and a number of undesirable outcomes could arise. THese were signalled by the proc exiting normally but setting an out parameter to a numeric error code.
I pointed out that this wasnt sensible; each operation as a function should logically only have one successful outcome, with anything else being a fail. If AddTransactionToAccount failed because the account was closed, there were no cards, there wasnt enough funds etc.. then the sproc shouldnt exit normally but set an error code.. it should raise a custom exception with a relevant code and message.
The crazy thing was, the client side code would look like:
VB.NET:
Try
cmd.ExecuteNonQuery()
If outParam.Equals("000") Then return 'success
Else If outParam.Equals("001") Then NotEnoughFunds()
..
Catch OracleException
..handle errors here like database disconencted etc
If ex.Message.Contains ...
End Try
There are problems with such an approach:
You cant bubble exceptions without checking a code and throwing
It doesnt make sense to have 2 blocks of code for handling errors
It doesnt make sense to waste extra CPU cycles on always running a set of IF after every call to the sproc
The big issue was that this sproc was returning a cursor that code written by Microsoft was using to fill a datatable.. Now, because MS' code would assume the cursor was ready if the sproc completed, we were getting errors because the sproc was completing normally even though errors had occurred. I just could not get this concept through to the guys programming this:
Exceptions exist for a reason and should be thrown as far as possible. It is not down to the database to make decisions how to handle an exception and then convert it into an error code to pass back to the UI. The established, understood, assumed method of reporting exceptional circumstances is to throw an exception, NOT catch the exception before execution leaves the database, and convert it into an out param string indicating something went wrong..
rofl.. well.. I dont think I ever called you lazy, I just told you what I saw when I read your words.. Not everyone can cope with such honesty, but I tend to speak my mind in a direct way and occasionally I'm called rude.. I usually say "nahh.. I just try to be fair by hating everyone equally"
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.
