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
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
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 & "'"
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
PS. Or build SQL command manually