Copy Data table item to certain row and certain column. 1

[Xsi]

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

  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

 

[jebenson]

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!

 

[Xsi]

You are a monster works as a charm!!!!!

THANK YOU!!

 

[Xsi]

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.

 

[jebenson]

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!

 

[softhemc]

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.

 

[Xsi]

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.

 

[Xsi]

Hi again Softhemc,

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

 

[softhemc]

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.

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.

 

[jebenson]

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!

 

[Xsi]

hi softhemc@,

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

Best regards

Daniel