Validating for unique ID

Question

Hello everyone. I use c# with WPF and am wondering what is the best way to handle and validate for unique id errors. I have a primary key field in the database that has to be manually inputted with a 6 number code. This field is unique. The problem is that I don't know how to validate and handle that.

Right now I have a view with all of my text boxes and in the backing code I create a validation error event. Then I add the validation rule and bind it to a class which I create for validating. Right now I am validating for whether it is a number and whether it is empty. Here is the code

namespace Inventar.Presenters
{
   public class Validators:ValidationRule
    {
       OleDbConnection oleCon;
        OleDbTransaction transaction = null;
        OleDbCommand oleComd;

      public override ValidationResult Validate(object value,
            System.Globalization.CultureInfo cultureInfo)
        {
            int IntValue=0;

            try
            {
               IntValue=Convert.ToInt16(value);
            }
            catch (Exception)
            {
                return new ValidationResult(false, "Molimo upišite broj");
            }

            if (IntValue < 0) return new ValidationResult(false, "Molimo upišite broj veći od 0");

          
            
            return new ValidationResult(true, null);

      }

Now below I have tried to create a method that might work for what I need it to do which is check if the id already exists in the database table.

private void Id_Error(object sender, ValidationErrorEventArgs e)
        {
                string connectionString = "provider=ORAOLEDB.ORACLE; data source=ORCL; password=fin; user id=fin;";
            oleCon = new OleDbConnection(connectionString);
            List<Dobavljac> _dobavljacStore = new List<Dobavljac>();

    OleDbCommand cmd = new OleDbCommand("select DOB_ID from Dobavljaci where dob_id = :id", oleCon);
    OleDbParameter param = new OleDbParameter();
    param.ParameterName = ":id";
    param.Value = id.Text;
    cmd.Parameters.Add(param);
                OleDbDataReader reader = cmd.ExecuteReader();
               if (reader.HasRows)
    {
        MessageBox.Show("User Id already exists");
    }


            }

Right now the problem is that I don't see the view from this class so I can't access the property id.Text (id being the name of my textbox for the id input, original I know). But also I don't want the error to appear as a message box but would love to do this like the above examples using try and catch.

If you guys think this is the wrong way to do it please tell me what is the right way cos I'm a beginner, and frankly don't even know if the above code would do what it is supposed to as I can't test it due to the errors.

Please Help.

Answer 1

Set this Unique in your database, by doing this in the client side you never will succeed because you never know when a client updates. 

If he reads today and updates tomorrow because he let is desktop on during nighttime the chance is high the user has already done that. I'm not aware how that is done and what is the general conversation language used for this for Oracle.

https://forums.oracle.com/forums/main.jspa;jsessionid=8d92106930d70234885319e842b48caae3da7b9b9e78.e38La3uKbh4Oci0Ma3uKa30NbNuQe0?categoryID=84

By the way, better is to use a logical and a technical ID where in Microsoft development the GUID is in my perception the best kind of columntype to use.

---

Success
Cor

Answer 2

I'm sorry Cor I am having trouble understanding what you are telling me. It is a primary key in the database so I do believe that the unique constraint fires. The problem is in the handling of this error.

Here is my code for the insert into a database

public void Save(Dobavljac dobavljac)
        {



            //if (!_adressStore.Contains(adress))

            //    _adressStore.Add(adress);
            {

                try
                {
                    oleCon.Open();
                    transaction = oleCon.BeginTransaction();
                    oleComd = new OleDbCommand();
                    oleComd.Connection = oleCon;
                    oleComd.Transaction = transaction;
                    oleComd.CommandText = "Insert into DOBAVLJACI" +
                    "(DOB_ID, NAZIV, RACUN, TELEFON, EMAIL)" + "Values(:dob_id, :naziv, :racun, :telefon, :email)";
                    oleComd.Parameters.AddWithValue(":dob_id", dobavljac.Dob_Id);
                    oleComd.Parameters.AddWithValue(":naziv", dobavljac.Naziv);
                    oleComd.Parameters.AddWithValue(":racun", dobavljac.Racun);
                    oleComd.Parameters.AddWithValue(":telefon", dobavljac.Telefon);
                    oleComd.Parameters.AddWithValue(":email", dobavljac.Email);
         

                    //oleComd.Parameters.AddWithValue(":id", contact.Id);
                    oleComd.ExecuteNonQuery();
                    transaction.Commit();
                    //if (!_contactStore.Contains(contact))
                    //    _contactStore.Add(contact);
                    oleCon.Close();
                }
                catch (Exception ex)
                {
                    throw new Exception("Failed to fill adapter. SQL is: '" + oleComd.CommandText + "' and error is: " + ex.Message); ;

                    try
                    {
                        // Attempt to roll back the transaction.
                        transaction.Rollback();
                    }

                    catch
                    {
                    }
                }

            }

Right now when I try to insert an already existing ID what happens is that when I click save the application will transfer me into visual studios into the above line of code and give me the error

Failed to fill adapter. SQL is: 'Insert into DOBAVLJACI(DOB_ID, NAZIV, RACUN, TELEFON, EMAIL)Values(:dob_id, :naziv, :racun, :telefon, :email)' and error is: ORA-00001: unique constraint (FIN.DOBAVLJACI_PK) violated.

Obviously this is not going to work for a user. So I would like to somehow at least make the application not crash, ideally to show the error as he is typing the ID so he knows he has made a mistake before he clicks save and all hell breaks loose.

Also not sure about the GUID, I thought that is the ID that is set automatically, incrementing for every row which doesn't work for me.

Thank you for your reply.

Answer 3

Identity would be better solution than GUID to create incrementing primary key. GUID might be good if you would need globally unique identifier, that usually is not the case in simple databases.

You could try to check database values everytime user types something, but that would make a lot database queries. Other option might be check the value when focus is removed from textbox where data is entered. Then if user entered something, non-empty value, check from database whether or not that values exists and if so show message box. If value does not exists, make the insert but be aware to handle exception of the insert that value already exists and show message if exception is catched. That would handle the case Cor mentioned where time between first check and actual saving might be so long that someone already inserted data with same key (usually these are rare cases).

Answer 4

Try always to rollback as soon as possible also seperate the catch of a connection (the open) from the processing. Something like this, not tested of course.

            try
            {
                oleCon.Open();
                try
                {
                    transaction = oleCon.BeginTransaction();
                    oleComd = new OleDbCommand();
                    oleComd.Connection = oleCon;
                    oleComd.Transaction = transaction;
                    oleComd.CommandText = "Insert into DOBAVLJACI" +
                    "(DOB_ID, NAZIV, RACUN, TELEFON, EMAIL)" + "Values(:dob_id, :naziv, :racun, :telefon, :email)";
                    oleComd.Parameters.AddWithValue(":dob_id", dobavljac.Dob_Id);
                    oleComd.Parameters.AddWithValue(":naziv", dobavljac.Naziv);
                    oleComd.Parameters.AddWithValue(":racun", dobavljac.Racun);
                    oleComd.Parameters.AddWithValue(":telefon", dobavljac.Telefon);
                    oleComd.Parameters.AddWithValue(":email", dobavljac.Email);
                    oleComd.Parameters.AddWithValue(":id", contact.Id);
                    oleComd.ExecuteNonQuery();
                    transaction.Commit();
                    oleCon.Close();
                }
                catch (Exception ex)
                {
                    //Always roll direct back to avoid that the Server hangs
                    transaction.Rollback();
                    throw new Exception("Failed to fill adapter. SQL is: '" + oleComd.CommandText + "' and error is: " + ex.Message);
                }
            }

            catch
            {
                throw new Exception("Failed to open;");
            }

---

Success
Cor

Answer 5

Ok so now it's giving me the Failed to open exception when I try to insert the same ID but it doesn't really solve my issue. It still crashes the app every time a user types in an already existing ID. Is there no way for me to tell him hey, that ID already exists, choose a different one, before he clicks the button?

Or if not that, at least that on the click of the button the app doesn't crash but it's just a messagebox with an error but the app keeps working?

Answer 6

One way would be before inserting the record, just run a Select statement to see if the ID already exist in the database.  Then you should be able to handle a duplicate properly.
 "dino2dy" wrote in message news:[e-mail]...

Ok so now it's giving me the Failed to open exception when I try to insert the same ID but it doesn't really solve my issue. It still crashes the app every time a user types in an already existing ID. Is there no way for me to tell him hey, that ID already exists, choose a different one, before he clicks the button?

Or if not that, at least that on the click of the button the app doesn't crash but it's just a messagebox with an error but the app keeps working?

Answer 7

Dino,

Did it solve the problem stated in your question or does it not. 

There can be endless more errors in your program but which can be everthing, this is just code in a method from which we have absolute no idea what you are doing. It throws and exception, which should be catched in the parth where the method is called.

But that is not the purpose of the forums, if a question answers on the problem stated in the question header, than mark it as answer and create a new question for your follow up questions. 

You know the main purpose of this forums is to be a knowledge base for those searching for a question.

---

Success
Cor

Answer 8

I'm sorry Cor maybe I didn't state my problem clearly enough but in my opinion your code didn't solve it so I will try to state it again to be clearer.

I am looking for some way to validate and handle for unique, so that the application doesn't crash when the user enters an ID that already exists.

Your code has me avoiding the primary key error for the failed to open error but the application is still throwing me out when that error hits.

If I am misunderstanding you, I have no problem putting your answer as correct and opening a new thread but I really feel like that was my question.

Answer 9

Here is what I am trying now.

 catch (Exception ex)
                    {
                        transaction.Rollback();
                        MessageBox.Show("Doslo je do greske " + ex.Message);
                        //throw new Exception("Failed to fill adapter. SQL is: '" + oleComd.CommandText + "' and error is: " + ex.Message); ;

                    }
                }

                catch
                {
                    throw new Exception("Failed to open;");
                }
            }
        }

With the message box it doesn't crash the program just gives an error.

The problem now is though that it will add the row to the observable collection and it will show up in my grid.

I can only assume that this is where that trouble is

 public void SaveDobavljac(Dobavljac dobavljac)
        {
            if (dobavljac.IsNew== true)
            {
            if (!CurrentDobavljac.Contains(dobavljac))
                CurrentDobavljac.Add(dobavljac);

            _dobavljacRepository.Save(dobavljac);

            StatusText = string.Format("Dobavljac '{0}' je spašen.", dobavljac.LookupDobavljac);
            
        }
            else
            {
                _dobavljacRepository.Update(dobavljac);
            }
        }

Answer 10

Hi dino2dy,

How’s it going now? I think you can consider create a new thread for the new issue and mark the helpful replies in this thread.

---

Bob Shen [MSFT]
MSDN Community Support | Feedback to us

Answer 11

Unfortunately it isn't going. I still am getting the unwanted behavior of it appearing in my data. I have however created a new thread and here is the link so I guess I will close this one.

New Thread

Thanks to everyone who helped me with this.