none
How to copy records into the same table? RRS feed

  • Question

  •  

    Hello.

    I need some help in copying some records from a table into the same table, changing only the fields of the key, of course.

     

    I thought to read interested records into a data adapter and after into a dataSet, afterward modify the key looping on the datatable rows and at the end apply the method "Update" of dataAdapter. But the result is not a copy, but really an update... How can I do to solve my problem?

    Any suggestions? 

     

    Thank you very much.

     

    fedrok

    Thursday, January 3, 2008 4:20 PM

Answers

  • Here's a simple approach:

     

    Code Block

    public void CopyRow(DataRow fromRow, string keyColumnName, int newKey)

    {

       DataRow toRow = fromRow.Table.NewRow();

       foreach (DataColumn c in fromRow.Table.Columns)

       {

          if (c.ColumnName != keyColumnName)

          {

             toRow[c] = fromRow[c];

          }

       }

       toRow[keyColumnName] = newKey;

       fromRow.Table.Rows.Add(toRow);

    }

     

     

     

    Friday, January 4, 2008 10:12 AM

All replies

  • I think you have a case of the "designers"

    It is a small condition that affects many programmers

    Known cures - code or win the lotto and give up this crazy programming business 

     

    If you look into sql statements and datareaders then you will find what you need.

    You could easily read the records one at a time with a datareader and change the values you want and then insert the values into the database as a new record.

     

    you don't need anything from the designer to do this.  It can all be done with a little code.  Once it is done you can display the new records and your set.

     

    Do some searching and i think you will find what you need.

    Keywords - sql statements, datareader

     

    I have an application that i am finishing up that i will have available from my website to download which automatically writes these statements for you just by selecting the columns (fields) you want from a list.  It connects to a database of your choice and will gather all the data for you to choose from.  I do have the first part done which is already available to download which will write the sql statements for you but not the command execution code.

     

    If you would like to get it from my website the address is below in my signature line.

    I expect to have the next part uploaded by next week sometime

    Thursday, January 3, 2008 6:13 PM
  • First of all, many thanks for your help.

     

    The solution using dataReader or dataTable and copying record one by one is clear.

    But I would like to find a simple solution, like "in a twist" because I have a simple table and the primary key is a simple numeric field. The request is to copy a record into the same table only changing the only numeric field composing the key of my table.

    Table has many many fields, and I don't want to reference this fields by code, because the schema of table is floating, is subject to many monthly update (new fields are added every month), and so I don't want to modify my code every month.

     

    I thought to use a simple sql instruction, like

     

    insert into mytable values( select * from mytable where myfield1= myvalue1 and myfield2=myvalue2)

     

    But the problem is that the numeric field composing the table key is not autoincrement, and so previuos statement is not applyable.

     

    Any suggestions?

     

    Thank you.

     

    Friday, January 4, 2008 9:22 AM
  • Here's a simple approach:

     

    Code Block

    public void CopyRow(DataRow fromRow, string keyColumnName, int newKey)

    {

       DataRow toRow = fromRow.Table.NewRow();

       foreach (DataColumn c in fromRow.Table.Columns)

       {

          if (c.ColumnName != keyColumnName)

          {

             toRow[c] = fromRow[c];

          }

       }

       toRow[keyColumnName] = newKey;

       fromRow.Table.Rows.Add(toRow);

    }

     

     

     

    Friday, January 4, 2008 10:12 AM
  • Yes!!!!

    This is the simplest solution I was looking for.

    Thank you Sir. And congratulations.

     

    Friday, January 4, 2008 10:32 AM