Problems copy from table and adding to table

[johmolan]

I try to copy data from rows in my db and then add them with another ID.

but somehow I think I have made a mess out of it.

Public Sub Ordreupdate()

        Dim dtbl As New DataTable
        Dim dt As New DataTable
        dtbl = Kalkyle1DataSet.Tables("Ordre")
        dt = dtbl.Clone

        Dim copyRows1 As DataRow
        Dim copyRows() As DataRow = _
        Kalkyle1DataSet.Ordre.Select("OrdreID = 1")


        For Each copyRows1 In copyRows
            dt.Rows.Clear()
        Next
        For Each copyRows1 In copyRows
            dt.ImportRow(copyRows1)
        Next
        'DataGridView1.DataSource = dt


        '*********************************************************************
        Dim conn = New SqlClient.SqlConnection
        conn = New SqlConnection(Form1.DS2)
        Dim myScalarQuery As String
        Dim ID As Integer
        Dim MaxID As Integer
        Dim KundeID As Integer

        conn.Open()
        myScalarQuery = " Select Max(OrdreID) As ID From Ordre"
        Dim myCommand As New SqlCommand(myScalarQuery, conn)
        ID = myCommand.ExecuteScalar()
        conn.Close()
        MaxID = ID + 1
        KundeID = CInt(KundeIDTextBox.Text)
        



        Dim n As Integer = 0
        For Each copyRows1 In copyRows
            Dim newOrdreRow As DataRow = Kalkyle1DataSet.Tables("Ordre").NewRow()
            Dim Ordredato As String = Kalkyle1DataSet.Tables("Ordre").Rows(n).Item("Ordedato")
            Dim Kalkopprettet As String = Kalkyle1DataSet.Tables("Ordre").Rows(n).Item("Kalkyle_opprettet")
            Dim LevDato As String = Kalkyle1DataSet.Tables("Ordre").Rows(n).Item("Leveringsdato")
            Dim Virkelig_LevDato As String = Kalkyle1DataSet.Tables("Ordre").Rows(n).Item("Virkelig_leveringsdato")
            Dim Hovedtegningnr As String = Kalkyle1DataSet.Tables("Ordre").Rows(n).Item("Hovedtegningnr")
            Dim Prodtype As String = Kalkyle1DataSet.Tables("Ordre").Rows(n).Item("Produkttype")
            Dim MatType As String = Kalkyle1DataSet.Tables("Ordre").Rows(n).Item("Materialtype")
            Dim KonstrDato As String = Kalkyle1DataSet.Tables("Ordre").Rows(n).Item("Konstruktor_dato")
            Dim KalkUtarb As String = Kalkyle1DataSet.Tables("Ordre").Rows(n).Item("Kalkyle_utarbeidet")
            Dim EtterkalkUtarb As String = Kalkyle1DataSet.Tables("Ordre").Rows(n).Item("Etterkalkyle_utarbeidet")
            Dim TegnRev As String = Kalkyle1DataSet.Tables("Ordre").Rows(n).Item("Tegningsrevisjon")
            Dim Status As Integer = Kalkyle1DataSet.Tables("Ordre").Rows(n).Item("Status")


            newOrdreRow("OrdreID") = MaxID
            newOrdreRow("KundeID") = KundeID
            newOrdreRow("Ordedato") = Ordredato
            newOrdreRow("Kalkyle_opprettet") = Kalkopprettet
            newOrdreRow("Leveringsdato") = LevDato
            newOrdreRow("Virkelig_leveringsdato") = Virkelig_LevDato
            newOrdreRow("Hovedtegningnr") = Hovedtegningnr
            newOrdreRow("Produkttype") = Prodtype
            newOrdreRow("Materialtype") = MatType
            newOrdreRow("Konstruktor_dato") = KonstrDato
            newOrdreRow("Kalkyle_utarbeidet") = KalkUtarb
            newOrdreRow("Etterkalkyle_utarbeidet") = EtterkalkUtarb
            newOrdreRow("Tegningsrevisjon") = TegnRev
            newOrdreRow("Status") = Status


            Kalkyle1DataSet.Tables("Ordre").Rows.Add(newOrdreRow)
            n = n + 1
        Next

        OrdreTableAdapter.Update(Kalkyle1DataSet.Ordre)


    End Sub

When I look at it it looks like I copy the data from the table correctly but when I declare and add the data I think I have done something wrong since ex:

Dim Ordredato As String = Kalkyle1DataSet.Tables("Ordre").Rows.Item("Ordedato")
points to a spesific row in the table and then ignores the copy I have done before?

I use a datagridview to see what data I have copied and that looks right.

So what I basicly need is to get data from the table for a spesific OrdreID and then add it back in under a new OrdreID.

Can someone give me a hand here?

 

[cjard]

What database are you using? In oracle I would simply run this statement:

INSERT INTO tblWhatever
SELECT 
  maxid + rown,
  columnA,
  columnB
FROM
  (
    SELECT rownumber as rown, max(id) as maxid, columnA, columnB FROM tblWhatever
  )

Your success will depend on your ability to create a column that is the row number, in the inner query

Actually, in Oracle I would have a SEQUENCE generating incrementing numbers, and just do this:

INSERT INTO tblWhatever SELECT * FROM tblWhatever

The ID would be overwritten by a new sequence value oin the trigger:

CREATE OR REPLACE TRIGGER trg_tblwhatever BEFORE INSERT ON tblwhatever FOR EACH ROW
AS
  SELECT tblwhateverid.nextval INTO :new.id FROM dual;
END;

If youre not using Oracle, look for ways to make a row number or sequence/trigger in your db:
http://www.databasejournal.com/feat...301/RowNumber-function-in-SQL-Server-2005.htm for example

Note, avoid using SELECT max(id) in a trigger to calculate a new ID.. It must run for each inserted row, rather than running once per statement (that can insert thousands of rows) in the above maxid+rownumber example



If you really must do this in code, fill out this pseudo code:

'Fill the datatable

'Find the max ID in the table

'Loop over the table
'  change the ID
'  call SetAdded on the row
'End loop

'Update the datatable in the tableadapter

You seem to be going to great effort to make the rows appear "new" to .net - just calling setAdded will achieve the same purpose