none
Can I copy TableAdapter queries to a new copy of the table in my dataset??? RRS feed

  • Question

  •  

    I changed one of the Primary Keys on my database  (I added another column to be part of the primary key)  -- I then needed to add this change to my dataset so I drug out another copy of that particular table onto my dataset designer.

     

    IT made a "Giving1" table and I still have the Giving table.  MY problem (not that big really, but would like to know if there is an easy way to do this) is that I have several queries already setup on the original GIVING table.  Can I copy these queries somehow?  Or do I have to redo them, then rename the tables so that my code will use this new table?

    Friday, September 14, 2007 6:56 PM

Answers

  • (I added another column to be part of the primary key)

     

    You have created a composite primary key so you have to ALTER all your queries to account for your composite key or you code will fail in the new table.  So go into management studio and ALTER your queries changing table name and the key as needed and run the code against the new table.

    Friday, September 14, 2007 7:22 PM

All replies

  • (I added another column to be part of the primary key)

     

    You have created a composite primary key so you have to ALTER all your queries to account for your composite key or you code will fail in the new table.  So go into management studio and ALTER your queries changing table name and the key as needed and run the code against the new table.

    Friday, September 14, 2007 7:22 PM
  • Except that he's talking about TableAdapter commands and DataTables in the DataSet, not the database. 

    If the queries don't reference the primary key column, they don't need to be changed.  And in any event changing them in Management Studio won't help, because the queries are external to the database.  (Unless Management Studio gives you a way of editing ADO DataSets that I don't know about, which would be teh awesome.)

     

    The best you can do, I think, is to manually create the commands in Giving1's TableAdapter, copying and pasting the command text from the corresponding commands in Giving's TableAdapter.

    Friday, September 14, 2007 8:49 PM
  • EDIT

    (IT made a "Giving1" table and I still have the Giving table.)

     

    The above is from the original post there are now two tables not one so unless the queries does not include a FROM clause they need to be changed, I was giving a cleaner solution for data integrity reasons.  The code running in VS2005 does not mean it will run in SQL Server  2005 because of delayed execution.

    Friday, September 14, 2007 8:57 PM
  • I think you're confused about what he actually did.  From the original post:  "I then needed to add this change to my dataset so I drug out another copy of that particular table onto my dataset designer."

     

    He modified the schema of the Giving table in SQL Server.  Then he dragged the Giving table from the Database Explorer to the DataSet designer surface.  Since there's already a DataTable named Giving, and a GivingTableAdapter, the designer created a new DataTable called Giving1 and a Giving1TableAdapter.  The Giving DataTable has the table's schema from before he added the new columns, and the schema of the Giving1 DataTable has the new columns.

     

    There's no table called Giving1 in his database.  There's only a DataTable called Giving1 in his DataSet.

     

    The commands in the GivingTableAdapter which don't need to reference the new columns can be copied to the Giving1TableAdapter.  It's a pretty tedious process.  One at a time, he'll have to copy their text to the clipboard, add the query to the Giving1TableAdapter, paste its text into the Add Query... window, and give the new query the same name and properties that it had in the old table adapter.

     

    Then he can delete the Giving DataTable (and its adapter) and rename the Giving1 DataTable (and its adapter), and his program's code will (mostly) still work.  (Any code that directly references a DataRow's ItemArray will need to be changed, but the whole point of using typed datasets is that you don't have to write much code like that.)

    Saturday, September 15, 2007 1:46 AM
  • (I changed one of the Primary Keys on my database  (I added another column to be part of the primary key)  -- I then needed to add this change to my dataset so I drug out another copy of that particular table onto my dataset designer.)

     

    You are quick to call me confused, the thread is not yours please don't talk to me again. All I am saying is make the changes in the database before running it through ADO.NET. The reason is changing Primary Key is not that simple it can lead to data corruption and other problems that will take longer to clean up than the time it takes to make the changes I recommended. Recommended is the operative word, the person can choose your advice but please don't talk to me again.

     

     

     

    Saturday, September 15, 2007 4:36 AM
  • I meant no offense.  There's no conflict between what you've recommended and what I have.  You're completely correct that changing the PK is non-trivial.

     

    My point is that what you're suggesting he do - make the changes in the database and then make them in ADO.NET - is what he said he did in his original post.  The question he's asking is:  what to do next?  How does he get the ADO DataSet back in sync with what's in the database?

    Saturday, September 15, 2007 4:57 AM
  • Sorry to cause any problems... I just followed the first suggestion and got what I needed done.  I had a datagrid view tied to some of the queries in the early tableadapter so I HAD to copy those queries.

     

    Anyway, its working fine and thank you both for the help!

    Saturday, September 15, 2007 4:03 PM