Duplicate Entry Error

[idpintl]

I am trying to synchronize a PDA database table to the SQL server through the PDA's cradle. When both the SQL server table and PDA table are completely empty, inserting the first record, saving and then synchronizing works. However, when I try to update that field, I get the following error:

[MySQL][ODBC 3.51 Driver][mysqld-4.0.18-nt] Duplicate entry ” for key 1

No matter what I try after the fact (update / change data, turn off / turn on constraints, etc), the duplicate entry error continues to appear.

Thanks – JW

ps- I have included a screen shot of the SQLyog screen display

[CalEvans]

No anwer to your problem but I do have a question.

1: What platform is your PDA?

This looks like a cool project. Did you write the conduit yourself or is it commercial?

=C=

[idpintl]

The PDA is an iPAQ PocketPC with Windows CE. The conduit is commercial, which is a software called Visual CE from Syware. This project is a non-profit humanitarian endeavour for the purpose of managing Tuberculosis in third-world settings. Our health workers in Uganda utilize the PDA to capture and record data on patient compliance to medication, patient disposition, etc., and the data is synchronized through the Internet (via mobile phone connected to the PDA) to the primary TB database.

[Ritesh]

I am still not clear about how are you “synchronizing” data…

Are you using the “Database Sync Tool” of SQLyog to sync data?

[Shadow]

And what do you mean by 'updating data' ? Do you mean that after the initial syncronization each attempt fails if you updated any record in DB located on the PDA?

[idpintl]

I am synchronizing data through an add-on to the Syware Visual CE package called MEnable – this allows us to synchronize the PDA table's to the SQL tables. And by “updating data” I mean adding new information to a patients' on-going record. The underlying problem is this: when I add new patient's to the SQL database, this information easily sync's to the PDA (and these records can be updated with additional data from the PDA); however, the sync only fails when a new record is added to the PDA and the initial sync to the SQL DB is performed. So long as the record is originally added in SQL I can update any record in the DB and PDA without fail.

I have been troubleshooting this with a tech from Syware, and he's convinced this is a SQL problem. His reply this morning was:

SQLSetPos(SQL_ADD) appears to have a bug in it. We pass the value 2 as the second parameter to the call. It appears that it is interpreting it as 1.

Any thoughts?

[CalEvans]

I concur with the tech form Syware. It would help if you could post the SQL statement he was looking at when he said that.

From the looks of it you are not giving MySQL a unique value for your PK. Have you tried making NTBN auto_increment?

If I understand the problem correctly, when new data is added tot eh PDA and then synced, the Add fails. The add is failing because the conduit is using an insert that is trying to give the PK a value that already exists. The first thing I would try is removing NTBN from the insert statement and making that field auto-increment. This may solve your immediate problem but there may be other ramifications to doing this that I am not aware of. (orphaned records, etc.)

HTH. Cool project. When you get this solved, I'd like to ask more questions.

=C=

[idpintl]

I have posted the logs containing the SQL statement at the following locations:

Note: I posted on our website because the pages of these logs are too long to post here.

odbc_trace.txt was run today because there have been some configuration changes to the SQL DB. There were two sync's performed during this trace (the first one was updating fields: successful; the second was inserting a new record into the PDA: failed)

sql.txt was run last Thursday, and is the log that the Syware tech looked at and commented on.

http://www.idpintl.org/temp/odbc_trace.txt

http://www.idpintl.org/temp/sql.txt

CalEvans: Yes, I have made NTBN an auto_increment number. And I wholly agree with you, the PK is being given a value that already exists. However, I do not understand the reason for removing the NTBN number from the insert statement (I am very new to this by the way).

Thanks for your time – JW

[Shadow]

If you define a column as autoincrement, then you don't need to assign a value to this field in INSERT statements or just assign NULL. If you explicitly set an autoincrement field to a value other than NULL (or 0) then the field will be set to that value and the whole point of autoincrement columns is lost.

[idpintl]

Thanks for the clarification Shadow.

[Author]

Posts