Click here to Skip to main content
15,070,511 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have an in-memory datatable that has lots of columns that I am drawing in from different tables in a SQL database. I could do it via inner or left joins but the overhead is tremendous when someone doesnt want all of the columns so I am using a datatable (no dataset involved). All works wonderfully until I want to update one of the columns.

I have googled keywords for my question as well as looked at code project but I cant see what I am after (unless it is buried among other similar questions).

The code I am using is below, borrowed from Microsoft's website, though it does expect it to have a dataset, skipping that part, the IDE is saying nothing is wrong until I actually run it.

What I have tried:

Dim MyRow() As Data.DataRow
               MyRow = Facility_Table.Select("FacilityNameRef=123")
               MyRow(0)("Locked") = 0

(the reference put here is just a dummy but I know the reference exists in the datatable once it is populated and I can see that from a datagridview that I have bound to the datatable.
Updated 21-Mar-18 1:54am
CHill60 20-Mar-18 8:09am
"but the overhead is tremendous when someone doesnt want all of the columns" - then don't return all of the columns is my first thought.
You say nothing is wrong until you run it but you haven't said what the problem actually is. Does MyRow actually contain anything and shouldn't it be MyRow("Locked") = 0 (assuming you have a column called Locked)
Member 12561559 20-Mar-18 8:26am
using my code i get: object reference not set to an instance of an object - i know the object exists as ive populated it. though i'm probably being dull somewhere along the line.

I am trying to update the datatable - from a different form,
the facility_table datatable is declared on one form as so:
Friend Facility_Table As New DataTable

and im updating it with (for example):

Dim MyRow() As Data.DataRow
MyRow = Form2.Facility_Table.Select("FacilityNameRef=123")
MyRow(0)("Locked") = 0

and using just MyRow("Locked") = 0 as per your recommendation, i get a red underline in the IDE saying "integer cannot be converted to datarow)

I think you need to say
MyRow(0).Item("Locked") = 0
Update a single column in a in-memory datatable
Dim MyRow() As Data.DataRow
MyRow = Facility_Table.Select("FacilityNameRef=123")
MyRow(0)("Locked") = 0

You can't update single column (all rows in that column) this way, because DataTable.Select method[^] returns an array of rows. You have to loop through the rows in collection to be able to change their values.

Dim MyRows() As Data.DataRow = Facility_Table.Select("FacilityNameRef=123")
For Each r As Data.DataRow in MyRows
    r("ColumnName") = 0
    'r.Item("ColumnName") = 0

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900