none
Using DataAdapter.Update() without a primary key RRS feed

  • Question

  • I have a situation where I am pulling from a 3rd party database that has no primary key. I used a data adapter to pull the information in, but since I have no primary key, I cannot use the Update command. Is this true, or is there a work around for this?

    Monday, September 24, 2007 2:39 AM

Answers

  • It would look like (you need to replace table name and column names with the real one)

     

    INSERT INTO MyTableNameHere (Column1, Coumn2, Column3) VALUES (?, ?, ?)

     

    Now you can use this parameterized SQL statement in your command to pass three values and insert them into database. When you insert you do not need to specify WHERE clause, since it is not an update o delete that needs to locate existing row in a database

    Thursday, September 27, 2007 10:55 AM
    Moderator

All replies

  • It is true, but the issue is not related to the DataAdapter. To be able to update any record in a database, engine first need to locate each record and to do this it needs to know primary key or unique combination of fields. This is how engine could distinct records. Otherwise it has no idea which record to update. Quite possible the data you are using has this unique combination of the fields you could rely on, and you could provide this information in a WHERE clause of your UPDATE statement. If this is not the case, then it looks like pretty bad design of the data.

     

    Monday, September 24, 2007 10:46 AM
    Moderator
  • So if I know the combination of fields that make up a 'primary key' (there are 4 fields that do this), I can add that to a self made Update command?

     

    If so, this update command would need to be versatile enough to work on every row changed. Now in my situation I will only be adding, not deleting or updating, rows. And I am adding to MS Access so the SQL is a bit limited. What would an example Update SQL string look like?

    Monday, September 24, 2007 4:27 PM
  • In a case if you only need to add rows and there not to update/delete them, then you do no need to have any primary key or know unique combination of the fields. You just insert using INSERT SQL statement. But if you still need to do this, next sample shows how to pass parameters to WHERE clause of the action queries (UPDATES or DELETES)

     

    http://support.microsoft.com/kb/301075/en-us
    Tuesday, September 25, 2007 10:20 AM
    Moderator
  • Forgive my n00bness at this but I'm self-learning SQL and C#. I know how to write an Insert statement and that is how I'm doing this now, I loop through all the changed rows manually and insert each value into the source (since it's access can't do a multiple value insert) via it's ouwn oledbcommand. But I figured there was a way to do it all at once.

     

    What would the insert command look like for this? I can't wrap my head around a catch all statement when the values for each row changes.

    Wednesday, September 26, 2007 1:30 PM
  • It would look like (you need to replace table name and column names with the real one)

     

    INSERT INTO MyTableNameHere (Column1, Coumn2, Column3) VALUES (?, ?, ?)

     

    Now you can use this parameterized SQL statement in your command to pass three values and insert them into database. When you insert you do not need to specify WHERE clause, since it is not an update o delete that needs to locate existing row in a database

    Thursday, September 27, 2007 10:55 AM
    Moderator