473,231 Members | 1,703 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

How to replace(row value) / remove(rows) in DataTable

Hi all,

I have a dataTable that contains nearly 38400 rows.
In the dataTable consist of 3 column.
column 1 Name: MUHNO
column 2 Name: HESNO
Column 3 Name: BALANCE

Let me give you some example first:

++++++++++++++++++++++++++++++++++++++++
MUHNO HESNO BALANCE
----------------------------------------------------------------
0111621000 7371626 0
0911621000 7371626 -250
0911621100 7371626 -25
0914021000 7371626 0
++++++++++++++++++++++++++++++++++++++++

Now in my DataTable I have 16000 distinct HESNO
and total rows is 38400.

What I want is I have to use the HESNO to get all
the rows as in shown above and count the balance
that has MUHNO starting 09??????? and add into the row
that has 0111621000 and delete the rest of the
3 rows that their HESNO is same but their MUHNO
that stsrts with 09????????.

So I have to count for below 3 rows balance:

MUHNO HESNO BALANCE
----------------------------------------------
0911621000 7371626 -250
0911621100 7371626 -25
0914021000 7371626 0

which is -275 and in to the row balance column:
I guess I have to replace the value of the
BALANCE column for the row:

MUHNO HESNO BALANCE
---------------------------------------------
0111621000 7371626 0
What I did is that I run 38400 rows and I put all the HESNO
into array. Then I remove duplicated value in the array
and I sort the array.

Now I have to create a for loop for array
to check in dataTable and use replace/remove operation
that I decsribe above. But Unfortunatly I am stuck in
here.

Simply the operation have to be like in below:

Before
MUHNO HESNO BALANCE
--------------------------------------------
0111621000 7371626 0
After
MUHNO HESNO BALANCE
---------------------------------------
0111621000 7371626 -275
and delete the rows:
MUHNO HESNO BALANCE
---------------------------------------
0911621000 7371626 -250
0911621100 7371626 -25
0914021000 7371626 0.

Can anyone out there to tell me easy way to achive this
in fatsest way?

I thank you all for your kind understanding to reading my post.

Rgds,
Niyazi
May 11 '06 #1
3 5311
Niyazi,

This is the oldest use of dataprocessing.

Your datatable is an collection of datarows so you don't need an array.
However you can use a new datatable that you made distinct.

You can with that therefore clone that one and than it is something as here
in a kind of pseudo code.

myDistinctTable = MyOldTable.clone

create a field myPreviousKey value Is Nothing/Null
create one or more counterfields
Than you start going through your
For each row in myOldTable
if the previousKey not = CurrentKey
If not previousKey is Nothing/null
Add the new MyDistinctTableRow to that table
end if
Create new datarow
Set the key
end if
count into the new datarow
End For

"Niyazi" <Ni****@discussions.microsoft.com> schreef in bericht
news:D0**********************************@microsof t.com...
Hi all,

I have a dataTable that contains nearly 38400 rows.
In the dataTable consist of 3 column.
column 1 Name: MUHNO
column 2 Name: HESNO
Column 3 Name: BALANCE

Let me give you some example first:

++++++++++++++++++++++++++++++++++++++++
MUHNO HESNO BALANCE
----------------------------------------------------------------
0111621000 7371626 0
0911621000 7371626 -250
0911621100 7371626 -25
0914021000 7371626 0
++++++++++++++++++++++++++++++++++++++++

Now in my DataTable I have 16000 distinct HESNO
and total rows is 38400.

What I want is I have to use the HESNO to get all
the rows as in shown above and count the balance
that has MUHNO starting 09??????? and add into the row
that has 0111621000 and delete the rest of the
3 rows that their HESNO is same but their MUHNO
that stsrts with 09????????.

So I have to count for below 3 rows balance:

MUHNO HESNO BALANCE
----------------------------------------------
0911621000 7371626 -250
0911621100 7371626 -25
0914021000 7371626 0

which is -275 and in to the row balance column:
I guess I have to replace the value of the
BALANCE column for the row:

MUHNO HESNO BALANCE
---------------------------------------------
0111621000 7371626 0
What I did is that I run 38400 rows and I put all the HESNO
into array. Then I remove duplicated value in the array
and I sort the array.

Now I have to create a for loop for array
to check in dataTable and use replace/remove operation
that I decsribe above. But Unfortunatly I am stuck in
here.

Simply the operation have to be like in below:

Before
MUHNO HESNO BALANCE
--------------------------------------------
0111621000 7371626 0
After
MUHNO HESNO BALANCE
---------------------------------------
0111621000 7371626 -275
and delete the rows:
MUHNO HESNO BALANCE
---------------------------------------
0911621000 7371626 -250
0911621100 7371626 -25
0914021000 7371626 0.

Can anyone out there to tell me easy way to achive this
in fatsest way?

I thank you all for your kind understanding to reading my post.

Rgds,
Niyazi

May 11 '06 #2
Hi Cor,
Thank you for your input. My problem is that in the MUHNO table the strings
that contains 09??????? have to be remove but before remove it I have to
calculate allthe balances if their HESNO is same. Than I have to move that
balance in the MUHNO that contains the string which is doesn't start with
09?????? but it HESNO is same.

I do understand the concept that you wrote but I never done it before. So I
have bif confuse how to achive the problem I am facing, with the way I want
and combine your concept.

Here is what I did at the moment:

I create a dataRow array

tmpResultROWS = Nothing
tmpResultROWS = tmpTABLE.Select(mEXPR1, mSORT1)

then wakl throu all 38400 rows and get their HESNO into array. Then I use:

'REMOVE DUBLICATED VALUE FROM ARRAY +++++++++++++++++
Dim col As New Scripting.Dictionary
Dim ii As Integer = 0
For ii = 0 To DTHESNO_ARRAY.Length - 2
If Not col.Exists(CStr(DTHESNO_ARRAY(ii))) Then
col.Add(CStr(DTHESNO_ARRAY(ii)), ii)
End If
Next

ReDim _DTHESNOKR102A(col.Count - 1)

Dim iii As Integer = 0
For iii = 0 To col.Count - 1
_DTHESNOKR102A(iii) = col.Keys(iii)
Next

'NOW SORT THE STRING ARRAY
Array.Sort(_DTHESNOKR102A)

col = Nothing

to remove duplicated array and I sorted (But seems this section is very slow)

Then I get nearly 16500 distinct HESNO in my array.

Then I walk throu nearly 16500 HESNO as:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++
'THE UNIQUE _DTHESNOKR102A (DTHESNO ARRAY)
'tmpTABLE that contains 38399 rows

Dim i As Integer = 0
For i = 0 To _DTHESNOKR102A.Length - 1
Dim mxDTHESNO As String = ""
mxDTHESNO = _DTHESNOKR102A(i)

Dim xcEXPR1 As String = "WWWW = " & mxDTHESNO
Dim xcSORT1 As String = "WWWW ASC"
Dim resROWS As DataRow()
resROWS = Nothing
resROWS = tmpTABLE.Select(xcEXPR1, xcSORT1)

Dim myBALANCE As Decimal = 0.0
If resROWS.Length > 1 Then
Dim mLen As Integer = resROWS.Length - 1
Dim yumax As Integer = 0
For yumax = 0 To mLen
myBALANCE = myBALANCE + CDec(resROWS(yumax).ItemArray(16))
Next

Dim trimax As Integer = 0
For trimax = 0 To mLen
Dim tmpDTMHNO As String = ""
tmpDTMHNO = resROWS(trimax).ItemArray(20)
tmpDTMHNO = tmpDTMHNO.Remove(2, 8)

If tmpDTMHNO = "09" Then
'DELETE or REMOVE OPERATION

'Check the resROWS(i) to see if it is the
ItemArray(20)'s first 2
'characeter is "09". If ture than remove the row from
tmpTABLE.

'===========================================
tmpTABLE.Rows.Remove(resROWS(trimax))
'============================================

Else
'REPLACE OPERATION
'If the rows ItemArray(20)'s first 2 characeter is NOT
"09" then
'replace that rows ItemArray(16) value with variable
call myBALANCE
'and the replace the entire row in tmpTABLE

'===========================================
resROWS(trimax).ItemArray(16) = CStr(myBALANCE)
Dim rowE As DataRow = resROWS(trimax)
'Delete the rows first
tmpTABLE.Rows.Remove(resROWS(trimax))
'And insert the new row
tmpTABLE.Rows.Add(rowE)
'============================================
End If
Next
End If
Next

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++
My problem lies in here:
'DELETE or REMOVE OPERATION
'===========================================
tmpTABLE.Rows.Remove(resROWS(trimax))
'============================================

And

'REPLACE OPERATION
'===========================================
resROWS(trimax).ItemArray(16) = CStr(myBALANCE)
Dim rowE As DataRow = resROWS(trimax)
'Delete the rows first
tmpTABLE.Rows.Remove(resROWS(trimax))
'And insert the new row
tmpTABLE.Rows.Add(rowE)
'============================================
I couldn't find time to test it yet, but I am wondering if the line
tmpTABLE.Rows.Remove(resROWS(trimax))

realy removes the data. Plus does the replace operation is works?
Because when I test it this part:

resROWS(trimax).ItemArray(16) = CStr(myBALANCE)

It seems I cannot replace the value in column(16).

What I realy need is (as you can see from my code) does this lines is
correct that removes the row from dataTable?
tmpTABLE.Rows.Remove(resROWS(trimax))

Plus does this code replace the value for specific column in specific rows:
resROWS(trimax).ItemArray(16) = CStr(myBALANCE)

I realy appricate if I can find some answer. By the way working with data
that is constructed in AS400 nearly 25 years ago and updated nearly 10 years
ago it realy kills me to code very efficently.

Anyway I thank you for your kind understanding to reading my post.

Rgds,
GC


"Cor Ligthert [MVP]" wrote:
Niyazi,

This is the oldest use of dataprocessing.

Your datatable is an collection of datarows so you don't need an array.
However you can use a new datatable that you made distinct.

You can with that therefore clone that one and than it is something as here
in a kind of pseudo code.

myDistinctTable = MyOldTable.clone

create a field myPreviousKey value Is Nothing/Null
create one or more counterfields
Than you start going through your
For each row in myOldTable
if the previousKey not = CurrentKey
If not previousKey is Nothing/null
Add the new MyDistinctTableRow to that table
end if
Create new datarow
Set the key
end if
count into the new datarow
End For

"Niyazi" <Ni****@discussions.microsoft.com> schreef in bericht
news:D0**********************************@microsof t.com...
Hi all,

I have a dataTable that contains nearly 38400 rows.
In the dataTable consist of 3 column.
column 1 Name: MUHNO
column 2 Name: HESNO
Column 3 Name: BALANCE

Let me give you some example first:

++++++++++++++++++++++++++++++++++++++++
MUHNO HESNO BALANCE
----------------------------------------------------------------
0111621000 7371626 0
0911621000 7371626 -250
0911621100 7371626 -25
0914021000 7371626 0
++++++++++++++++++++++++++++++++++++++++

Now in my DataTable I have 16000 distinct HESNO
and total rows is 38400.

What I want is I have to use the HESNO to get all
the rows as in shown above and count the balance
that has MUHNO starting 09??????? and add into the row
that has 0111621000 and delete the rest of the
3 rows that their HESNO is same but their MUHNO
that stsrts with 09????????.

So I have to count for below 3 rows balance:

MUHNO HESNO BALANCE
----------------------------------------------
0911621000 7371626 -250
0911621100 7371626 -25
0914021000 7371626 0

which is -275 and in to the row balance column:
I guess I have to replace the value of the
BALANCE column for the row:

MUHNO HESNO BALANCE
---------------------------------------------
0111621000 7371626 0
What I did is that I run 38400 rows and I put all the HESNO
into array. Then I remove duplicated value in the array
and I sort the array.

Now I have to create a for loop for array
to check in dataTable and use replace/remove operation
that I decsribe above. But Unfortunatly I am stuck in
here.

Simply the operation have to be like in below:

Before
MUHNO HESNO BALANCE
--------------------------------------------
0111621000 7371626 0
After
MUHNO HESNO BALANCE
---------------------------------------
0111621000 7371626 -275
and delete the rows:
MUHNO HESNO BALANCE
---------------------------------------
0911621000 7371626 -250
0911621100 7371626 -25
0914021000 7371626 0.

Can anyone out there to tell me easy way to achive this
in fatsest way?

I thank you all for your kind understanding to reading my post.

Rgds,
Niyazi


May 12 '06 #3
Hi,
The not perfect way to achive this is in below. I will try to test the Cor's
pesude code on vb-tips.com

If tmpDTMHNO = "09" Then
'DELETE or REMOVE OPERATION
++++++++++++++++++++++++++++++++++++++++
resROWS(trimax).Delete()
Else
'REPLACE OPERATION
+++++++++++++++++++++++++++++++++++++++++++++++++
Dim mFlag As Boolean = False
resROWS(trimax).Item(16) = CStr(myBALANCE)
If resROWS(trimax).Item(16) = "0" Then
mFlag = True
Else
mFlag = False
End If

If mFlag = True Then
resROWS(trimax).Delete()
Else
'Do Nothing in here
End If
End If

Thank you.

Rgds,
Niyazi

"Niyazi" wrote:
Hi Cor,
Thank you for your input. My problem is that in the MUHNO table the strings
that contains 09??????? have to be remove but before remove it I have to
calculate allthe balances if their HESNO is same. Than I have to move that
balance in the MUHNO that contains the string which is doesn't start with
09?????? but it HESNO is same.

I do understand the concept that you wrote but I never done it before. So I
have bif confuse how to achive the problem I am facing, with the way I want
and combine your concept.

Here is what I did at the moment:

I create a dataRow array

tmpResultROWS = Nothing
tmpResultROWS = tmpTABLE.Select(mEXPR1, mSORT1)

then wakl throu all 38400 rows and get their HESNO into array. Then I use:

'REMOVE DUBLICATED VALUE FROM ARRAY +++++++++++++++++
Dim col As New Scripting.Dictionary
Dim ii As Integer = 0
For ii = 0 To DTHESNO_ARRAY.Length - 2
If Not col.Exists(CStr(DTHESNO_ARRAY(ii))) Then
col.Add(CStr(DTHESNO_ARRAY(ii)), ii)
End If
Next

ReDim _DTHESNOKR102A(col.Count - 1)

Dim iii As Integer = 0
For iii = 0 To col.Count - 1
_DTHESNOKR102A(iii) = col.Keys(iii)
Next

'NOW SORT THE STRING ARRAY
Array.Sort(_DTHESNOKR102A)

col = Nothing

to remove duplicated array and I sorted (But seems this section is very slow)

Then I get nearly 16500 distinct HESNO in my array.

Then I walk throu nearly 16500 HESNO as:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++
'THE UNIQUE _DTHESNOKR102A (DTHESNO ARRAY)
'tmpTABLE that contains 38399 rows

Dim i As Integer = 0
For i = 0 To _DTHESNOKR102A.Length - 1
Dim mxDTHESNO As String = ""
mxDTHESNO = _DTHESNOKR102A(i)

Dim xcEXPR1 As String = "WWWW = " & mxDTHESNO
Dim xcSORT1 As String = "WWWW ASC"
Dim resROWS As DataRow()
resROWS = Nothing
resROWS = tmpTABLE.Select(xcEXPR1, xcSORT1)

Dim myBALANCE As Decimal = 0.0
If resROWS.Length > 1 Then
Dim mLen As Integer = resROWS.Length - 1
Dim yumax As Integer = 0
For yumax = 0 To mLen
myBALANCE = myBALANCE + CDec(resROWS(yumax).ItemArray(16))
Next

Dim trimax As Integer = 0
For trimax = 0 To mLen
Dim tmpDTMHNO As String = ""
tmpDTMHNO = resROWS(trimax).ItemArray(20)
tmpDTMHNO = tmpDTMHNO.Remove(2, 8)

If tmpDTMHNO = "09" Then
'DELETE or REMOVE OPERATION

'Check the resROWS(i) to see if it is the
ItemArray(20)'s first 2
'characeter is "09". If ture than remove the row from
tmpTABLE.

'===========================================
tmpTABLE.Rows.Remove(resROWS(trimax))
'============================================

Else
'REPLACE OPERATION
'If the rows ItemArray(20)'s first 2 characeter is NOT
"09" then
'replace that rows ItemArray(16) value with variable
call myBALANCE
'and the replace the entire row in tmpTABLE

'===========================================
resROWS(trimax).ItemArray(16) = CStr(myBALANCE)
Dim rowE As DataRow = resROWS(trimax)
'Delete the rows first
tmpTABLE.Rows.Remove(resROWS(trimax))
'And insert the new row
tmpTABLE.Rows.Add(rowE)
'============================================
End If
Next
End If
Next

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++
My problem lies in here:
'DELETE or REMOVE OPERATION
'===========================================
tmpTABLE.Rows.Remove(resROWS(trimax))
'============================================

And

'REPLACE OPERATION
'===========================================
resROWS(trimax).ItemArray(16) = CStr(myBALANCE)
Dim rowE As DataRow = resROWS(trimax)
'Delete the rows first
tmpTABLE.Rows.Remove(resROWS(trimax))
'And insert the new row
tmpTABLE.Rows.Add(rowE)
'============================================
I couldn't find time to test it yet, but I am wondering if the line
tmpTABLE.Rows.Remove(resROWS(trimax))

realy removes the data. Plus does the replace operation is works?
Because when I test it this part:

resROWS(trimax).ItemArray(16) = CStr(myBALANCE)

It seems I cannot replace the value in column(16).

What I realy need is (as you can see from my code) does this lines is
correct that removes the row from dataTable?
tmpTABLE.Rows.Remove(resROWS(trimax))

Plus does this code replace the value for specific column in specific rows:
resROWS(trimax).ItemArray(16) = CStr(myBALANCE)

I realy appricate if I can find some answer. By the way working with data
that is constructed in AS400 nearly 25 years ago and updated nearly 10 years
ago it realy kills me to code very efficently.

Anyway I thank you for your kind understanding to reading my post.

Rgds,
GC


"Cor Ligthert [MVP]" wrote:
Niyazi,

This is the oldest use of dataprocessing.

Your datatable is an collection of datarows so you don't need an array.
However you can use a new datatable that you made distinct.

You can with that therefore clone that one and than it is something as here
in a kind of pseudo code.

myDistinctTable = MyOldTable.clone

create a field myPreviousKey value Is Nothing/Null
create one or more counterfields
Than you start going through your
For each row in myOldTable
if the previousKey not = CurrentKey
If not previousKey is Nothing/null
Add the new MyDistinctTableRow to that table
end if
Create new datarow
Set the key
end if
count into the new datarow
End For

"Niyazi" <Ni****@discussions.microsoft.com> schreef in bericht
news:D0**********************************@microsof t.com...
Hi all,

I have a dataTable that contains nearly 38400 rows.
In the dataTable consist of 3 column.
column 1 Name: MUHNO
column 2 Name: HESNO
Column 3 Name: BALANCE

Let me give you some example first:

++++++++++++++++++++++++++++++++++++++++
MUHNO HESNO BALANCE
----------------------------------------------------------------
0111621000 7371626 0
0911621000 7371626 -250
0911621100 7371626 -25
0914021000 7371626 0
++++++++++++++++++++++++++++++++++++++++

Now in my DataTable I have 16000 distinct HESNO
and total rows is 38400.

What I want is I have to use the HESNO to get all
the rows as in shown above and count the balance
that has MUHNO starting 09??????? and add into the row
that has 0111621000 and delete the rest of the
3 rows that their HESNO is same but their MUHNO
that stsrts with 09????????.

So I have to count for below 3 rows balance:

MUHNO HESNO BALANCE
----------------------------------------------
0911621000 7371626 -250
0911621100 7371626 -25
0914021000 7371626 0

which is -275 and in to the row balance column:
I guess I have to replace the value of the
BALANCE column for the row:

MUHNO HESNO BALANCE
---------------------------------------------
0111621000 7371626 0
What I did is that I run 38400 rows and I put all the HESNO
into array. Then I remove duplicated value in the array
and I sort the array.

Now I have to create a for loop for array
to check in dataTable and use replace/remove operation
that I decsribe above. But Unfortunatly I am stuck in
here.

Simply the operation have to be like in below:

Before
MUHNO HESNO BALANCE
--------------------------------------------
0111621000 7371626 0
After
MUHNO HESNO BALANCE
---------------------------------------
0111621000 7371626 -275
and delete the rows:
MUHNO HESNO BALANCE
---------------------------------------
0911621000 7371626 -250
0911621100 7371626 -25
0914021000 7371626 0.

Can anyone out there to tell me easy way to achive this
in fatsest way?

I thank you all for your kind understanding to reading my post.

Rgds,
Niyazi


May 16 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Mike | last post by:
My page populates a table with a list of names and other information from a JavaScript object. I receive changes (adds, change & delete) to that list, convert it into a JavaScript object. I do...
2
by: Viorel | last post by:
Adding new row with default values. In order to insert programmatically a new row into a database table, without direct "INSERT INTO" SQL statement, I use the well-known DataTable.NewRow,...
2
by: TB | last post by:
Before displaying the result of a table called "people" in a datagrid called "mydatagrid", I need to modify the content of a column called "moreinfo" in the in-memory datatable (but not the...
6
by: Niyazi | last post by:
Hi all, What is fastest way removing duplicated value from string array using vb.net? Here is what currently I am doing but the the array contains over 16000 items. And it just do it in 10 or...
8
by: johkar | last post by:
I would like to ensure this script is optimized (runs correctly or does not execute) for the major browsers. Will checking getElementById and getElementsByTagName accomplish this? In addition,...
2
by: Lennart | last post by:
Hi I have a DataGridView on a Form filled with data from a DataTable. When I Load this form I want to select a row in the DataGridView depending on a selected Primary Key index; How do I do...
2
by: sck10 | last post by:
Hello, When iterating through a datatable, how do you get the row that you are on? I would like to replace "i" with the row number. Thanks, sck10 int i = 0;
4
by: SirCodesALot | last post by:
Hi All, I am trying to dynamically replace a table in the dom, anyone have an idea on how to do this. here is some sample suedo code of what I want to do. var tableHTML = "<table...
29
by: shivasusan | last post by:
Hi! I can add rows with inputs to my HTML table dynamically using DOM, but I cannot remove selected rows. In fact, every row contains a Delete button. So, user selects the rows to remove, clicks...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.