Catch primary key violation exception in Visual Basic 2003

  • Thread starter Thread starter Hetal
  • Start date Start date
H

Hetal

I searched online and went through the forums as well, but i could not
find a way to capture the database primary key violation exception.
Any help will be much appreciated.

Thanks,
Hetal
 
Hetal said:
I searched online and went through the forums as well, but i could
not find a way to capture the database primary key violation
exception. Any help will be much appreciated.
Click to expand...

Why can't it be caught? Try-Catch? When does it occur? Please describe the
circumstances.


Armin
 
Hi Armin,

Thank you for your response.

I do have a try catch block in place and the exception occurs when my
insert statement violates a priimary key. This is how the catch block
looks like.

===========
Catch ex As Exception
If ex.InnerException Is Nothing Then
Log("*** Schedule.CreateShifts() Error occured. - " &
ex.Message)
Else
Log("*** Schedule.CreateShifts() Error occured. - " &
ex.InnerException.Message)
End If
==========

and the error message i am receiving is:

"ERROR [23000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.21-community-
nt]Duplicate entry '2007-09-17 00:00:00-9-1' for key 1"

My question is, how can i single out this particular exception in my
catch block? As i will be performing a different set of activity for
primary key violations and for any other exceptions, i will just be
logging them in a log file.

Thanks,
Hetal.
 
Hetal said:
Hi Armin,

Thank you for your response.

I do have a try catch block in place and the exception occurs when
my insert statement violates a priimary key. This is how the catch
block looks like.

===========
Catch ex As Exception
If ex.InnerException Is Nothing Then
Log("*** Schedule.CreateShifts() Error occured. - " &
ex.Message)
Else
Log("*** Schedule.CreateShifts() Error occured. - " &
ex.InnerException.Message)
End If
==========

and the error message i am receiving is:

"ERROR [23000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.21-community-
nt]Duplicate entry '2007-09-17 00:00:00-9-1' for key 1"

My question is, how can i single out this particular exception in my
catch block? As i will be performing a different set of activity for
primary key violations and for any other exceptions, i will just be
logging them in a log file.
Click to expand...

I don't have MySQL here so I can't test it. I'd investigate the exception
object when the error occurs to find out whether there is any reliable
information to recognize it as a primary key violation. The error message
shows you are using an OdbcConnetion, so maybe the exception is an
OdbcException carrying detailled information in it's Errors property. Each
item (OdbcError) has a NativeError property. Compare it to the error number
from the MySQL documentation. Just a guess.

.....look for "dupliate entry" here:
http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html

.....seems to be number 1062.

HTH

Armin
 
Hetal,

The answer is simple, don't use primary keys for outside database use. There
are always two identifiers, one for the humans and one for the database. In
that case you cannot have any conflict. Identifiers types can be: automatic
idents which are integers or Guids, the later is prefered, because those you
can manage yourself.

If you want to use the identifier outside the database as well, then first
let the database create that one (not a Guid that is for humans unusable)
and show that to the user. I find that a very bad solution because you are
by instance dependend on what exist and what is deleted.

Cor
 
Back
Top