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


#1

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


#2

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

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


#3

How to display all column values


#4

Hello @indra,

You could iterate over the DataColumnCollection of the datatable:

Regards,
Susana


#5

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)


#6

i am getting this error


#7

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


#8

Thank you so much ClaytonM it works


#9

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 !


#10

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.


#11

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 !!


#12

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


#13

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:



#14

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