Result of select query is displaying "System.Data.DataRow"

Hi

I am trying to extract data from SQL DB using select query. what i have done is

  1. Execute Query output is stored in “queryDT” a datatable.
  2. Using for each row i tried to get the row data
  3. In write line mt output is showing as “System.Data.DataRow”

Can anyone help me in solving this.

Thanks in advance,
Mrudula

row.ToString or item.Tostring will give that result.

it should be row(“ColumnName”).ToString or row(ColumnIndex).ToString

2 Likes

How to display all column values

Hello @indra,

You could iterate over the DataColumnCollection of the datatable:

Regards,
Susana

To Display (Comma saperated), use Writeline:

a) Column Names:

string.Join(",",dt.Columns.Cast(Of DataColumn)().[Select](Function(x) x.ColumnName).ToArray())

b) Column Values:

Inside for each-

string.Join(",",item.ItemArray)

3 Likes

i am getting this error

@indra
row is representing the datarow within a For Each or an individual datarow variable.

Unless you are wanting to loop through each column (which you would need 2 For Each; one for Rows and one for Columns), you should do the For Each for rows, then for column use the header name or index of the column.

Foreach row in dtSqlQuery.Rows
Write line row(“columnname1”).ToString+row(“columnname2”).ToString+row(“columnname3”).ToString+row(“columnname4”).ToString+row(“columnname5”).ToString

That is faster, however alternatively you can use your For Each Column

Foreach row in dtSqlQuery.Rows
Foreach column in dtSqlQuery.Columns
Write line row(column).ToString

Those are my thoughts. Thanks.

1 Like

Thank you so much ClaytonM it works

1 Like

hello @ClaytonM
I was also having a doubt regarding the same topic. And your answer was of great help. THANKS a lot…
but there still there is a small issue. When I used write line :"row(“columnname1”).ToString+row(“columnname2”).ToString+row(“columnname3”).ToString " it is working fine, but in the alternate method u have provided,
how will we use both “foreach row and foreach column” to get the columns?
I am getting validation errors!
I think the alternative method is better because giving the column names each time is a tiresome job…

Thanks in advance !

Hi @UnicornStark,

If you would like to take that approach, I assume your validation errors are because you are trying to use a ForEach row activity for the columns.

You will want to choose the generic ForEach activity, then in the parameters, change the TypeArgument to “DataColumn”

Hope that helps.

hello @ClaytonM

Thanks for the quick reply. I am still having the error! Cant change the variable type to “DataColumn” because the previous output is “Datatable” which is the same variable I am using in the ‘For Each Activity’ . When I use “row(colums).Tostring” , it is throwing this error : ( open strict on dissalows late binding ) !

Again thanks in advance, pardon me as If I am asking too much basic stuff… I’ve just started to learn about vb scripts !!

Hey @UnicornStark

If you are using Generic for each activity for Datacolumns in type argument then inside for each you have to pass Your_Datatable.Columns.

Regards…!!
Aksh

Yes what @aksh1yadav said lol. Also, reference Susana 's screenshot.

Here are two examples, which I think will work. There’s 2 ways you can do it using either the array if items in the row or the columns:


1 Like

@ClaytonM
Hey, Thanks a lot. It worked Perfectly!!

Thank you brother.

was executed successfully, with the second option, that of the columns!!

Hello Vaidya,

what if I need the whole row at a time in an array.
image

so for example first it gives me (JED,JHRGD, EUR, )
so that I can save it in an array and later use the values need for my process.
Thank you in advance

Normally, you would not save a DataRow to an array and just use the DataRow during your process which references a DataTable.

But, if you require an array, you can use it later in your process.

Just use the DataRow or Array as arguments if you invoke workflow or keep the variables in a scope that is outside both sequences to keep it global.

Regards.