datatable select rows

Question

I am using a datatable created by program. In this datatable i want to insert values in some specified columns.

Initially I am inserting primary key values leaving remaining columns null, when I am querying datatable with recently inserted value in Primary column to update same row, I am facing error Missing operand after ID operator

Can any one tell me the exact issue.

I am trying following code:

 dt.Rows.Add(1);
 int insertedValue = 1;
 DataRow[] dr = dt.Select("ID = '" + insertedValue.toString() + "'");

And the table structure after entring primary value is as follows.

ID    Volumn1    Volumn2    volumn3
--------------------------------------
1

If at all an option, you may want to take a look at [Linq](http://stackoverflow.com/questions/10855/linq-query-on-a-datatable). — Roman, Mar 03 '12 at 07:02

Tim M. · Accepted Answer · 2012-03-03T07:16:35.950

19

You can do this more cleanly with LINQ and make this a strongly typed operation.

Something like:

dt.Rows.Add(1);
int insertedValue = 1;
var result = 
        dt.AsEnumerable().Where( dr => dr.Field<int>( "ID" ) == insertedValue );

Working example:

DataTable dt = new DataTable();
dt.Columns.Add( "ID", typeof( int ) );
dt.Rows.Add( 1 );

var result = dt.AsEnumerable().Where( dr => dr.Field<int>( "ID" ) == 1 );

edited Mar 03 '12 at 07:16

answered Mar 03 '12 at 07:00

Tim M.

53,671
14
120
163

I have tried above in my code, this time i can see the var result properties where in message property it showing "Specified cast is not valid" @Tim Medora – Pramod Mar 03 '12 at 07:09
@Pramod - see my updated example. Your columns need to be strongly-typed and the Where() method takes a generic parameter of the same type. – Tim M. Mar 03 '12 at 07:17
`result` contains an enumerable collection of data rows. You can use the `First()` method to get the first result. – Tim M. Mar 03 '12 at 07:27
1

But after using FIRST() i would have lost all the column names form source table...??? i mean i cant update datatables current row for selected id for specified column(dynamic) @ Tim Medora. – Pramod Mar 03 '12 at 07:42
`First()` will give you a DataRow back...same as `DataTable.Select()` – Tim M. Mar 03 '12 at 08:03

score 6 · Answer 2 · answered Dec 21 '15 at 15:52

6

You can simply format the selection string as shown below:

    DataRow[] dr = dt.Select(string.Format("ID ='{0}' ", insertedValue));

Feel free to let me know if this works for you.. Thanks

answered Dec 21 '15 at 15:52

Sunday Efeh

81
1
1

2

I think this is the best solution – Esi Aug 14 '17 at 07:32
Note: This works great in conjunction with: foreach (DataRow r in dr) {... loops. – Jeff Nov 27 '18 at 02:42

score 2 · Answer 3 · answered Mar 03 '12 at 06:57

2

You do not need ' ' in your filter.

I think this should work:

DataRow[] dr = dt.Select("ID = " + insertedValue.toString());

answered Mar 03 '12 at 06:57

gideon

19,329
11
72
113

score 1 · Answer 4 · answered Nov 21 '13 at 22:50

1

By the way, reference System.Data.DataSetExtensions

answered Nov 21 '13 at 22:50

Sean

282
3
11

score 0 · Answer 5 · answered Jun 23 '17 at 11:19

If you are looking for a specific row and your datatable has a primary key you could use the Find method and target the primary key which would return just the row you want rather than an array:

DataRow foundRow = dt.Rows.Find([INSERT SEARCH PARAMETER HERE]);
if(foundRow != null)
{
    TO SET A STRING EQUAL TO A FOUND VALUE:
    string str = foundRow["COLUMN NAME / INDEX];

    OR IF YOU ARE INSERTING A VALUE YOU CAN USE IT LIKE THIS:
    foundRow["COLUMN NAME / INDEX"] = NEW VALUE;
}

score 0 · Answer 6 · answered May 24 '19 at 13:48

0

select column of row

dt.Rows[0].Field<string>("MyColumnName")

answered May 24 '19 at 13:48

Alparslan ŞEN

73
3

datatable select rows

6 Answers6