primary keys

• • Reply

  19 years ago

  by UltimateWilliam

  UltimateWilliam

  , United States

  Joined 19 years ago

  
  a user enters a value for the field SSNUMBER and the database found out that the number exists and an error will occur because this is a primary key.
  

  
  how do we prevent this error especially if the record is not in the result set or recordset but exists in the table? do i have to search first for the possible occurence of the SSNumber in the table before accepting the data and will prevent the error generated by the database. . .?
  

  
  should i use adCmdTable to use index? this may be slow if the table is big and this must happen just to add a single record.
  

  
  thanks.

  Report abuse
• • Reply

  19 years ago

  by tlosekoot

  Thamar

  Poland, Poland

  Joined 19 years ago

  As far as I tried, it is impossible to catch this error. This because the database already reports the error before the AfterInsert of the form is initialized.
  

  
  I guess you'll indeed have to check each record using the AfterUpdate event of your control.
  

  
  I don't say it isn't possible at all, so you can try something anyway. Please post any results here, I'd like to know how you worked it out.
  

  
  Good luck!
  

  
  Regards,
  

  
  Thamar

  Report abuse
• • Reply

  19 years ago

  by hobbes68

  hobbes68

  Milan, United States

  Joined 19 years ago

  Well, you could:
  1. When you leave the textbox where the user has inserted the SSN number perform a check on the existence of the SSN in your table (if it is a primary key, it is indexed and the search will not take long) and depending on the answer (OK, KO) allow or prevent the user to add the new record.
  

  
  2. depending on the database you are using, trap the error -
     Access has a specific error code for PRIMARYKEY replication and so does SQLServer and show the user a "nice" message. (I can help with the error codes, I used them in a couple of solutions. In SQLServer, use a stored proc )
  

  
  The second solution is better if the duplication seldom occurs, so not to spend too much time in controlling the existence of the SSN number.
  

  
  hob

  Report abuse