How to store user data from database into Variable/Array

Hi Friends,

In my workflow

  1. I am using Execute Query activity to run a query on SQL database to get data. I am storing the output into DataTable successfully.
  2. Using For Each Row activity I am looping through the DataTable successfully.
  3. Using Get Row Item activity I am getting the values of particular column (SnippEventID) and storing the output into a Variable with General Value (Variable Type)

If I use Message Box activity in For Each Row I am able to print the output Variable one by one successfully.

But If I want to store all the four SnippEventID values into a Array. How can I do that?

Can I use Add Data Row activity and Build Data Table activity to store all the Variable?

Hi Bhaskar,

Are you connecting to Oracle Database? I am currently having trouble connecting to Oracle database.

Any assistance is greatly appreciated.

Thanks
Vinod

I am connecting to SQL Database

Check out this link for Oracle connection strings

https://www.connectionstrings.com/oracle/

Hope the below links help you.

Hi Friend,

@ClaytonM

  1. I used Build Data Table activity and Add Data Row activity to store all the Variable.
  2. Then I used Output Data Table activity to out all the Variables.

But I can see Column 1 field in my output. I am trying to use Skip method but I am getting below error message.

Error Screenshot
image

Output Screenshot:
image

If I removed the Column 1 in Build Data Table activity I am getting the below error message.

image

Can anyone suggest?

Hello.

So do I understand this correctly, you want to store all your values in a data table, then return them back as an array? or something like that?

You don’t need to use the Output Data Table for this, and actually it just makes it more complicated.

All you need to do is query your data table that you built to an array of the items. You can use Select for this. To join all the items to one string, then use String.Join()

Here is an example code:

String.Join(System.Environment.Newline, datatablevariable.AsEnumerable.Select(Function(x) x(0).ToString).ToArray )

You can put that in the Write Line and it will list all the items in column 1 of your data table.

Regards.

Hi @ClaytonM

yes I am able to store all my values into a data table and I am trying to return them back one by one for every iteration.

I tried your example code buy after datatable I cant see AsEnumerable method. Please find the screenshot below.

You might be on an older version of Studio. Just type it in and it will work. I think .AsEnumerable was added to the list in version 2018 or 2018.2

I am using Studio 2018.3.1 Community Edition

Try typing it out anyway even though the method doesn’t get listed. Let me know if get a Validation error or anything.

I am getting the below error .

image

Try changing the name of the variable from datatable to something else.
If that doesn’t fix it, can you upload the work flow and I can check it on my end.

Thanks.

No its not working.

I removed the database details. Please add your SQL database details and run the workflow.

Sequence1.xaml (11.4 KB)

Ok, I don’t know what’s going on, but I remember seeing this issue before where there is like this bug sometimes and it thinks it’s not a member. I saw the validation error, so I deleted the “.AsEnumerable”, pressed ok to update it, then edited it again and retyped it and error went away. I don’t know if that will help, but here is the file which may let you copy it to yours.
Sequence1.xaml (7.6 KB)

Regards.

It Worked. After pasting the your code I gave space at the end of the code and the error gone. When I ran the workflow I got output :frowning:

image

But now how can I return one by one value out from the DTOutPut and run my rest of the script. I am confused :confused:

Apologies if I misunderstood :thinking::
If you want to output each value one at a time as you use the Add Data Row activity, then do so right after the Add Data Row inside the loop.

However, if you want to output each item one at a time outside that loop, then you need to use a For Each instead of a String.Join

For each row in DTOutPut
   WriteLine row(0).ToString

If that doesn’t answer your question, can you please explain what you are wanting to do exactly again so maybe I undersrand better. Thanks

Hi @ClaytonM

In my workflow

  1. I am using Execute Query activity to run a query on SQL database to get data. I am storing the output into DataTable successfully.
  2. Using For Each Row activity I am looping through the DataTable successfully.
  3. Using Get Row Item activity I am getting the values of particular column (SnippEventID) and storing the output into a Variable with General Value (Variable Type)

If I use Message Box activity in For Each Row I am able to print the output Variable one by one successfully.

You code is give me output of all the four SnippEventID’s as shown below

image

But If I want to store all the four SnippEventID values into a Array of strings or as a Data Table how can i do that.

There are 2 ways to store your values like that, to use a List variable and use the “Add to collection” activity or use a data table with “Add Data Row”. So how you are trying to do this is fine.

It sounds like you want to output each value one by one as you add it to the data table. You can do this inside your “For each row in GetQueryOutput” right next to the Add Data Row, like Write Line SnippEventID, or whatever value is being stored in the data table.

Regards.