Avatar of kerrymr
kerrymr
 asked on

Copy Data from one datatable to another datatable.

Hi experts!
I have two datasets in SQL and a user form that I am working with is visual studio.  VB language.
Steps I need to accomplish:
1. Get data from SQl table
2. Get data from user form.
3. Combine the data together and update a different SQl table.

Note that Field
+   "RouterID" is a primary key in SQL with identity specification = true.
+ "OrderNumber" is an automatically generated  field on the form.


My code so far:

       ssql = "SELECT OpNo, WorkCenter, "
        ssql = ssql + "OpShortTxt, BaseQty, SetupMachine, SmUnit, SetupLabor, SlUnit, MachineRun, MrUnit, "
        ssql = ssql + "LaborRun, LrUnit, StdVal5, V5Unit, StdVal6, V6Unit "
        ssql = ssql + "from TSAPRouterData Where "
        ssql = ssql + "PartNumber ='" & TxtPartNumber.Text & "' and GroupCounter='" & DgRouters.SelectedRows(0).Cells(1).Value.ToString & "'"

        da = New SqlDataAdapter(ssql, conn)
        cb = New SqlCommandBuilder(da)
        ds = New DataTable
        da.Fill(ds)

        Dim ssql2 As String = "SELECT OpNo, WorkCenter, "
        ssql2 = ssql2 + "OpShortTxt, BaseQty, SetupMachine, SmUnit, SetupLabor, SlUnit, MachineRun, MrUnit, "
        ssql2 = ssql2 + "LaborRun, LrUnit, StdVal5, V5Unit, StdVal6, V6Unit, OrderNumber, RouterID"
        ssql2 = ssql2 + "from TReleasedRouter"

        Dim da2 = New SqlDataAdapter(ssql, conn)
        Dim cb2 = New SqlCommandBuilder(da2)
        Dim ds2 = New DataTable
        da2.Fill(ds2)

        ds2 = ds.Copy()

' My attempt at trying to figure out the primary key thing from Microsoft website.

        Dim keys(2) As DataColumn
        Dim column As DataColumn

        column = New DataColumn()
        column.DataType = System.Type.GetType("System.String")
        column.ColumnName = "OrderNumber"

        ds2.Columns.Add(column)

        column = New DataColumn()
        column.DataType = System.Type.GetType("System.Int16")
        column.ColumnName = "RouterID"
        ds2.Columns.Add(column)

        keys(1) = column
        ds2.PrimaryKey = keys

        Dim i As Integer = 0

' add the information from the form to each datarow and a unique number if necessary but id rather SQl handle the unique number.
        For Each row As DataRow In ds2.Rows
            row.Item("OrderNumber") = TxtCustOrderNo.Text
            row.Item("RouterID") = i
            i = i + 1
        Next

        da2.Update(ds2) <-------ERROR IS HERE

Open in new window


I get this error...
System.InvalidOperationException: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

I am open to the easiest solution possible to get past this error.  If this is crazy convoluted code feel free to suggest alternate working approaches.
.NET ProgrammingVisual Basic.NET

Avatar of undefined
Last Comment
kerrymr

8/22/2022 - Mon
Ark

Table adapter can not generate Update, Delete and Insert commandfor tables which doesn't contain primary key. Add PK to second table (TReleasedRouter) using SQL designer.
PS. Or build SQL command manually
Ark

Here is SQL commands:
"IF NOT EXISTS (SELECT * FROM sys.key_constraints WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = 'TReleasedRouter') ALTER TABLE TReleasedRouter DROP CONSTRAINT pk_TReleasedRouter_RouterID"
"IF COL_LENGTH('TReleasedRouter', 'RouterID') IS NOT NULL ALTER TABLE TReleasedRouter DROP COLUMN RouterID"
"ALTER TABLE TReleasedRouter ADD  RouterID int identity(0,1) not null"
"ALTER TABLE TReleasedRouter ADD CONSTRAINT pk_TReleasedRouter_RouterID primary key(RouterID)"
"IF COL_LENGTH('TReleasedRouter', 'OrderNumber') IS NULL ALTER TABLE TReleasedRouter ADD OrderNumber varchar(50)"
"UPDATE TReleasedRouter SET OrderNumber = '" & TxtCustOrderNo.Text & "'"

Open in new window

1. Drop PK if exists
2. Drop RouterID column if exists
3. Create identity column RouterId
4. Create PK
5. Create OrderNumber column if not exists
6. Fill OrderNumber column
K R

Thanks for the update.
I ended up switching my string to parameter queries.
In case anyone ever has the same problem, google parameter queries.

My sample:
Before you use this code to update the SQL tables, pull the data from your "read" dataset and populate a datatable ds.


        Dim ssql2 As String = "Insert into TReleasedRouter (OpNo, WorkCenter, "
        ssql2 = ssql2 + "OpShortTxt, BaseQty, SetupMachine....

        Using comm As New SqlCommand()
            With comm
                .Connection = conn
                .CommandType = CommandType.Text
                .CommandText = ssql2

                    For Each row As DataRow In ds.Rows
                        .Parameters.AddWithValue("@OpNo", row.Item(0))
                        .Parameters.AddWithValue("@WorkCenter", row.Item(1)).....
                        If conn.State = ConnectionState.Closed Then conn.Open()
                        comm.ExecuteNonQuery()
                        comm.Parameters.Clear()
                    Next
                Catch ex As Exception
                    MsgBox(ex.ToString, vbInformation, "Error")
                    Exit Sub
                Finally
                    If conn.State = ConnectionState.Open Then conn.Close()
                End Try
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Ark

In this case you can do it within 1 command:
ssql = "INSERT INTO TReleasedRouter (OrderNumber, OpNo, WorkCenter, OTHER_COLUMNS)"
ssql += "SELECT '" & TxtCustOrderNo.Text & "', OpNo, WorkCenter, OTHER_COLUMNS "
ssql += "from TSAPRouterData Where "
ssql += "PartNumber ='" & TxtPartNumber.Text & "' and GroupCounter='" 
ssql += "DgRouters.SelectedRows(0).Cells(1).Value.ToString & "'"

Open in new window

kerrymr

ASKER
I used to have the code like this then I read about sql injection attacks.  I thought best practice was a parameterized query.
ASKER CERTIFIED SOLUTION
Ark

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
kerrymr

ASKER
Thanks very much for all your expert knowledge and assistance!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.