Update database

Hi Team,

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.

Thanks.

1 Like

@nagini.pragna

Use Path Exist Activity to check the file is available or not

IF Condition

Then-> you can update the details
Else-> row(“FileName”) = “”

Continue as it is

Thanks

Hi

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

Or

If you mean a database as a excel workbook then
Hope the below steps would help you resolve this

  1. Use a excel application scope and pass filepath as input

  2. Inside the scope use a READ RANGE activity and get the output as datatable named dt

  3. Then use a FOR EACH ROW activity and pass dt as input

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

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

UPDATE tablename
SET Statys = ‘File Available’
WHERE Filename = @filenmae;

And in parameter property create a variable named Filename and pass value as CurrentRow(“Filename”).ToString

Cheers @nagini.pragna

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

Cheers @nagini.pragna

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.

Great
Can you share the screenshot of if condition alone

@nagini.pragna

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.