giving error message according to Sql data
-
The statement is in regard to exception handling in general, which is what the debate is about, not a specific, narrow case of one type of SqlException
I know the language. I've read a book. - _Madmatt
Ah, thanks for clearing that up. I was a touch confused on this.
I'm not a stalker, I just know things. Oh by the way, you're out of milk.
Forgive your enemies - it messes with their heads
-
Your problem is not how to deal with the error, the problem is what is the CAUSE of the error. There is no excuse for primary key violation and it is up to the developer to make sure the exception cannot arise in the normal flow of the program.
erdinc27 wrote:
if the user tries to add an existing data it gives error like that
I suspect you are inserting instead of updating a record.
Never underestimate the power of human stupidity RAH
10!
-
Well, this has certainly been an interesting debate. What nobody has seemed to point out so far is that you actually should combine the two techniques. This is for a simple reason - while you should certainly attempt to detect the unique key violation explicitly, there is no guarantee that this alone will work. The reason for this is simple, presumably your application is going to be multi-user; well, between the time you check the uniqueness and the actual insert occurs, another user could have inputted the same values. So, you have two checks in there - one to cope with the initial check, and a try/catch to cope with the database race condition. Simple. Job done.:thumbsup:
I have CDO, it's OCD with the letters in the right order; just as they ruddy well should be
Forgive your enemies - it messes with their heads
Pete O'Hanlon wrote:
there is no guarantee that this alone will work
10!
-
T M Gray wrote:
Your statement is a matter of philosophy or preference.
No, established best practices, architecture guidance and experience. http://msdn.microsoft.com/en-us/library/seyhszts.aspx[^] "Know when to set up a try/catch block. For example, you can programmatically check for a condition that is likely to occur without using exception handling. In other situations, using exception handling to catch an error condition is appropriate." In this case the unique key violation is a known condition that may occur and can be tested for.
I know the language. I've read a book. - _Madmatt
Mark Nischalke wrote:
In this case the unique key violation is a known condition
Which the database will check anyway; so why check it twice or more? You're just slowing things down needlessly. Especially considering that the internal check by the database is likely to be the quickest.
-
T M Gray wrote:
You could catch that particular exception.
Exceptions are for unexpected events not for normal processing.
I know the language. I've read a book. - _Madmatt
Exactly, and your point is... ?
-
hey guys..i added a Unique constraint to a column in my sql table and if the user tries to add an existing data it gives error like that "Violation of UNIQUE KEY constraint 'ukc_cekilis_no'. Cannot insert duplicate key in object 'dbo.NumaraBilgileri'." it is ok..but i want to show an error message like that in my program..how i can check if the data already exist
Catch it and interpret it -- which database engine?
-
Mark Nischalke wrote:
In this case the unique key violation is a known condition
Which the database will check anyway; so why check it twice or more? You're just slowing things down needlessly. Especially considering that the internal check by the database is likely to be the quickest.
Not slowing it down. If you can't account for it otherwise, such as not applying an unique index on a field that may not be unique, then certainly the processing can, and should, be done at the database. If the key already exists return something like false not just let the exception be thrown.
I know the language. I've read a book. - _Madmatt
-
Your problem is not how to deal with the error, the problem is what is the CAUSE of the error. There is no excuse for primary key violation and it is up to the developer to make sure the exception cannot arise in the normal flow of the program.
erdinc27 wrote:
if the user tries to add an existing data it gives error like that
I suspect you are inserting instead of updating a record.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
There is no excuse for primary key violation
You're right in that - but the OP didn't have a primary key violation he had a unique constraint violation - which is legitimate. For example, I am adding new products each of which has a unique id (maybe a GUID or an integer) which will be the primary key. But the product also requires a human-readable Code - which is also required to be unique. At some point the user needs to type in the code for a new product - you can check the DB at this point, and tell them to go ahead. Meanwhile another user can try to use the same code, pass the 'does it exist' test, and attempt a commit. Whoever gets there first will be fine, the 2nd person's commit will fail.
___________________________________________ .\\axxx (That's an 'M')
-
Mycroft Holmes wrote:
There is no excuse for primary key violation
You're right in that - but the OP didn't have a primary key violation he had a unique constraint violation - which is legitimate. For example, I am adding new products each of which has a unique id (maybe a GUID or an integer) which will be the primary key. But the product also requires a human-readable Code - which is also required to be unique. At some point the user needs to type in the code for a new product - you can check the DB at this point, and tell them to go ahead. Meanwhile another user can try to use the same code, pass the 'does it exist' test, and attempt a commit. Whoever gets there first will be fine, the 2nd person's commit will fail.
___________________________________________ .\\axxx (That's an 'M')
_Maxxx_ wrote:
he had a unique constraint violation
Ok I missed that mainly b/c I make no distinction between the 2 constraints, if that scenario existed then I would still do a select on the code field before inserting and feed the result back to the user. In the case of your new product I would return the record/info on the product entered by the first commit. I admit there is a potential for a conflict on a really high volume system but I have yet to meet such a system.
Never underestimate the power of human stupidity RAH
-
Not slowing it down. If you can't account for it otherwise, such as not applying an unique index on a field that may not be unique, then certainly the processing can, and should, be done at the database. If the key already exists return something like false not just let the exception be thrown.
I know the language. I've read a book. - _Madmatt
Mark Nischalke wrote:
Not slowing it down.
Yes, slowing it down. <Whoops, I hit post prematurely> GUI.DoesTheKeyAlreadyExist? BL.DoesTheKeyAlreadyExist? API.DoesTheKeyAlreadyExist? DAL.DoesTheKeyAlreadyExist? SP.DoesTheKeyAlreadyExist? DB.DoesTheKeyAlreadyExist? How many times are you going to check the same darn thing when no problem exists? A duplicate key is an exceptional situation, whether you can test it or not. Consider database round-trips to be very expensive. </Whoops, I hit post prematurely>
Mark Nischalke wrote:
If the key already exists return something like false
How do I know
falsemeans duplicate rather than timeout or a referential integrity violation? -
I believe Mark's argument is that, given that the described error condition is a likely occurrence, then the OP should code to detect and handle the condition directly, rather than rely on the exception mechanism (which is expensive).
Software Zen:
delete this;Gary Wheeler wrote:
the described error condition is a likely occurrence
No, it never happens; I've certainly never seen one outside of unit testing -- of exception handling.
-
I have always been taught and follow the principle that conditions that can be tested for are not exceptions and you shouldn't use exception handling for them. Certainly there are cases you can't test for or don't expect to happen. In this particular case the unique key violation is expected and can be tested for.
I know the language. I've read a book. - _Madmatt
Mark Nischalke wrote:
conditions that can be tested for
Do you check your car's battery charge, tire pressure, tread depth, lug nut torque, etc. every time you leave the house? Do you check your fly before leaving the men's room? Do you wear a belt and suspenders? Just being able to perform a test, doesn't make it worthwhile.
-
Mark Nischalke wrote:
Not slowing it down.
Yes, slowing it down. <Whoops, I hit post prematurely> GUI.DoesTheKeyAlreadyExist? BL.DoesTheKeyAlreadyExist? API.DoesTheKeyAlreadyExist? DAL.DoesTheKeyAlreadyExist? SP.DoesTheKeyAlreadyExist? DB.DoesTheKeyAlreadyExist? How many times are you going to check the same darn thing when no problem exists? A duplicate key is an exceptional situation, whether you can test it or not. Consider database round-trips to be very expensive. </Whoops, I hit post prematurely>
Mark Nischalke wrote:
If the key already exists return something like false
How do I know
falsemeans duplicate rather than timeout or a referential integrity violation?Please, give me some credit. :rolleyes: Of course I would not architect such a contrived situation as you have outlined. Neither would I expect false to mean everything. As I, and others, have been saying here, handle the known conditions but be prepared for other cases.
I know the language. I've read a book. - _Madmatt
-
Gary Wheeler wrote:
the described error condition is a likely occurrence
No, it never happens; I've certainly never seen one outside of unit testing -- of exception handling.
Exactly the point. The application should be designed and coded so that it is an exceptional situation when it occurs. When responding to OP the first option given was to allow the exception to occur, which, IMO, is not correct, it should have been emphasized to correct the problem before it occurs or take other mitigating actions before just accepting the exception.
I know the language. I've read a book. - _Madmatt
-
Mark Nischalke wrote:
conditions that can be tested for
Do you check your car's battery charge, tire pressure, tread depth, lug nut torque, etc. every time you leave the house? Do you check your fly before leaving the men's room? Do you wear a belt and suspenders? Just being able to perform a test, doesn't make it worthwhile.
If I expected my nuts to be loose I certainly would check my fly.
I know the language. I've read a book. - _Madmatt
-
_Maxxx_ wrote:
he had a unique constraint violation
Ok I missed that mainly b/c I make no distinction between the 2 constraints, if that scenario existed then I would still do a select on the code field before inserting and feed the result back to the user. In the case of your new product I would return the record/info on the product entered by the first commit. I admit there is a potential for a conflict on a really high volume system but I have yet to meet such a system.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
I admit there is a potential for a conflict on a really high volume system but I have yet to meet such a system.
Welcome to my world! Imagine, if you will, something like a public facing web site that requires registration using a user name (rather than an email address which reduces but doesn't eliminate the problem).
___________________________________________ .\\axxx (That's an 'M')
-
Mycroft Holmes wrote:
I admit there is a potential for a conflict on a really high volume system but I have yet to meet such a system.
Welcome to my world! Imagine, if you will, something like a public facing web site that requires registration using a user name (rather than an email address which reduces but doesn't eliminate the problem).
___________________________________________ .\\axxx (That's an 'M')
I can just about see the latency issue getting into it there, I would think even that would need to be a fairly high volume system. Have you ever tracked the number of constraint violations? I think I'll stick to my corporate systems and stay hidden behind the firewall thank you!
Never underestimate the power of human stupidity RAH
-
Catch it and interpret it -- which database engine?
hey guys thanks for all your replies i use MsSql 2008 express..and i tried something like that
try
{
dr[j] = xnl.Item(i).ChildNodes[j].InnerText;
}
catch(SqlException ex)
{
Console.WriteLine("This Record is already exist");
}but still on the screen it shows the same error which is shown in Sql screen and sometimes it gives error like object reference not set to an instance of an object in that line dr[j] = xnl.Item(i).ChildNodes[j].InnerText; what is wrong here.i read a Xml file over website and put it in a datatable is it because it cannot connect that site ?
modified on Thursday, December 9, 2010 4:20 AM
-
hey guys..i added a Unique constraint to a column in my sql table and if the user tries to add an existing data it gives error like that "Violation of UNIQUE KEY constraint 'ukc_cekilis_no'. Cannot insert duplicate key in object 'dbo.NumaraBilgileri'." it is ok..but i want to show an error message like that in my program..how i can check if the data already exist
I think exception try..catch.. can only save you from this trouble. Just use
TryandCatchSqlException. And check the detail inSqlException,If it Contains word 'UNIQUE' then you'll get sure that the exception is regarding UNIQUE Key Constrain and further more you can notify to users with a formatted message. That's not a big deal.Regards, Hiren. Microsoft Dynamics CRM My Recent Article: - Way to know which control have raised PostBack[^]
-
I think exception try..catch.. can only save you from this trouble. Just use
TryandCatchSqlException. And check the detail inSqlException,If it Contains word 'UNIQUE' then you'll get sure that the exception is regarding UNIQUE Key Constrain and further more you can notify to users with a formatted message. That's not a big deal.Regards, Hiren. Microsoft Dynamics CRM My Recent Article: - Way to know which control have raised PostBack[^]
Hiren Solanki wrote:
,If it Contains word 'UNIQUE'
And what do you do when you deploy it onto a localised version of SQL Server, say the Japanese version?
I'm not a stalker, I just know things. Oh by the way, you're out of milk.
Forgive your enemies - it messes with their heads