Syntax help (non empty rows) with Datatable.select

studio

#1

Hi, I would like to count number of rows in Datatable using Select Method.
My syntax currently looks like this:

Count = datatable.Select("[ColumnName1] = ‘"+variable1+"’" and “[ColumnName2]” isnot Nothing).count

There was no validation errors until I added the part after “and”, how do I write the syntax to select rows where a specific column is not empty?


#2

Hi @inyourgravity,
You can use like this

dtExcel.Select(“Condition”).CopyToDataTable().Rows.Count

or

Declare DataRow Array named “drArr”
drArr=dtExcel.Select(“Condition”)
Count=drArr.Length

Regards
Balamurugan


#3

But how do I write the “condition”?
the condition i input in the select filters is having validation error, not the count part.


#4

Count = datatable.Select("[ColumnName1] = ‘"+variable1+"’" and “[ColumnName2]” isnot Nothing).CopyToDataTable().Rows.Count

Regards
Balamurugan


#5

still getting the error “option strict on disallows implicit conversations from ‘string’ to ‘boolean’”


#6

hi @inyourgravity,

the Count variable should be int32

image

Regards
Balamurugan


#7

@inyourgravity
intCount= datatable.Select("[ColumnName1] = ‘"+variable1+"’and [ColumnName2] <>’"+string.Empty+"’").count

Regards,
Mahesh


#8

already did so.

and “[ColumnName2]” isnot Nothing

this part is giving the error. once it is removed, there isnt any error.


#9

‘string’ is just an empty string variable?


#10

@inyourgravity

Yes string.Empty means empty value


#11

Hi @inyourgravity

As @MAHESH1 is also right to apply the

Regards
Balamurugan


#12

Hi @inyourgravity just another way to solve this problem (and easier) its:

datatable.Rows.Cast(of DataRow).Select(function(row) row(yourColumnIndex).tostring=variable1 AndAlso not string.isnullorwhitespace(row(yourOtherColumnIndex).tostring).Count