PK violation

[Greg25]

I am not a programmer !!!!

Using SQL Server 7
App: Virtual Ticket/Job Manager & Access 2001
Tables: job_workflow, Job_charges

I am in the process of impamenting a barcode system into the job tracking system that we already have in tack.

I have been running some tests on a sample DB and have been encountering some problems with a PK violation

On the table job_workflow, or Job_charges has a unique PK so I assigned a number that has not been used, I can now place this info into that tables no problem

The problem is when I go back to the Virtual Ticket/Job Manager to manually enter a record the way it was designed to be entered it tells me the violation of the PK cannot insert duplicate key could their be a way around this

Virtual Ticket/Job Manager 3.0 software can’t look up the info from a different table or another DB.

Any suggestions welcomed

Greg

 

[foxdev]

manually enter a record the way it was designed to be entered

Since we don't know what your application is trying to do, its difficult to know for sure. Is the PK for this table an "Identity" column? Does your application try to explicitly insert a value into this column? Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com

 

[Greg25]

I believe its just an identity Number, and the record that was scanned in was ok its just when I got to insert a new record form the app could their may be a trigger to fire on insert to assign a new number instead of the number that job Manager assigns to that column

I hope I am helping a little its hard and even harder when you just don’t know

Thanks Greg

 

[foxdev]

Sorry, I should have been a little clearer.

An "identity" column in SQL Server is one that is automatically assigned a number by the system, but only if one is not specifically specified by the Insert statement (and if the "allow identity inserts" is turned on).

If a number is not being automatically assigned, then assumedly your program is trying to do that, and it is trying to use a number (or value - I'm guessing that its numeric) that has already been used.

I don't think a trigger will work (Terry?) because PK violations are checked before any triggers fire. Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com

 

[redlam]

In this situation, the only way to get a trigger to fire is to use an 'INSTEAD OF' trigger in SQL2000. All triggers prior to SQL2000 fire only after constraint checks have been verified and transactions have been committed. I can provide more info on this if you need it...

 

[Greg25]

I believe the program is automatically assigning the number and yes it is a numeric value

This is not some thing I need over night I am just testing some stuff out to se if I could get the info from the bar code reader to place the info on a certain table
it’s not that easy but I do have time

Thanks for your help Greg