Solved

How to loop through a datatable and access items(i)

Posted on 2009-05-11
25
255 Views
Last Modified: 2012-05-06
I need the ability to loop through a datatable but every value needs to be evaluated in each rows column.  I do not know how many columns or rows so it needs to be index based.  The bigger picture is that I am trying to build a function that takes in an encrypted datatable and returns an unencrypted datatable.

Everything is in place for all the encryption/decryption but I cannot seem to access each value.  I can access a row or a column but not the "item(i)" that needs looped through.

Each row has its own IV and Key coming from the database so each row needs evaluated to extract the IV and Key so that the row can then use the decrypt function to decrypt the "item(i)" I cannot seem to access.

Any help would be greatly appreciated!

Thanks in advance,

B
Public Shared Function SQLStoredProcDT(ByVal DT As DataTable) As DataTable
 
            Dim rows As Integer = DT.Rows.Count
            Dim cols As Integer = DT.Columns.Count
 
            Dim myDT As New DataTable()
 
            Try
 
                Dim dc As DataColumn
                For Each dc In DT.Columns
 
                    myDT.Columns.Add(dc.ColumnName)
 
                Next
 
                Dim dr As DataRow
                For Each dr In DT.Rows
 
                    For Each col As DataColumn In DT.Columns
 
                        Dim IV As String = DT.Columns("IV").ToString
                        Dim EKey As String = DT.Columns("EKey").ToString
                        Dim rij As EncryptionAlgorithmType = EncryptionAlgorithmType.Rijndael
 
                        For i As Integer = DT.Columns.Count
 
                            DT.Rows.Add(DecryptString(dr(i - 1).Item(i - 1), rij, EKey, IV))
 
                        Next i
 
                    Next
 
                    Next
 
                Return myDT
 
            Catch ex As Exception
                Throw ex
 
            End Try
 
        End Function

Open in new window

0
Comment
Question by:cyimxtck
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 10
25 Comments
 

Author Comment

by:cyimxtck
ID: 24359425
There is a mistake in the code I have placed on there...

DT.Rows.Add(DecryptString(dr(i - 1).Item(i - 1), rij, EKey, IV))


the above line should be

myDT.Rows.Add(DecryptString(dr(i - 1).Item(i - 1), rij, EKey, IV))

sorry.
0
 

Author Comment

by:cyimxtck
ID: 24359828
Here is where I am at this point...

       Public Shared Function SQLStoredProcDT(ByVal DT As DataTable) As DataTable

            Dim myDT As New DataTable()

            Dim i As Integer = DT.Columns.Count
            Dim x As Integer = DT.Rows.Count

            Try

                Dim dc As DataColumn
                For Each dc In DT.Columns

                    myDT.Columns.Add(dc.ColumnName)

                Next

                Dim dr As DataRow

                For Each dr In DT.Rows

                    Dim IV As String = DT.Rows(x - 1).Item("IV").ToString
                    Dim EKey As String = DT.Rows(x - 1).Item("EKey").ToString

                    For Each col As DataColumn In DT.Columns

                        Dim rij As EncryptionAlgorithmType = EncryptionAlgorithmType.Rijndael

                        Dim z As Integer = DT.Columns.Count - 3

                        myDT.Rows.Add(DecryptString(DT.Columns.Item(z).ToString, rij, EKey, IV))

                        'DT.Rows.Add(DecryptString(dr(i - 1), rij, Ekey, IV))
                        'DT.Rows.Item(0).Item(i - 1) = (DecryptString(dr(0), rij, EKey, IV))

                        z = z - 1
                        x = x - 1

                        IV = Nothing
                        EKey = Nothing

                    Next

                Next

                myDT.Columns.Remove("IV")
                myDT.Columns.Remove("EKey")

                Return myDT

            Catch ex As Exception
                Throw ex

            End Try

        End Function
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24362785
You normally run such loop this way
For each Row in DT.Rows
    For Each Col in Row
        messagebox.show(Col.Caption)
    Next
Next

Open in new window

0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

Author Comment

by:cyimxtck
ID: 24364192
The issue with that not being index based is that the "header row" comes back to get decrypted and it was never obviously encrypted since it is the column names.  This has to be index based.

Even if someone could show me how to alter each value in the datatable to add a "1" to the end of every value in every column and row except the header row that would be superb.

Thanks for the help,

B
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24364342
Try this instead
For each Row in DT.Rows
    For i as integer = 0 to Row.columns.count - 1
        messagebox.show(row.item(i))
    Next
Next

Open in new window

0
 

Author Comment

by:cyimxtck
ID: 24364780
this shows me system.data.row instead of the data and then errors out that there is no row at position one?


 For Each dr In DT.Rows

                    Dim IV As String = DT.Rows(x - 1).Item(i - 2).ToString
                    Dim EKey As String = DT.Rows(x - 1).Item(i - 3).ToString

                    'For Each col As DataColumn In DT.Columns
                    For t As Integer = 0 To DT.Columns.Count - 1

                        Dim rij As EncryptionAlgorithmType = EncryptionAlgorithmType.Rijndael

                        Dim z As Integer = DT.Columns.Count - 3
                        'Dim val As String = DT.Columns.Item.ToString

                        Dim myDR As DataRow = myDT.NewRow()

                        'DT.Rows(0).Delete()
                        'DT.AcceptChanges()

                        MessageBox.Show(DT.Rows.Item(t).ToString)

                        ' Then add the new row to the collection.
                        'myDR("NameFirst") = DecryptString(DT.Columns.Item(i - 5).ToString, rij, EKey, IV)
                        'myDR("NameMiddle") = DecryptString(DT.Columns.Item(i - 4).ToString, rij, EKey, IV)
                        'myDR("NameLast") = DecryptString(DT.Columns.Item(i - 3).ToString, rij, EKey, IV)
                        'myDT.Rows.Add(myDR)

                        'Item(z) = DecryptString(val(i), rij, EKey, IV))
                        'myDT.Rows.Add(DecryptString(DT.Columns.Item(z).ToString, rij, EKey, IV))

                        'DT.Rows.Add(DecryptString(dr(i - 1), rij, Ekey, IV))
                        'DT.Rows.Item(0).Item(i - 1) = (DecryptString(dr(0), rij, EKey, IV))

                        z = z - 1
                        x = x - 1

                        IV = Nothing
                        EKey = Nothing

                    Next

                Next

                myDT.Columns.Remove("IV")
                myDT.Columns.Remove("EKey")

                Return myDT
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24365251
Dim IV As String = DT.Rows(x - 1).Item(i - 2).ToString
                    Dim EKey As String = DT.Rows(x - 1).Item(i - 3).ToString

What is i and x? What are intial values of these?
0
 

Author Comment

by:cyimxtck
ID: 24365307
Now all I get in the message box are the column names and not the values of the columns. Here is the entire method:

        Public Shared Function SQLStoredProcDT(ByVal DT As DataTable) As DataTable

            Dim myDT As New DataTable()

            Dim i As Integer = DT.Columns.Count
            'Dim x As Integer = DT.Rows.Count

            Try

                Dim dc As DataColumn

                For Each dc In DT.Columns

                    myDT.Columns.Add(dc.ColumnName)

                Next

                'Dim dr As DataRow

                'x = DT.Rows.Count

                'For Each dr In DT.Rows
                For x As Integer = 0 To DT.Rows.Count

                    'Dim IV As String = DT.Rows(x).Item(i).ToString
                    'Dim EKey As String = DT.Rows(x).Item(i).ToString

                    'For Each col As DataColumn In DT.Columns
                    For t As Integer = 0 To DT.Columns.Count - 3

                        'Dim rij As EncryptionAlgorithmType = EncryptionAlgorithmType.Rijndael

                        'Dim z As Integer = DT.Columns.Count - 3
                        'Dim val As String = DT.Columns.Item.ToString

                        'Dim myDR As DataRow = myDT.NewRow()

                        'DT.Rows(0).Delete()
                        'DT.AcceptChanges()

                        MessageBox.Show(DT.Columns.Item(t).ToString)

                        ' Then add the new row to the collection.
                        'myDR("NameFirst") = DecryptString(DT.Columns.Item(i - 5).ToString, rij, EKey, IV)
                        'myDR("NameMiddle") = DecryptString(DT.Columns.Item(i - 4).ToString, rij, EKey, IV)
                        'myDR("NameLast") = DecryptString(DT.Columns.Item(i - 3).ToString, rij, EKey, IV)
                        'myDT.Rows.Add(myDR)

                        'Item(z) = DecryptString(val(i), rij, EKey, IV))
                        'myDT.Rows.Add(DecryptString(DT.Columns.Item(z).ToString, rij, EKey, IV))

                        'DT.Rows.Add(DecryptString(dr(i - 1), rij, Ekey, IV))
                        'DT.Rows.Item(0).Item(i - 1) = (DecryptString(dr(0), rij, EKey, IV))



                        'IV = Nothing
                        'EKey = Nothing

                    Next
                    x = x - 1
                Next

                myDT.Columns.Remove("IV")
                myDT.Columns.Remove("EKey")

                Return myDT

            Catch ex As Exception
                Throw ex

            End Try

        End Function
0
 

Author Comment

by:cyimxtck
ID: 24365347
I am trying to dumb it down so that I can move forward so you will see most of the code commented out.

This is so hard and it seems so trivial I have no idea why I am having so much trouble with this...

Sorry if it seems I am thick.  :)
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24365358
I can confirm that i successfully use the following syntax in my programs to read columns values

dTable.Rows(i).Item(0) to get first column's value
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24365408
Try the above syntax somewhere in your code to display the value of a cell.
0
 

Author Comment

by:cyimxtck
ID: 24365475
That works!  I have two rows in the database and it gives me the values for the rows.

NOW....how can I get those values into another datatable so that I can return that datatable?

Here is the code now:

        Public Shared Function SQLStoredProcDT(ByVal DT As DataTable) As DataTable

            Dim myDT As New DataTable()

            Dim i As Integer = DT.Columns.Count
            'Dim x As Integer = DT.Rows.Count

            Try

                Dim dc As DataColumn

                For Each dc In DT.Columns

                    myDT.Columns.Add(dc.ColumnName)

                Next

                'Dim dr As DataRow

                'x = DT.Rows.Count

                'For Each dr In DT.Rows
                For x As Integer = 0 To DT.Rows.Count

                    'Dim IV As String = DT.Rows(x).Item(i).ToString
                    'Dim EKey As String = DT.Rows(x).Item(i).ToString

                    'For Each col As DataColumn In DT.Columns
                    For t As Integer = 0 To DT.Columns.Count - 3

                        'Dim rij As EncryptionAlgorithmType = EncryptionAlgorithmType.Rijndael

                        'Dim z As Integer = DT.Columns.Count - 3
                        'Dim val As String = DT.Columns.Item.ToString

                        'Dim myDR As DataRow = myDT.NewRow()

                        'DT.Rows(0).Delete()
                        'DT.AcceptChanges()

                        'MessageBox.Show(DT.Columns.Item(t).ToString)

                        MessageBox.Show(DT.Rows(x).Item(t).ToString)
                        ' Then add the new row to the collection.
                        'myDR("NameFirst") = DecryptString(DT.Columns.Item(i - 5).ToString, rij, EKey, IV)
                        'myDR("NameMiddle") = DecryptString(DT.Columns.Item(i - 4).ToString, rij, EKey, IV)
                        'myDR("NameLast") = DecryptString(DT.Columns.Item(i - 3).ToString, rij, EKey, IV)
                        'myDT.Rows.Add(myDR)

                        'Item(z) = DecryptString(val(i), rij, EKey, IV))
                        'myDT.Rows.Add(DecryptString(DT.Columns.Item(z).ToString, rij, EKey, IV))

                        'DT.Rows.Add(DecryptString(dr(i - 1), rij, Ekey, IV))
                        'DT.Rows.Item(0).Item(i - 1) = (DecryptString(dr(0), rij, EKey, IV))



                        'IV = Nothing
                        'EKey = Nothing

                    Next

                    x = x - 1

                Next

                myDT.Columns.Remove("IV")
                myDT.Columns.Remove("EKey")

                Return myDT

            Catch ex As Exception
                Throw ex

            End Try

        End Function
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24365666
The code should work fine. I dont understand why its not. What output do you get in the messagebox?

MessageBox.Show(DT.Rows(x).Item(t).ToString)
0
 

Author Comment

by:cyimxtck
ID: 24365691
That does work like I said above - great work!  But now I need to get those values into the "new" datatable so that I can return those values.

Thanks for all your help on this!
0
 

Author Comment

by:cyimxtck
ID: 24365845
Here is the latest code that works thanks to you but I cannot see how to get independent values into a datatable?  In this case there are several columns but the columns will change and you will have no idea what they are (unless you read them from the DT)  But since they change how would you do that>?

Thanks again!
0
 

Author Comment

by:cyimxtck
ID: 24365846
       Public Shared Function SQLStoredProcDT(ByVal DT As DataTable) As DataTable

            Dim myDT As New DataTable()

            Dim i As Integer = DT.Columns.Count
            'Dim x As Integer = DT.Rows.Count

            Try

                Dim dc As DataColumn

                For Each dc In DT.Columns

                    myDT.Columns.Add(dc.ColumnName)

                Next

                'Dim dr As DataRow

                'x = DT.Rows.Count

                'For Each dr In DT.Rows
                For x As Integer = 0 To DT.Rows.Count - 1

                    'For Each col As DataColumn In DT.Columns
                    For t As Integer = 0 To DT.Columns.Count - 3

                        Dim IV As String = DT.Rows(x).Item("IV").ToString
                        Dim EKey As String = DT.Rows(x).Item("EKey").ToString

                        Dim rij As EncryptionAlgorithmType = EncryptionAlgorithmType.Rijndael

                        'Dim z As Integer = DT.Columns.Count - 3
                        'Dim val As String = DT.Columns.Item.ToString

                        'Dim myDR As DataRow = myDT.NewRow()

                        'DT.Rows(0).Delete()
                        'DT.AcceptChanges()

                        'MessageBox.Show(DT.Columns.Item(t).ToString)

                        MessageBox.Show(DecryptString(DT.Rows(x).Item(t).ToString, rij, EKey, IV))

                        ' Then add the new row to the collection.
                        'myDR("NameFirst") = DecryptString(DT.Columns.Item(i - 5).ToString, rij, EKey, IV)
                        'myDR("NameMiddle") = DecryptString(DT.Columns.Item(i - 4).ToString, rij, EKey, IV)
                        'myDR("NameLast") = DecryptString(DT.Columns.Item(i - 3).ToString, rij, EKey, IV)
                        'myDT.Rows.Add(myDR)

                        'Item(z) = DecryptString(val(i), rij, EKey, IV))
                        'myDT.Rows.Add(DecryptString(DT.Columns.Item(z).ToString, rij, EKey, IV))

                        'DT.Rows.Add(DecryptString(dr(i - 1), rij, Ekey, IV))
                        'DT.Rows.Item(0).Item(i - 1) = (DecryptString(dr(0), rij, EKey, IV))

                        IV = Nothing
                        EKey = Nothing

                    Next

                Next

                myDT.Columns.Remove("IV")
                myDT.Columns.Remove("EKey")

                Return myDT

            Catch ex As Exception
                Throw ex

            End Try

        End Function
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24365898
You will do something like

Dim myDR As DataRow = myDT.NewRow()
myDR(t) = DecryptString(DT.Rows(x).Item(t).ToString, rij, EKey, IV)


Then after all the columns are done (outside inner loop), use this
MyDT.Rows.Add(myDR)

you do not need to remove columns for the time being.
0
 

Author Comment

by:cyimxtck
ID: 24366051
It says myDR already belongs to this table in the error message>??

        Public Shared Function SQLStoredProcDT(ByVal DT As DataTable) As DataTable

            Dim myDT As New DataTable()

            Try

                Dim dc As DataColumn

                For Each dc In DT.Columns

                    myDT.Columns.Add(dc.ColumnName)

                Next
                Dim myDR As DataRow = myDT.NewRow()

                For x As Integer = 0 To DT.Rows.Count - 1

                    For t As Integer = 0 To DT.Columns.Count - 3

                        Dim IV As String = DT.Rows(x).Item("IV").ToString
                        Dim EKey As String = DT.Rows(x).Item("EKey").ToString

                        Dim rij As EncryptionAlgorithmType = EncryptionAlgorithmType.Rijndael

                        myDR(t) = DecryptString(DT.Rows(x).Item(t).ToString, rij, EKey, IV)

                        'MessageBox.Show(DecryptString(DT.Rows(x).Item(t).ToString, rij, EKey, IV))

                        IV = Nothing
                        EKey = Nothing

                        myDT.Rows.Add(myDR)

                    Next

                Next

                myDT.Columns.Remove("IV")
                myDT.Columns.Remove("EKey")

                Return myDT

            Catch ex As Exception
                Throw ex

            End Try

        End Function
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24366112
Move this line
myDT.Rows.Add(myDR)
to under Next. So this should be between the two Next statements.
0
 

Author Comment

by:cyimxtck
ID: 24366161
When I do that it errors out saying this row already belongs to that table.  If I move it outside both next's it only gives me the last row?
0
 

Author Comment

by:cyimxtck
ID: 24366195
If I do this:

                    Next

                    myDT.Rows.Add(myDR(x))

                Next

it only give me the first name column populated and it isn't accurate.  It is a first name of the first row and the middle initial of the second row and middle name and last name are blank>>?
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 24366251
Move this line
Dim myDR As DataRow = myDT.NewRow()

to inside of first for loop and before the second for loop.
0
 

Author Comment

by:cyimxtck
ID: 24366292
PERFECT!!!

Thank you so much for your help.  I was up until midnight last night messing around with this thing!
0
 

Author Closing Comment

by:cyimxtck
ID: 31580339
This was a perfect example of someone who understands that code and what you are trying to achieve.  Perfect in every way!!!
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24366344
Glad the problem is solved.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question