Solved

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

Posted on 2009-05-11
25
253 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

739 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