VB.NET Updating a Data Table

Posted on 2007-07-24
Last Modified: 2009-02-26
I am writing a 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?
Question by:jfsedlar3rd

    Author Comment

    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
    LVL 96

    Accepted Solution

    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

    LVL 6

    Assisted Solution

    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 ( and use the Find() method of the Rows property ( 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

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    729 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

    19 Experts available now in Live!

    Get 1:1 Help Now