INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Copy Data table item to certain row and certain column.

Copy Data table item to certain row and certain column.

(OP)
Hello People,

below is a picture of how my result looks in the datagridview



I have made a simple SQL Query that put the results in a data-table where I already prepared certain columns I have created.

there are 20 different columns.

The picture below includes a column headed ”order number” (column 2). I need a function that contains a loop that will perform the following function:

If an item exists twice in the column, regardless of row, the first instance must control all identical instances throughout. When an identical instance is identified, the content from the “target language” column in the first instance must be copied to the “target language” columns in all the other instances.


Could someone help me with a code?


here is what I got so far this is a part of old code

CODE --> VB.net

If HashDatatable1.Rows.Count > 0 Then
            For i As Integer = 0 To HashDatatable1.Rows.Count - 1
                If Not HashDatatable1.Rows(i).Item(18).ToString.Contains("Language:") Then
                    HashDatatable1.Rows(i)(18) = "0"
                End If


                '  MsgBox(HashDatatable1.Rows(i).Item(18).ToString())


            Next
        End If 

Thank you in advance

RE: Copy Data table item to certain row and certain column.

This should do it:

Dim ThisOrder As Integer = 0
Dim LastOrder As Integer = 0
Dim ThisLanguage As String = ""

For i As Integer = 0 To HashDatatable1.Rows.Count - 1
ThisOrder = HashDatatable1.Rows(i).Item("OrderNumber") 'get Order Number from current row

If i = 0 Then ThisLanguage = HashDatatable1.Rows(i).Item("TargetLanguage") 'Get Target Language if this is the first row

If i > 0 Then 'only run this code for rows after the first
If ThisOrder = LastOrder Then 'check if ThisOrder is the same as LastOrder
HashDatatable1.Rows(i).Item("TargetLanguage") = ThisLanguage 'ThisOrder and LastOrder match, so copy ThisLanguage to current row's Target Language
Else
ThisLanguage = HashDatatable1.Rows(i).Item("TargetLanguage") 'ThisOrder and LastOrder do not match (i.e., this is a new order number), so get the Target Language from the current row
EndIf
EndIf
LastOrder = ThisOrder 'set LastOrder equal to ThisOrder
Next

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

RE: Copy Data table item to certain row and certain column.

(OP)
You are a monster works as a charm!!!!!

THANK YOU!!

RE: Copy Data table item to certain row and certain column.

(OP)
Hello again!

I want to reuse the code but I need to modify it a bit though...

Below it is a picture of two different data tables presented in two different datagridviews.

Table1= contains 20 columns
table2= contains 1 column

both tables contains a column called "Context Matches"(just different column index numbers)

Now I need to loop every item in table1 and column "Order number",
if any row item in "table2" column "Context Matches" contains the item (from column "Order number"(table1))
then copy founded item from "table2" to "table1" column "Context Matches".





Could you help me?

Thank you in advance.

RE: Copy Data table item to certain row and certain column.

How exactly would you match the two records? Is the OrderNumber included in the ContextMatches field in the second table?

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

RE: Copy Data table item to certain row and certain column.

I don't have time at the moment to give you an example - I have deadlines to meet on my current project, however:

Lookup PATINDEX in SQL Server's Books On Line (BOL) which will show you how to define the pattern matching criteria to extract OrderNumber from ContextMatches
and then run an UPDATE query to update the relevant column in Table1.

RE: Copy Data table item to certain row and certain column.

(OP)
Hi again @Jebenson,
Yes its included in the context matches but not only the number example:
This is Table1 and column "Order number"
23443
12323
32523
23434


Here is how the table2 context matches column can look like:
I believe we need a regex to or something to select the order numberor something..hmm (\d{5})



(-1) - 35069 – Language: USA-DEU;
Reuse: 4171;
Context Match: 2507;
Total: 6678;
Include CM in Reuse: No;





Thank you in advance.


Lemme me know if you need any more info.

RE: Copy Data table item to certain row and certain column.

(OP)
Hi again Softhemc,

Thank you again I'll take a look on it!

RE: Copy Data table item to certain row and certain column.

Ok I've put something together quickly.

I've assumed that (-1) - is a constant length as as such and have put together a couple of tables based on that assumption.
I've also used ContextMatch as a column in Table1 and ContextMatches in Tablle2 just to make the code more readable.

Obviously replace with your own Table and Column names as appropriate.

CODE

UPDATE Table1
  SET ContextMatch = t2.ContextMatches
FROM Table2 t2
WHERE SUBSTRING(t2.ContextMatches, 7, 6) = OrderNumber; 

Note the OrderNumber column in the last line does not need a table prefix as it is assumed to be a column in the default table (ie the UPDATE table)

I've tested this on half a dozen dummy records and it works as expected.

The numbers in SUBSTRING refer to Start (7th character) and Length (6 characters), adjust those as needed.

RE: Copy Data table item to certain row and certain column.


You can do this with the RowFilter property of the DataView:

Dim dv As DataView = Table2.DefaultView

For Each dr As DataRow in Table1.Rows
dv.RowFilter = "ContextMatches LIKE '%" & dr.Item("OrderNumber") & "%'"
For Each drv As DataRowView in dv
drv.item("ContextMatches") = dr.Item("ContextMatches")
Next
dv.RowFilter = Nothing
Next

It would probably be a good idea to get distinct values for OrderNumber from Table1, so you're not rewriting the same information multiple times when an OrderNumber appears more than once.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

RE: Copy Data table item to certain row and certain column.

(OP)
hi softhemc@,

unfortunately
(-1) is not constant.
that is a number that can change from (-1) to example (1231323)

Best regards

Daniel

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close