I have database table of three columns Filename, Status, Date.
I am updating the database table with filename(this contains the whole path and file name). If the file is available, i am updating table status as file available and if the file is not available i have to update the status as file not available,
however when the file is not available the filename is updated as “null” and status as “Status” instead of file not available.
Can anyone help me how to update the status as “file not available” when file is not available.
Use Path Exist Activity to check the file is available or not
Then-> you can update the details
Else-> row(“FileName”) = “”
Continue as it is
If you mean a database as a sql database then We can use execute non query activity and update the status column in sql database
If you mean a database as a excel workbook then
Hope the below steps would help you resolve this
Use a excel application scope and pass filepath as input
Inside the scope use a READ RANGE activity and get the output as datatable named dt
Then use a FOR EACH ROW activity and pass dt as input
Inside the loop use a PATH EXISTS activity and mention the input as CurrentRow(“Filename”).ToString and get the Boolean variable output as bool_exists
Then inside the same loop use a IF condition like this
bool_exists = TRUE
If true it goes to THEN block where use a ASSIGN ACTIVITY LIKE THIS
CurrentRow(“Status”) = “File Available”
If you mean as sql database inside the IF activity we have atlast use a EXECUTE NON QUERY ACTIVITY instead of a assign activity and mention this command in SQL property
SET Statys = ‘File Available’
WHERE Filename = @filenmae;
And in parameter property create a variable named Filename and pass value as CurrentRow(“Filename”).ToString
Thanks for the reply. I am using sql database
“Update “Table_Name” SET STATUS=‘File is not available’ where InputFileName=’” +Str_lastestModifiedFile+"’"
Str_lastestModifiedFile this is the variable which contains the filename and path.
The above query is working if the file is available, but if the file is not available, in database it is updating as null in filename column and status as “status” but it should be "file is not available.
Perfect then use the same query but with above steps I mentioned
It should work for sure
Thanks for the reply, i followed same, i have a boolean value which gives file is available or not. if it is available i using one query and if it is not available i am using the same query but set the status as file is not available, which is not updating for me.
Can you share the screenshot of if condition alone
Thanks for the reply. Issue is resolved. I have used select query and from the output of select query, i am checking whether the file is null or not, if it is null i am updating the status as file not available and if it is not null, i am updating the status as file is available
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.