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:
I get this error...
System.InvalidOperationExc eption: 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.
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.InvalidOperationExc
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.
Here is SQL commands:
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
"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 exists2. Drop RouterID column if exists
3. Create identity column RouterId
4. Create PK
5. Create OrderNumber column if not exists
6. Fill OrderNumber column
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(" @WorkCente r", 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
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("
.Parameters.AddWithValue("
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
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 & "'"
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks very much for all your expert knowledge and assistance!
PS. Or build SQL command manually