copy records

[johmolan]

I am trying to copy records in my db from from 1 row to another .
I have a relation db, there I have 1 customer table With primarykey called CustomerID, 1 Ordertable with OrderID as Primarykey and OrderID as foreignkey.
then I have several Processtables all connected with orderID as foreignkey.

Now I want to copy all records connected to Order1 into Order2

When I start my program the tableadapters fill my dataset and my tables.

then I tried to insert into the ordretable the values allready in the tableadapter .
But I don't think I can do it the way I thougt.

The code I tried looks like this:

Dim OrdreTableAdapter As New Kalkyle1DataSetTableAdapters.OrdreTableAdapter

        OrdreTableAdapter.Insert(OrdreID, @Ordedato, @Kalkyle_opprettet, @Leveringsdato, @Virkelig_leveringsdato, @Hovedtegningnr, @Ordre.Produkttype, _
        @Materialtype,@Konstruktordato, @Kalkyle_utarbeidet, @Etterkalkyle_utarbeidet, @Tegningsrevisjon,MaxKundeID, @Status)

Is there a way to update or insert the values allready in the adapter into a row where the OrderId allready is set?

 

[johmolan]

maybe I can use something like:

Dim copyRows() As DataRow = _
Kalkyle1DataSet.Tables("Ordre").Select("KundeID = '1'")


Dim copyRow As DataRow

For Each copyRow In copyRows
OrderTable.ImportRow(copyRow)
Next

But I guess somehow the orderID will make it fail, is it a way around it or is this the wrong way to do this??

 

[MattP]

Here's a quick example I did using the ImportRow method.

        Dim ds As New DataSet
        Dim dt As New DataTable("DelimFile")

        With dt.Columns
            .Add("Column1", GetType(Integer))
            .Add("Column2", GetType(Integer))
            .Add("Column3", GetType(Integer))
            .Add("Column4", GetType(String))
            .Add("Column5", GetType(Double))
        End With

        For Each line In IO.File.ReadAllLines("C:\Temp\Compute.txt")
            dt.Rows.Add(line.Split(","c))
        Next

        Dim dt1 As New DataTable("Subset")
        dt1 = dt.Clone()

        Dim theRows As DataRow() = dt.Select("Column2 = 2")

        For Each row As DataRow In theRows
            dt1.ImportRow(row)
        Next

Edit: Found this link showing Microsoft suggesting the same thing. How To Copy DataRows Between DataTables by Using Visual Basic .NET

 

[johmolan]

I tried it with this code:

Dim copyRows() As DataRow = _
Kalkyle1DataSet.Tables("Ordre").Select("KundeID = '1' AND OrdreID = 1")

Dim copyRow As DataRow

For Each copyRow In copyRows

Kalkyle1DataSet.Tables("Ordre").ImportRow(copyRow)
Next

Me.OrdreTableAdapter.Fill(Me.Kalkyle1DataSet.Ordre)

But I need to change the OrdreID in all rows to 3 before I import them,

How do I do that?

 

[johmolan]

I added a table to the code, Then I import the rows into the table, but I still don't understand how I can set the first columnvalues to "3".

The code now look like this:

Dim copyRows() As DataRow = _
Kalkyle1DataSet.Tables("Ordre").Select("KundeID = '1' AND OrdreID = 1")

Dim ds As New DataSet
Dim dtOrdre As New DataTable
Dim copyRow As DataRow

For Each copyRow In copyRows
dtOrdre.ImportRow(copyRow)

Next


Dim copyRows2() As DataRow = _
ds.Tables("dtOrdre").Select("KundeID = '1'")
For Each copyRow In copyRows2

'Kalkyle1DataSet.Tables("Ordre").ImportRow(copyRow)
Next

Me.OrdreTableAdapter.Fill(Me.Kalkyle1DataSet.Ordre)

As you understand I need to change the columns values in OrdreID to 3

 

[johmolan]

I have now made some progress.

Can someone please take a look at my code and tell me why it only imports the last row?

Quote:

Dim dtPros18 As DataTable
Dim dtblPros18 As DataTable
Dim i As Integer
Dim ID As Integer
Dim MaxID As Integer
Dim myScalarQuery3 As String
conn.Open()
myScalarQuery3 = " Select Max(ID) As ID From Prosess_18"
Dim myCommand3 As New SqlCommand(myScalarQuery3, conn)
ID = myCommand3.ExecuteScalar()
conn.Close()
MsgBox("ID = " & ID)
MaxID = ID + 1
MsgBox("MaxID = " & MaxID)

dtblPros18 = Kalkyle1DataSet.Tables("Prosess_18")
dtPros18 = dtblPros18.Clone
Dim copyRows3() As DataRow = _
dtblPros18.Select("OrdreID = 1")

i = MaxID
For Each copyRow In copyRows3
dtPros18.ImportRow(copyRow)
dtPros18.Rows(0)("OrdreID") = MaxOrdreID
i = i + 1
MsgBox("i = " & i)
dtPros18.Rows(0)("ID") = i
Next

Dim copyRows4() As DataRow = _
dtPros18.Select("OrdreID = '1'")
For Each copyRow In copyRows4
'Kalkyle1DataSet.Tables("Prosess_18").ImportRow(co pyRow)
Next
DataGridView1.DataSource = dtblPros18

 

[johmolan]

I made this work adding a temp table, and using that to alter some of the columns.
But when I am finnished running the insert methods I guess I need to run the update method to get the new data into the DB, because I can se the new data in my datagridviews but when I restart it is gone.

But when I now run the updatemethod the error is as follows:

The UPDATE statement conflicted with the FOREIGN KEY constraint "FK__Prosess_1__Ordre__00200768". The conflict occurred in database "Kalkyle1", table "dbrdre", column 'OrdreID'. The statement has been terminated.

I have read somthing about it, that the error comes up because I am trying to "change" the child or parent table without the other etc.

How do I turn off the comstraints so I can update my db or is it another way to do this?.

Here is the code for the tables ordre and Prosess_18

Dim conn = New SqlClient.SqlConnection
conn = New SqlConnection(Form1.DS2)
Dim ds As New DataSet
Dim dtOrdre As New DataTable
Dim dtblOrdre As New DataTable
Dim n As Integer = 0

'************************************
Dim OrdreID As Integer
Dim MaxOrdreID As Integer
Dim myScalarQuery2 As String
conn.Open()
myScalarQuery2 = " Select Max(OrdreID) As OrdreID From Ordre"
Dim myCommand2 As New SqlCommand(myScalarQuery2, conn)
OrdreID = myCommand2.ExecuteScalar()
conn.Close()
MsgBox("OrdreID = " & OrdreID)
MaxOrdreID = OrdreID + 1
MsgBox("MaxOrdreID = " & MaxOrdreID)
Dim o As Integer = 0

Dim copyRow As DataRow
dtblOrdre = Kalkyle1DataSet.Tables("Ordre")
dtOrdre = dtblOrdre.Clone
Dim copyRows() As DataRow = _
dtblOrdre.Select("KundeID = '1' AND OrdreID = 1")

For Each copyRow In copyRows
dtOrdre.Rows.Clear()
Next
For Each copyRow In copyRows
dtOrdre.ImportRow(copyRow)
Next
DataGridView1.DataSource = dtOrdre
For Each copyRow In copyRows
dtOrdre.Rows(0)("OrdreID") = MaxOrdreID

Next
MsgBox("OrdreID er nå " & MaxOrdreID)

DataGridView1.DataSource = dtOrdre


Dim copyRows2() As DataRow = _
dtOrdre.Select("KundeID = '1'")
For Each copyRow In copyRows2
Kalkyle1DataSet.Tables("Ordre").ImportRow(copyRow)
Next

'**********************************************************************************
'***********************************************************************************
MsgBox("Gikk dette bra da tro ")

Dim dtPros18 As DataTable
Dim dtblPros18 As DataTable
Dim i As Integer
Dim ID As Integer
Dim MaxID As Integer
Dim myScalarQuery3 As String
Dim copyRow2 As DataRow



conn.Open()
myScalarQuery3 = " Select Max(ID) As ID From Prosess_18"
Dim myCommand3 As New SqlCommand(myScalarQuery3, conn)
ID = myCommand3.ExecuteScalar()
conn.Close()
MaxID = ID + 1



dtblPros18 = Kalkyle1DataSet.Tables("Prosess_18")
dtPros18 = dtblPros18.Clone

Dim copyRows3() As DataRow = _
dtblPros18.Select("OrdreID = 1")

i = MaxID
n = 0
For Each copyRow2 In copyRows3
dtPros18.Rows.Clear()
Next
For Each copyRow2 In copyRows3
dtPros18.ImportRow(copyRow2)
Next

For Each copyRow2 In copyRows3
dtPros18.Rows("OrdreID") = 3
n = n + 1
Next
n = 0
MsgBox("i = " & i)
For Each copyRow2 In copyRows3
dtPros18.Rows("ID") = i
n = n + 1
i = i + 1
Next

DataGridView1.DataSource = dtPros18

Dim copyRows4() As DataRow = _
dtPros18.Select("OrdreID = '3'")
For Each copyRow In copyRows4
Kalkyle1DataSet.Tables("Prosess_18").ImportRow(copyRow)
Next


Me.Prosess_18BindingSource.EndEdit()
Me.Prosess_18TableAdapter.Update(Me.Kalkyle1DataSet.Prosess_18)

Me.OrdreBindingSource.EndEdit()
Me.OrdreTableAdapter.Update(Kalkyle1DataSet.Ordre)

End Sub