How to check for date availability before updating in DB

Hi, :wink:
I need to use a large SQL request to update multiple dates in my MS Access DB. Column9 - Column22 have a DateTime type in DB.

"UPDATE " & itemBE &" SET Column9 = #" & FilteredDataTable.Rows(0).Item("Column9").ToString & "#, Column10 = #" & FilteredDataTable.Rows(0).Item("Column10").ToString & "#, Column16 = #" & FilteredDataTable.Rows(0).Item("Column16").ToString & "#, Column17 = #" & FilteredDataTable.Rows(0).Item("Column17").ToString & "#, Column18 = #" & FilteredDataTable.Rows(0).Item("Column18").ToString & "#, Column19 = #" & FilteredDataTable.Rows(0).Item("Column19").ToString & "#, Column20= #" & FilteredDataTable.Rows(0).Item("Column20").ToString & "#, Column21 = #" & FilteredDataTable.Rows(0).Item("Column21").ToString & "#, Column22 = #" & FilteredDataTable.Rows(0).Item("Column22").ToString & "# WHERE Column2 = '" & FilteredDataTable.Rows(0).Item("Column2").ToString & "' AND Column3 = '" & FilteredDataTable.Rows(0).Item("Column3").ToString & "'"

This query works fine when there are all dates in the table. But if one of the dates = Nothing I get the error: "Updating order dates in the database, table by company code: The date contains a syntax error in the query expression ‘#’. "

Thanks for your help

1 Like

Hi,

If you do an if checking the dates would it solve?
Something like:

FilteredDataTable.Rows(0).Item(“Column9”) isNot Nothing and FilteredDataTable.Rows(0).Item("Column22 ") isNot Nothing

Thanks for the answer. I think it would help, but how to write the correct syntax?

You must do the IF outside of your SQL code.
UiPath.Studio_2021-12-17_13-39-17

Thanks for the answer.But I have many columns with dates and I never know which column does not have a date. And if the date is not in the FilteredDataTable column, you need to do UPDATE COLUMN = NULL

ok, now i understand what you need.
Even if the field is empty you need to update with the null value.
Try using a CASE, I have no experience with MS Access, but I’m passing the PostgreSQL syntax, I believe it’s something similar for MS Access.

" case “+FilteredDataTable.Rows(0).Item(“Column9”).toString +” when ‘’ then null else “+FilteredDataTable.Rows(0).Item(“Column9”).toString+” end "

I try to use Case


But it works for a very long time

1000 lines processes in 35 minutes