Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

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

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
cyimxtck
Asked:
cyimxtck
  • 15
  • 10
1 Solution
 
cyimxtckAuthor Commented:
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
 
cyimxtckAuthor Commented:
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
 
CodeCruiserCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
cyimxtckAuthor Commented:
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
 
CodeCruiserCommented:
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
 
cyimxtckAuthor Commented:
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
 
CodeCruiserCommented:
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
 
cyimxtckAuthor Commented:
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
 
cyimxtckAuthor Commented:
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
 
CodeCruiserCommented:
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
 
CodeCruiserCommented:
Try the above syntax somewhere in your code to display the value of a cell.
0
 
cyimxtckAuthor Commented:
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
 
CodeCruiserCommented:
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
 
cyimxtckAuthor Commented:
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
 
cyimxtckAuthor Commented:
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
 
cyimxtckAuthor Commented:
       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
 
CodeCruiserCommented:
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
 
cyimxtckAuthor Commented:
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
 
CodeCruiserCommented:
Move this line
myDT.Rows.Add(myDR)
to under Next. So this should be between the two Next statements.
0
 
cyimxtckAuthor Commented:
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
 
cyimxtckAuthor Commented:
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
 
CodeCruiserCommented:
Move this line
Dim myDR As DataRow = myDT.NewRow()

to inside of first for loop and before the second for loop.
0
 
cyimxtckAuthor Commented:
PERFECT!!!

Thank you so much for your help.  I was up until midnight last night messing around with this thing!
0
 
cyimxtckAuthor Commented:
This was a perfect example of someone who understands that code and what you are trying to achieve.  Perfect in every way!!!
0
 
CodeCruiserCommented:
Glad the problem is solved.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 15
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now