CODEWITHSUNDEEP
vb.netdatatable
BobSki
BobSki

Reputation: 1552

Selecting a particular column from dataTable and updating values in all the rows

I'm currently selecting a bunch of values from SQL and load them into a dataTable. I'm trying to find that particular column and update the value in each rows.

Dim dt as DataTable
'not all code is included - but this definitely works on selecting data
sql = " Select value1, value2, value3 from TblA "
cmd = New SqlClient.SqlCommand(sql, conn)
Dim reader As SqlDataReader = cmd.ExecuteReader
dt.Load(reader)
return dt

My goal is to take the value from each row in VALUE3, and run it through a small Formatting function called - UpdateFormat. Something along the lines of....

Dim specValue As String
For Each row As DataRow In dt.Rows
      UpdateFormat(specValue ) = row.Item("Value3")
Next row

I'm hoping to update each value and update the dataTable so it has the correct values.

Upvotes: 1

Views: 2292

Answers (1)

Ňɏssa Pøngjǣrdenlarp
Ňɏssa Pøngjǣrdenlarp

Reputation: 38865

In a previous episode, we learned that you want to format the data in that column from 012345678 to 012-34-5678W where the column is VarChar and the 'W' is fixed.

You can do the formatting in the SQL:

Dim sql = <sql>
              SELECT Id, Foo, Bar, ColA, 
                    CONCAT(SUBSTRING([Value3],1,3), 
                            '-', 
                        SUBSTRING([Value3],4,2), 
                        '-', 
                        SUBSTRING([Value3],6,4), 
                        'W' ) AS NewV3Value 
                FROM 
                    dbo.TableBob
          </sql>.Value

Doing so, the column is delivered to you already formatted. Depending on your DB flavor, the exact syntax may vary.

The <sql>...</sql>.Value is just an XML literal which allows you to format long or complex queries in such a way that it makes it easier for you to read.

To do it yourself in the table. As the format is trivial to apply, you really dont need a method to do it: 

Dim tmp As String
For Each row As DataRow In dt.Rows
    tmp = row.Field(Of String)("Value3")
    row("Value3") = String.Format("{0}-{1}-{2}W", 
                                     tmp.Substring(0, 3),
                                     tmp.Substring(3, 2),
                                     tmp.Substring(5, 4))
Next row

The tmp var is just to shorten the code.

Upvotes: 1

Related Questions