Primary key violation

  • Shamsudheen,

    Thank you so much for your prompt reply, I have to fix this issue urgently. Here is my stored proc

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE Procedure [dbo].[DS_GetProductsByFilter]

    @FieldName nVarChar(20),

    @FieldValue nVarChar(50)

    AS

    SET NOCOUNT ON

    DECLARE @Where VARCHAR(200)

    DECLARE @sqlcmd VARCHAR(3000)

    IF @FieldName = 'Show All'

    SET @Where = ''

    ELSE

    IF @FieldName = 'Title'

    SET @Where = ' WHERE ' + @FieldName +

    ' LIKE ' + CHAR(39) + '%' + @FieldValue + '%' +

    CHAR(39)

    ELSE

    SET @Where = ' WHERE ' + @FieldName +

    ' = ' + CHAR(39) + @FieldValue + CHAR(39)

    SET @sqlcmd = 'Select PM.itemcode, isbn,title,cost,

    price,mfgsuggestedprice,altprice, PD.Discount,validcost,

    PM.VendorID, ProductType,PD.SchemeID,PM.description,

    sectioncode,distributioncategory,itemalias,lastpurchaseorder,

    Keywords,lastvouchernumber,allowregisterpricing,

    allowregistertitle,reorderable,lastinvoiceno,PriceMethod,

    CostBasis,PurchaseFromVendorID,reclassfromitemcode,

    Internetitem,internetminqty,internetmaxqty,UpdateQOH,

    PD.VendorItemNo,PD.ASIN , Note

    FROM ProductMaster PM

    LEFT OUTER JOIN ProductMasterDist PD ON PM.ItemCode = PD.ItemCode '

    + @Where + ' ORDER BY PM.itemcode'

    EXEC (@sqlcmd)

    GO

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    GRANT EXECUTE ON DS_GetProductsByFilter

    Thanks,

    Ram.

  • This proc almost certainly isn't the problem. Your problem is with the insert/update.

    Your PK violation isn't coming from a select statement (unless it's something tangential, like you aren't selecting the PK column(s) so you don't have them when you try to update).

  • Keith,

    Like I wrote earlier, if I fill the primary key column in the grid and then click on row above of below the current row then it gives me "row cannot be located for updating". If I don't fill the primary key and leave it null, then it gives me "Violation of primary key constraint".

    Thanks,

    Ram.

  • Yes, I read what you wrote earlier. I'm just saying I don't believe it's how you populated your grid that's the problem, it's the insert/update. I don't think it's doing what you expect it to do. Did you try disabling the PK to see what gets inserted?

  • There is no way I can disable the PK, the DBA will kill me.

  • ramadesai108 (4/3/2008)


    Keith,

    Like I wrote earlier, if I fill the primary key column in the grid and then click on row above of below the current row then it gives me "row cannot be located for updating". If I don't fill the primary key and leave it null, then it gives me "Violation of primary key constraint".

    Thanks,

    Ram.

    Notice it said "cannot be located for updating", operative word UPDATING. It doesn't understand that you're attempting to do an insert, it's being treated as an update. That's where you need to start.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Matt,

    This is exactly what I thought, but since this is automatic, I don't know what to do. The reason for me to use ADODC is so that I can search easily and sort quickly. I have over 4 million records to display otherwise I would not use ADODC. Any suggestions are welcome from all.

    Thanks again.

    Ram.

  • ramadesai108 (4/3/2008)


    Shamsudheen,

    Thank you so much for your prompt reply, I have to fix this issue urgently. Here is my stored proc

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE Procedure [dbo].[DS_GetProductsByFilter]

    @FieldName nVarChar(20),

    @FieldValue nVarChar(50)

    AS

    SET NOCOUNT ON

    DECLARE @Where VARCHAR(200)

    DECLARE @sqlcmd VARCHAR(3000)

    IF @FieldName = 'Show All'

    SET @Where = ''

    ELSE

    IF @FieldName = 'Title'

    SET @Where = ' WHERE ' + @FieldName +

    ' LIKE ' + CHAR(39) + '%' + @FieldValue + '%' +

    CHAR(39)

    ELSE

    SET @Where = ' WHERE ' + @FieldName +

    ' = ' + CHAR(39) + @FieldValue + CHAR(39)

    SET @sqlcmd = 'Select PM.itemcode, isbn,title,cost,

    price,mfgsuggestedprice,altprice, PD.Discount,validcost,

    PM.VendorID, ProductType,PD.SchemeID,PM.description,

    sectioncode,distributioncategory,itemalias,lastpurchaseorder,

    Keywords,lastvouchernumber,allowregisterpricing,

    allowregistertitle,reorderable,lastinvoiceno,PriceMethod,

    CostBasis,PurchaseFromVendorID,reclassfromitemcode,

    Internetitem,internetminqty,internetmaxqty,UpdateQOH,

    PD.VendorItemNo,PD.ASIN , Note

    FROM ProductMaster PM

    LEFT OUTER JOIN ProductMasterDist PD ON PM.ItemCode = PD.ItemCode '

    + @Where + ' ORDER BY PM.itemcode'

    EXEC (@sqlcmd)

    GO

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    GRANT EXECUTE ON DS_GetProductsByFilter

    Thanks,

    Ram.

    Ram,

    in your query there is two table ProductMaster PM and ProductMasterDist PD and in which table you trying to insert or update

  • First of all you need to understand few basic stuff -

    1> Select statements don't update/insert into tables

    2> A primary key is unique and cannot have repetitive values

    3> There is a code segment with on_click where there is a function call or a procedure call which does the update/insert stuff

    4> You don't have any problem with your select procedure

    5> If you change the values in an existing record, you trigger an update

    6> If you click on a new cell, a new record should be created with PK+1(if the PK column is identity) or else you need to specify a new value which is not present in the column already

    Your problem shows that probably your primary key column is not an identity. Hence, you need to provide a new value.

    But, to complicate things, you have a join in the select list, and it is most probable that if you cannot code the insert/update section, you cannot update/insert using a true grid, as this involves multiple insert/updates. If this enlightens you, the problem should be solved, else you should post the problem on you programming language forum. There seems to be a bit of gap in the understanding of how your true grid works.

    You can check out the following sites for further info -

    http://www.componentone.com/products.aspx?ItemType=1&TabTypeID=1&PanelIndex=16&TabMapID=83&Itemid=73&Tabid=102&SubCategoryTypeID=1

    http://www.componentone.co.uk/products.aspx?ItemType=1&TabTypeID=1&PanelIndex=16&TabMapID=83&Itemid=73&Tabid=102&SubCategoryTypeID=1

    related stuff -

    http://delphi.about.com/od/usedbvcl/l/aa032503a.htm

    http://www.mpassociates.gr/software/catalog/prog/dataccess.html

    Please make sure you go through the part -

    If your queries don’t span multiple tables (a common case in many applications), using ComponentOne Query becomes a matter of "point and click."

    and

    You can create multi-table views combining information stored in different tables with the Schema Designer.

    Note: Views cannot be updated.

    Chandrachurh Ghosh
    DBA – MS SQL Server
    Ericsson India Global Services Limited
    Quality is not an act, it is a habit.

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply