locked
Updating records with primary keys that can change. RRS feed

  • Question

  • I have an interesting scenario that probably isn't very rare but, probably worth a quick question on here to get some feedback.

    I have a table with a Product ID varchar(30) primary key and an product cost (Decimal 12,2)

    There is a VB.NET form where users can maintain this table via a grid.  The select statement for the table has some joins to the product table so I am unable to use the SQL command builder to define the INSERT, UPDATE and DELETE statement for this form.

    Users are able to select the product numbers from a dropdown box and then assign a cost to the item.  If it's a new record, the records are inserted just fine however, if the user needs to change the product ID for a row, then the update statement fails because the Product ID in the grid row no longer matches the Product ID of the row in the underlying SQL table.

    I know I can get around this by selecting the Product ID twice in my select query and using 1 of the columns as an original value however, I just wanted to ask if there's a better way to do this?

    In the olden Access and VB6 days ADO would keep a connection open to the database and allow for instant updates as records were edited in the form however, I don't think that's possible with ADO.NET.

    I know I could also create a numeric Key and use it as the primary key however, a table like this really doesn't need a numeric key and I should be able to use the Product ID as the Primary Key.

    Any ideas? 

    Thursday, October 24, 2019 3:01 PM

All replies

  • Hi Phill D,

    >> unable to use the SQL command builder

    I don't quite understand why "Select" causes you to be unable to use INSERT, UPDATE and DELETE statements.

    To handle this issue, any demo code will be better.

    If you want to update the database to the Grid in real time, you can try "SqlDependency".

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 25, 2019 5:39 AM
  • It's because I'm using a JOIN in my SELECT statement to pull in some lookup values such as product description and product category for display purposes.  When you pull data from more than 1 table, Command builder doesn't know which tables or columns to update so the query is considered 'too complex'.

     
    Friday, October 25, 2019 1:48 PM
  • Hi Phill D,

    >> doesn't know which tables or columns

    Have you tried specifying a column by table name?

    SELECT table1.column, table2.column
    FROM table1, table2
    WHERE table1.column1 = table2.column2;

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 28, 2019 6:47 AM