Method Community

 

Getting Violation of UNIQUE KEY constraint error.

Last post 07-08-2009 8:01 AM by Anonymous. 4 replies.
Page 1 of 1 (5 items)
Sort Posts: Previous Next
  • 06-30-2009 10:56 AM

    • 1strate
    • Top 200 Contributor
    • Joined on 06-12-2009
    • Texas
    • Posts 24

    Getting Violation of UNIQUE KEY constraint error.

    Since this weekend's update, I've been sporadically getting a the following error when updating a service log I created.  The screen worked great for a few weeks prior to the this weekend's push.  I made some minor changes but they shouldn't have caused this error and when tested...they worked fine. I also tried a couple of different screen versions but I still get the error.  It is also sporadic and hard to reliably duplicate. I don't want to undo hours of work only to determine that it might be something related to the weekends update.  The error is:

     "Viloation of UNIQUE KEY constraint 'PoolServiceLog_ActivityNoRef_unique'. Cannot insert duplicate key in object 'dbo.PoolServiceLog'. The statement has been terminated.

    Additional Information

    Action Sequence Order:53"

     

    I'm currently using the screen FieldService_AddEditWorkOrder_Live_Backup062609_live and getting the error.  I also get the error with the older versions.  I assume that the error is telling me that I can't create a new record because it thinks one already exists; however, in reality...it doesn't exist.

    Background:  I added a section on this screen that takes user input and creates a Pool Service Log entry (when one doesn't exists) or updates the log entry (if one does exists). 

    Any guidance would be greatly appreciated.

    James Stapleton

    Update:  I will be adding this entry into the QXpress Online forum.

     

     

     

     

    Regards,
    James Stapleton
    1st Rate Pool Service, Inc.
  • 07-03-2009 8:16 AM In reply to

    Re: Getting Violation of UNIQUE KEY constraint error.

     James,

    It appears you've set the ActivityNo to only accept unique values in the PoolServiceLog. All you have to do is make the ActivityNo field in the PoolServiceLog table to not unique.

    Perform the following steps.

    1. In Method Go to Customize > Table and Fields

    2. Select the PoolServiceLog table.

    3. Find the ActivityNo field.

    4. Uncheck the Unique? checkox.

    5. Click Save changes.

    6. Click Finished Editing Fields.

    Now you should be able to save the changes.

  • 07-07-2009 11:42 AM In reply to

    • 1strate
    • Top 200 Contributor
    • Joined on 06-12-2009
    • Texas
    • Posts 24

    Re: Getting Violation of UNIQUE KEY constraint error.

    Peter,

    That has always been set that way and the screen worked great for several weeks. Not only that, when I refresh my browser, I don't get the error.  I would really like to work with support to resolve this issue...even if it turns out that it is a result of my own modifications to the screen--I'm willing to pay.

    Regards,
    James Stapleton
    1st Rate Pool Service, Inc.
  • 07-07-2009 4:57 PM In reply to

    Re: Getting Violation of UNIQUE KEY constraint error.

     James,

    If the field is set to allow for non-unique values, then you shouldn't receive this error message anymore, no matter if you refresh your browser or not. If you'd like I can log into your account and do some quick testing.

    Otherwise you can set up a training session with your account representative and go over the issue with him along with any other questions you may have. If it does turn out to be an issue on our end you will be re-imburse for the training session, if not we'll at least set up the page to work properly for you.

    Let us know how you would like to proceed.

  • 07-08-2009 8:01 AM In reply to

    Re: Getting Violation of UNIQUE KEY constraint error.

    James emailed back the following response:

    I did make the change you suggested and the error did go away; however, it then caused a new log to be created every time the work order was opened...even if a log had already been created for that work order in a previos session. Even though a pool service log exists...the screen behaves as though it doesn't and creates a new log.  By removing the unique requirement, multiple logs are created for a single work order.

    I guess i can just combine all the fields i have in my pool log table and add them directly to the activity table. That would solve the issue but it still doesnt answer the question as to what broke my screen in the first place. I hate the thought of all the work I put in that has to be redone. I also suspect that the method upgrade is to blame but I have try to    spend my time to fix it.  On the other hand, I understand that your company cant be held accountable for poorly designed screens created by your clients.

    For now, I will probably just add my pool log fields to the activity table. If I change my mind, I will call my account representative.

    Thanks for getting bck to me.

Page 1 of 1 (5 items)