Update a single column in a in-memory datatable

Question

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.

Posted 20-Mar-18 1:50am

Member 12561559

Updated 21-Mar-18 1:54am

Add a Solution

Comments

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)

2 solutions

Solution 1

I think you need to say

MyRow(0).Item("Locked") = 0

Posted 20-Mar-18 3:44am

dnibbo

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2018-03-21T01:56:00

Quote:
Update a single column in a in-memory datatable
...

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

Well...
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.

VB.NET

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