Philip Lee Philip Lee - 2 months ago 14
Vb.net Question

DataTable updating some columns using primary key

I have a dataTable defined in a class thus:

Public Shared Positions As DataTable


When my main form loads I create the structure for the DataTable like so:

Function createTable()
' Function to create datatable structure
Dim table As New DataTable

' Define primary key
Dim keys(0) As DataColumn
Dim keyColumn As New DataColumn()
keyColumn.ColumnName = "hash"
keys(0) = keyColumn

' Define columns
table.Columns.Add(keyColumn)
table.Columns.Add("PositionX", GetType(String))
table.Columns.Add("PositionY", GetType(String))
table.Columns.Add("PositionZ", GetType(String))

return table
End Function


The values of PositionX, PositionY, and PositionZ change over time and I'd like to update them in the DataTable using the value of hash which is the primary key.

However, I just can't seem to get the syntax right. This article (https://msdn.microsoft.com/en-us/library/tat996zc.aspx) suggests that this approach would work:

Dim customerRow() As Data.DataRow
customerRow = DataSet1.Tables("Customers").Select("CustomerID = 'ALFKI'")

customerRow(0)("CompanyName") = "Updated Company Name"
customerRow(0)("City") = "Seattle"


But applying it to my code:

' Get the row containing this object
Dim resultRow() As Globals.Positions.DataRow
resultRow= Globals.Positions.Tables("Positions").Select("hash = '44DE43AB'")

resultRow(0)("PositionX") = 123456789
resultRow(0)("PositionY") = 123456789


Gives errors on the first two lines "Globals.Positions.DataRow not defined" and "Tables is not a member of DataTable".

What do I need to do in order to get the row containing the value of the primary key that I'm looking for so that I can update some of the column values ?

Answer

Perhaps have a property in the class for the DataTable, set it up, load data. Have a method to find and set values in a row e.g.

Public Class Demo1
    Public Property DataTable As DataTable
    ''' <summary>
    ''' Fire off create and load tabe
    ''' </summary>
    Public Sub New()
        CreateTable()
        LoadData()
    End Sub
    Public Sub CreateTable()
        DataTable = New DataTable
        DataTable.Columns.Add(New DataColumn With {.ColumnName = "hash", .DataType = GetType(String)})
        DataTable.Columns.Add(New DataColumn With {.ColumnName = "PositionX", .DataType = GetType(String)})
        DataTable.PrimaryKey = New DataColumn() {DataTable.Columns("hash")}

    End Sub
    Public Sub LoadData()
        DataTable.Rows.Add(New Object() {"22DE43AS"})
        DataTable.Rows.Add(New Object() {"44DE43AB"})
        DataTable.Rows.Add(New Object() {"33DE43AW"})
    End Sub
    ''' <summary>
    ''' Simple demo to find and update a row
    ''' </summary>
    ''' <param name="hash"></param>
    ''' <param name="PositionX"></param>
    ''' <returns></returns>
    Public Function SetItem(ByVal hash As String, PositionX As String) As Boolean
        Dim result As DataRow() = DataTable.Select($"hash = '{hash}'")
        If result.Count > 0 Then
            result.First.SetField(Of String)("PositionX", PositionX)
            Return True
        Else
            Return False
        End If
    End Function
End Class

Demo

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim demo As New Demo1
    If demo.SetItem("44DE43AB", "new value") Then
        MessageBox.Show("Success")
    Else
        MessageBox.Show("Failed")
    End If
End Sub

enter image description here

Comments