Solved

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

Posted on 2009-05-11
25
219 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
  • 15
  • 10
25 Comments
 

Author Comment

by:cyimxtck
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:cyimxtck
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Try the above syntax somewhere in your code to display the value of a cell.
0
 

Author Comment

by:cyimxtck
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
       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
Comment Utility
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
Comment Utility
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
Comment Utility
Move this line
myDT.Rows.Add(myDR)
to under Next. So this should be between the two Next statements.
0
 

Author Comment

by:cyimxtck
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Glad the problem is solved.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
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 this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now