• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5276
  • Last Modified:

VB.NET Updating a Data Table

I am writing a vb.net app using vs. 2003(.net1.1) I have a data table that I am populating based on a query. After the Data table has been filled I have another loop that iterates through each column of the data table and compares the name to a select case statement where it will then check the value. If it matches I need to be able to run updates on the data table to only a single item. Can someone help me with the code to run an update on the data table?
2 Solutions
jfsedlar3rdAuthor Commented:
This is what i have so far:

Dim ColumnName As String

            For i = 0 To dt.Rows.Count
                For Each col In dt.Columns
                    ColumnName = col.ColumnName.ToString

                    Select Case ColumnName
                        Case "Middle Initial"
                           'Perform Update here
                    End Select
                Next col
Bob LearnedCommented:
Try this:

            For Each dr As DataRow in dt.Rows
                For Each dc As DataColumn In dt.Columns

                    Select Case col.ColumnName
                        Case "Middle Initial"
                           ' Use just the first letter of the middle initial.
                          dr(dc) = dr(dc).ToString().Substring(0, 1)
                    End Select
                Next dc
            Next dr

There isn't any type of blanket UPDATE statement for a datatable. Your only option is to either loop through each row in your datatable and then update the specific field you're interested in...

'loop through each row
For Each row As DataRow In MyDataTable.Rows()
     If Convert.ToString(row("MyField1")) = "MyOldValue" Then
          row("MyField1") = "MyNewValue"
     End If

Or else add a primary key to the datatable (if you don't yet have one) using the PrimaryKeys property (http://msdn2.microsoft.com/en-us/library/system.data.datatable.primarykey(VS.71).aspx) and use the Find() method of the Rows property (http://msdn2.microsoft.com/en-us/library/system.data.datarowcollection.find(VS.71).aspx) to find the row you want to update by primary key.

'set an existing column as the primary key
Dim keys(1) As DataColumn
keys(0) = MyDataTable.Columns("MyPrimaryKeyColumn")
MyDataTable.PrimaryKeys = keys

'find a row by primary key
Dim row As DataRow = MyDataTable.Rows.Find("MyPrimaryKey")
If Not row Is Nothing Then
     row("MyField1") = "MyNewValue"
End If

Featured Post

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!

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