none
SQL Table Without Primary Key RRS feed

  • Question

  • I have a simple table in SQL 2008 having no Primary Key. The structure of table as follows:

    eName -> VarChar(20)

    eDesc -> VarChar20)

    eDept -> VarChar(20)

    I have put some values in the DataGrid View and want to save in the SQL server.

    The CommandBuilder object does not allow me to create the Update and Delete command without PK. I dont want to make PK. I just want to Update / Delte the values with the help of ADO.NET.

    Does anybody have the solution?

    Wasim Qadir

    Monday, May 28, 2012 7:13 AM

All replies

  • Hi Wasim Qadir,

    Welcome to MSDN Forum.

    We have to specify a PK to update or delete command. Because if we have no PK to the update or delete command, ado.net don't know which record you want to update or delete.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, May 29, 2012 2:16 AM
    Moderator
  • Hi Allen,

    I have found the solution for updating Table record without Primary Key using OleDbDataAdapter. It is working fine. Please see the followng link:

    http://www.vb-helper.com/howto_net_update_without_primary_key.html

    When I use the same logic using SqlDataAdapter, it is not working. I dont know how this logic is working with MS Access database? and not working with SQL server 2008.

    Can you guide me any other logic to complete this task?

    Best Regards.

    Wasim Qadir

    Tuesday, May 29, 2012 6:08 AM
  • Wasim, 

    Simply stop trying it, everything in .Net is made around the primary key.  The code you shows makes everything until the last piece by hand. TenThousand times more work than adding primary keys.

    If you do want to do that work, no problem, I've then stated this reply for those having the same problem like you.


    Success
    Cor

    Tuesday, May 29, 2012 9:23 AM
  • Wasim --- similar code should work with SQL Server, so you must have just missed something. Go back over your code carefully and perhaps you'll find what you missed. If you can't find it, post the relevant part of your database update code here and we'll try to help you.

    Cor --- we can't always dictate what the database looks like. Developers often have to work with existing databases that we have no control over. Deal with it. If Wasim's database table doesn't have any PKs, then telling him he needs to add PKs in order for anything to work doesn't help him at all! We can easily program workarounds in these cases ... it is not rocket science!!  ;0)


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Wednesday, May 30, 2012 1:41 AM
  • Bonnie,

    Did you already try Entity Framework, Linq to SQL and Strongly Typed Datasets and the commandbuilder, they all fail without the primary key in most situations. 

    Also I've had in past in big trouble with Crystal Reports, simply because of the fact there was no primary key.

    I don't dictate that he should do it, I advise. I would not be responsible for your advice not to use primary keys.

    We are not living anymore in 1985 when a database had 4 users or less and did contain 3 or 4 tables where not even relations where set, that could be done in MS Access, but somebody who uses SQL server would do it in my perception right.


    Success
    Cor

    Thursday, May 31, 2012 8:26 AM
  • Dear All,

    Technically i agree that every table must have a Primary Key, but i cannot force my clients to do so. It is right that we are not sitting in 1985 age, but the clients are running their system since the stone age.

    Why ADO.NET user cannot change / delete the records from the table having no PK? Why SQL server allow table creation with no PK?

    There must be a way to solve this problem.

    If anyone have a logic for this, please share it.

    Regards,

    Wasim Qadir

    Thursday, May 31, 2012 9:04 AM
  • Wasim --- I already told you it's possible to do so without a PK, you just can't use some of the built-in mechanisms for updating which rely on a PK. I asked you to post your code here. We can't help you if we can't see what you've tried already.

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Thursday, May 31, 2012 1:39 PM
  • While your database table does not have a Primary Key, can you not define a primary key for the DataTable object in code? (the PrimaryKey property of a DataTable object).  The PrimaryKey property is typed as an array of DataColumn, so you can pass all three of your columns in as the PrimaryKey, then .Net should play nice.

    Brent Spaulding | Access MVP

    Thursday, June 7, 2012 2:26 AM