Need Idea and help for access the DB and Xaml file

[RaviDevaraj]

Hi Team,
Hi @ClaytonM

I need help and guideliness from you i have an Xaml file to do certain task from excel we take Url,username,Password and trigger the task like login and enter the date from and date to search the report and save in one location.

all detaila i am getting from excel sheet and Now i need to get the all details from my Database table which i was created in my database get the details from there like url,username,password,start date,end date

In my XAML File i have called the {row(Link).ToString ,row(username).ToString,row(password).ToString, } syntax: row (ExcelColumnName).ToString
Now how those values and all handled by DataBase ?

How it is possible from DB Table

Can you pls help me

Thanks
RaviDevaraj

[ClaytonM]

Hello.

I am not exactly an expert using Databases, so I probably can’t help you that much.

If you search your activities for “Database” you’ll see some activities you can use.
Typically, you will use the “Connect”, “Execute Query”, then “Disconnect”

You will want to make sure you can connect to the database first. Once that is working then you can use the “Execute Query”.

In the Execute Query, you will use query syntax, so you might need to some research on that. Basically, the query code will be in a string, so you would concatenate your values from the excel file to place them in the query string.

example query string might look like:

" SELECT A.USERNAME,A.PASSWORD,A.STARTDATE,A.ENDDATE" _
+" FROM tablename+" A" _
+" WHERE" _
+" ((A.USERNAME ='"+row("Username").tostring.trim+"') AND" _
+" (A.PASSWORD ='"+row("Password").tostring.trim+"'))"

And, it’s always good to use Disconnect after your querys are all done.

Hope this drives you in the right direction. Sorry, I can’t provide more help than that.

Good luck!

[RaviDevaraj]

@ClaytonM

You will want to make sure you can connect to the database first

That has been Done !! Simple thing only and then i gave test connection is was “Success” only.

@ClaytonM

you can use the “Execute Query”.

Don’t have any idea about regarding this @ClaytonM

@ClaytonM

In the Execute Query, you will use query syntax

oh ok As per your guide lines i will write query , and get the values then how we need to do the rest of the things in need.

[RaviDevaraj]

Hi @ClaytonM

In Execute query i have type my entire query throws an error ?

see the screenshot for your references

Any idea to overcome this ?

[ClaytonM]

Where it says " FROM Master_data …", you need to place the “A” and “Where” inside the string.

" FROM Master_data A Where"+" ((A.Serial_No ='"+

That should get rid of the green and validation error.
Let me know if that helps.

Also, keep in mind that after the “Where” you should only compare columns that are in your Excel file so it can pull in the missing fields from the database. You are including every column from the database to compare with the Excel, so I’m not sure that’s what you want, because usually you will compare with a few columns then pull in the missing fields from the database, like I mentioned.

Regards.

[RaviDevaraj]

@ClaytonM

Also, keep in mind that after the “Where” you should only compare columns that are in your Excel file so it can pull in the missing fields from the database. You are including every column from the database to compare with the Excel, so I’m not sure that’s what you want, because usually you will compare with a few columns then pull in the missing fields from the database, like I mentioned.

Hi @ClaytonM
in execute query i have placed the only select query now with out erros . see below

But I am not Sure rest of queries if i am adding that through an error !!

why because simply i typed the query and put into my sql itself it throws an error like + symbol incorrect the syntax expression missing etc,.,.,.

@ClaytonM

A.Serial_No ='"+

i need to add in that query alone or i need to some where else like variable anything else

note : All my data i will add in my database table from there i need to get all details and i need to trigger my xaml file now.

I no need to use the using Excel file trigger of xaml file Process i need to over come that one @ClaytonM

[ClaytonM]

Make sure you use " _" on the end of each line (but not the last line) when you use multiple lines in your string concatenation.

Refer to my above SQL example where I demonstrate how to use the " _" character.

Regards.

[RaviDevaraj]

@ClaytonM

Refer to my above SQL example where I demonstrate how to use the " _" character.

USED THIS AND ENTER THE COMPLETE QUERY THROWS AN ERROR @ClaytonM

Can you validate the query and say where i have made an mistake to correct that query :

“SELECT Serial_No,User_Name,Password,Start_Date,End_Date,Broker_Id FROM Master_data
Where Serial_No ='”+row(Serial_No).ToString.Trim+“’ AND User_Name
=’”+row(User_Name).ToString.Trim+
“’ AND Password ='”+row(Password).ToString.Trim+“’ AND Start_Date ='”+row(Start_Date).ToString.Trim+“’
AND End_Date='”+row(End_Date).ToString.Trim+“’ AND Broker_Id ='”+row(Broker_Id).ToString.Trim+“'”

this query throws an error .

[ClaytonM]

Hi.

Can you provide what the error says?, because that would make it easier to identify the issue.

Also, I assume that all the column names used with row() are variables because you don’t have them as strings.

One thing I would suggest doing is outputting your query string to a text file and checking it to make sure that all the values from the Excel file are coming in correctly.

Regards.

[RaviDevaraj]

@ClaytonM

Can you provide what the error says?,

[ClaytonM]

Oh ok, you still have a validation error.
Can you click Edit Query and post what that looks like?
It doesn’t look like you are using the “_” at the end of each line correctly or formulating your query string.

Thanks.

[RaviDevaraj]

[RaviDevaraj]

Hi @ClaytonM

i have my updated xaml file here: MainTwoFiles.xaml (100.6 KB)
in this try catch block i have my connection and sql commands available is correct too

rest of the work need to held in below activities that how we need to achieve and from database i need to fetch a values too ?

Any idea to handle this

[RaviDevaraj]

Hi @ClaytonM

Any update regarding this scenario

Thanks
RaviDevaraj

[RaviDevaraj]

HI @ClaytonM
I have tried the For each Activity for Loop concepts in that “row in DT” throew an error and in if condition “Late Binding” it means what ?

Please find the error screenshot too below !!

And find the latest Update XAML File Here : MainTwoFiles.xaml (194.3 KB)

kindly help with this issues and we can move forward from this issues here !!
Thanks
RaviDevaRaj

[ClaytonM]

Hello.

Sorry I have been too busy to help.

I made a brief look and to resolve those validation errors you can try swapping out the For each to a new one. I think the For each could not change the row to a DataRow for some reason so you got late binding error.

Properties should look like that. When you use a generic For each with a data table, you need to use .AsEnumerable like shown in the image.

The If condition should be resolved by using the System.Data.DataRow as the TypeArgument.

Regards.

[RaviDevaraj]

@ClaytonM

Properties should look like that. When you use a generic For each with a data table, you need to use .AsEnumerable like shown in the image.

This one is competed, now no error in that section Thanks a lot for your help !!!

@ClaytonM

The If condition should be resolved by using the System.Data.DataRow as the TypeArgument.

In TypeInto Activity “TypeArgument” not available
see below image :

were it could be available and need to set can you help me

[ClaytonM]

The TypeArgument property is in the For each activity. It should be set to DataRow, like I mentioned.

If that still doesn’t help, let me know what the error says.

[RaviDevaraj]

@ClaytonM

The TypeArgument property is in the For each activity. It should be set to DataRow, like I mentioned.

this i have set that now no error
if condition is correct ?
then how to do trigger the next step here @ClaytonM

[ClaytonM]

@RaviDevaraj

if condition is correct ?

Yeah, it looks correct. “Company_Name” should be the column name that is in DT and CompName should contain a string that you are comparing with.

@RaviDevaraj

then how to do trigger the next step here

I don’t know what you are asking here. You basically want to perform the steps that you want to when the row item equals CompName.

Regards.