1

There are two unique fields in my database and it is possible the user will try to add a value which already exists in one (or both) of these columns via my webpage. Quite literally, there are 2 textboxes and these will refer to the 2 columns in SQL.

I would like the error caught and show the user which word (or words) was not allowed where a duplicate would be created.

SQL prevents me entering the duplicate, but, it will error on the first attempt. Meaning, if the user tried to enter 2 words and both were duplicates, SQL would error on the first textbox, meaning the user could update textBox1 value, try and again and then be told off about the second textbox. This is not good for the user as it's slow but I don't know what the best approach is.

Dave
  • 8,163
  • 11
  • 67
  • 103
Rinshad Hameed
  • 117
  • 1
  • 6
  • 18
  • 3
    You will have to ellaborate a little bit more than that. Show some code please – Adriaan Stander Mar 27 '13 at 08:52
  • Do you mean you want to catch specific SQL exceptions only, or you don't want each exception to throw an error but to log the errors and display all errors at the end of the process? – Dave Mar 27 '13 at 08:55
  • 1
    I assume you are looking for this: http://stackoverflow.com/questions/6221951/sqlexception-catch-and-handling So use the [number](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlexception.number.aspx) property. – Tim Schmelter Mar 27 '13 at 08:55
  • @Tim Schmelter I have two unique fields. When duplicate data is entered into any of the field error gives error message specifying field name is this possible? – Rinshad Hameed Mar 27 '13 at 09:00
  • @DaveRook I want to catch the exeption occur at each unique field when try to enter duplicate data into it. – Rinshad Hameed Mar 27 '13 at 09:08
  • You can't though, the error message will occur as soon as the first duplicate is attempted. Also, you shouldn't rely on the database to prevent this, your code should (the database should be your last layer of defense) – Dave Mar 27 '13 at 09:11
  • @DaveRook Is there any other solution to this? – Rinshad Hameed Mar 27 '13 at 09:48
  • @DaveRook Thanks for your concern plz voteup my question. – Rinshad Hameed Mar 27 '13 at 09:55

2 Answers2

1

In my opinion your last defence is the database! You should be preventing the query getting there!

For me, I'd query the database and retrieve the 2 values of all rows for just these 2 columns.

Then, when a user tries to submit, I'd check the list to see if it exists. This means no exception is thrown. I can then decide how to display this information to the screen.

Of course, this is great depending on how many rows you have, if you had millions maybe this isn't so good and you may want to ping the database first using a scalar variable to see if the value already exists!

Dave
  • 8,163
  • 11
  • 67
  • 103
0

SqlException are quite verbose and u will get a full stacktrace instead of a few particular fields what caused the exception.

Also sometimes SqlException wouldn't specify enough like in case of hitting a stored procedure which fails internally ..

The best idea would be to do all kinds of validations for the field values, FK constraints, not null checks, etc before hitting the DB.

Hitting the db and then letting the user know due to some exception is not at all advised as is a big performance bottleneck.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ashish Thukral
  • 1,445
  • 1
  • 16
  • 26