I set a UNIQUE constraint to the Employee_ID Column (for testing) and I get:
The relevant code is:
Without the constraint, I was able to add as many times the same record as I can (!). That is why I added the constraint 
Ideas ?
Xojo 2021r2.1
Monterey 12.6
Looks like youāre attempting to use the same Employee_ID value for each record, and the database is refusing it.
Does row_DBR specify a unique value for Employee_ID? If not, the Employee_ID column in the database needs to be set as auto-increment.
Or maybe you want the constraint on the employee name column? But this will cause other problems for you if you have more than one employee with the same name.
I set a default Record, then I click in Write Record.
Day ellapsed; then I had the idea to click ma,y times in Write Record and noticed that I am able to store many Records with the same information.
So, I declared Employee_ID as UNIQUE and purposely clicked two times in Write (a new SQLiteDB was created). At the second write, SQLite complains (and I expected that).
I do not set UNIQUE on Family Name nor First Name for obvious reasonsā¦
Employee_ID is the only Column I think I can set to UNIQUEā¦ (beside PK, of course).
And here I amā¦
You canāt INSERT employee 109 twice, you can UPDATE it many times after the INSERT. And even updating it, you canāt update such ID to an already existing one.
So the unique constraint is working correctly? Giving you an error if you try to add another record with an Employee ID that is the same as another Employee ID already there.
Or are you having a different problem?
Well then you have two choices:
Ensure that you never insert a record with the same id as one in an existing record.
When you get an error, check if the errorNumber is 19, and if so, output a more appropriate error message and give up.
OK: I rephrase my question:
how do I trap that error ? The error appeared in the db.AddRow lineā¦ who is located after Try and before Catch errorā¦
(please read the screen shot of the debugger far above before answering)
Youāve already trapped it.
If you are debugging and you are using ābreak on exceptionsā the debugger will stop on that line, but if you continue/resume debugging you will see that your code will move to the catch section.
Ah, OK. I stopped on the error lineā¦
So, my fault.
Thanks Alberto.
for your own error handling if you not want the ide stop everywhere use in the method
#Pragma BreakOnExceptions false
All answers allows me to change my Try code as:
Try
App.db.AddRow("Employee", row_DBR)
Catch error As DatabaseException
If error.ErrorNumber = 19 Then
MessageBox("A Record with this Employee ID already exists in the Data Base.")
Else
MessageBox("(AddRow) DB Error: " + error.Message)
End If
End Try```
and this works fineAny time I see error checking code like this I think. Why are you letting the user control the Employee ID in the first place. Sure your code needs to use the value, but why would the user have to choose a value.
If a new employee starts I presume they get the next available number. When you open an existing employee their number is displayed and presumably fixed for life.
Can you not make EmployeeID a read only field. Then when you create a new Employee pre-fill the value, or even hide it and inform he user once it is chosen. In other words donāt let them make the mistake in the first place. Let the computer deal with the computer parts and the users deal with the data side.
EmployeeID should be the autoincrement primary key. The database is much better at choosing the next number than you are.
Wrong. This Employee ID is set by the company. At my local McDonald, the numbers are re-used as soon as the max value is reached (probably a 3 digits number).
An Employee Data Base is set by the HR department, and usually, these people knows what they do.
Employee_ID have nothing related (as any of the other Columns excepted the PK) to Data Base inner working.
But, this is my vision of things. Yours may be different.
Case of a Part Number ID: in that case, the value can increase to ā¦ I recall some spare parts ID larger than 3,7XX,XXX (at Volvo / Volvo Penta). Auto or ship motors spare parts have to be unique and not re-used since the motor can live forā¦ decades !
Thank you for sharing your ideas.
PPS: None of the other Columns can have a UNIQUE constaint (all of the other fields can be duplicates), only the Employee_ID HAVE to be UNIQUE.
Fair enough. What if admin issues the same number twice? You wonāt be able to store it with that constraint in place.
usually, these people knows what they do.
You could have to deal with the unusual.
Dictatorshipā¦
What if admin issues the same number twice?
It is an error, they will correct it and write the new Record.
I may eventually (if someone request it) propose the number of Rows + 1 as the Employee_ID default value, but I will do not talk about it first.
And if this case happens, where to set the highest Employee_ID (for large corporations).
Never give bad ideas to a customerā¦
Allowing them to choose is a bad idea. 
I have a lot of experience of this.
Just because the company says āEmiles employee ID is 1234ā
does NOT mean you need to make that the uniqueID in YOUR database.
And indeed you should not.
The INTERNAL personID should be unique, and it should autogenerate.
Then, if you are asked to create a new person, they can be called Emile Schwarz, even if there is another Emile Schwarz in the database already.
The new employee 1234 can be 1234 even if there is another with the same numberā¦ this could be re-use of a company EmployeeID (Very common in police forces), and it may be a new period of employment for a previous employee
So the Company EmployeeID is an attribute of the person, just the same as surname and forename.
It should not be the same as the internal PERSON_ID, which must be unique, and which the user will never see.
Whether or not there can be 2 people with the same Company EmployeeID is business logic, not Database restriction.
If someone tries to create a person with EmployeeID, you should check to see if there is already a record with that reference in the database, with something like
Select count(1) from MYTABLE where COMPANYEMPLOYEEID = ā1234ā
You might have an attribute like āLeaving Dateā which you can checkā¦
Select count(1) from MYTABLE where COMPANYEMPLOYEEID = ā1234ā and LEAVINGDATE is NULL
Never give bad ideas to a customerā¦
never let a customer dictate the database schema.
My assumption is: the employee number is unique and fixed by the HR.
If a police badge number is used several times, how do you find out the name of the person using it? You have to ask for his first and last name.
In the case of a unique number: no question: we know who we are talking about. The salary increase will go well to the person concerned, no risk of error. ( )
This may be stipulated in the description of the software.