troubleshooting Question

Copy Data from one datatable to another datatable.

Avatar of kerrymr
kerrymr asked on
.NET ProgrammingVisual Basic.NET
7 Comments1 Solution94 ViewsLast Modified:
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

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.
ASKER CERTIFIED SOLUTION
Ark

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros