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

Last Comment

kerrymr

8/22/2022 - Mon

Ark

10/20/2016

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

10/20/2016

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

10/21/2016

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

10/21/2016

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

10/24/2016

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

10/24/2016

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