Insert Null into a date database field via database execute query

Hi Team,
Via the database execute query i am calling a stored procedure. This stored procedure writes data into a database table. I have 2 date columns in the table and 1 date-time column.

How do i pass a null value via uipath?

I have tried the following but no luck.

  1. Null - this has been deprecated.

  1. Nothing - this still gives me an error

image

  1. System.DbNUll -

1 Like

@RiteshJain1411 Try this urvariablename= “”

Don’t we have nullable types in UiPath?

In C#, for example -

int? iValue = null;

By adding ‘?’, int takes the values of int range + one extra value ‘Null’.

[OR]

int? example = null;
object exampleCast = (object) example ?? DBNull.Value;
object exampleNoCast = example ?? SqlInt32.Null;

Please give a try.

Regards,
Karthik Byggari

1 Like

HI All,

I also want to pass null into Database by using Execute non query command.
can you please help me in how to pass Null into DB using Uipath

@KapilJain in assign activity use this urvariablename= “”

@indra,

it is passing blank value, i want database to display it as “NULL” not blank.

@KapilJain Not sure works are not Try this urvariablename = “NULL”

I have tried with below:-
“NULL”- this update “NULL” as string
“”
System.DBNull
but all above does not work.

@KapilJain try this urvariablename = DBNull.Value

1 Like

Hi Indra,

i tried with this as well , but it was giving string conversion error.
Anyways it got solved now i am able to pass NULL value with different approach.

thanks for assistance

Hi Kapil,

Please share your findings and solution with us

Hi Ritesh,

please find below example:-
“DECLARE @test_value int
EXEC @test_value = [dbo].[Tasktest]
@testId,
NULL,
NULL,
NULL,
NULL,
NULL,
@ID OUTPUT”

you need to directly pass “NULL” at the column position.
no need to specify the column name.

let me know if it clarifies.

@KapilJain if you try variable name=“” in db you will get empty string. assign variablename =“” in uipath and pass this parameter in execute query or non query, and then check in DB

if Date field null able declare variable below like this
Assign OpenDatetime=if(string.IsNullOrWhiteSpace(OpenDate),new Nullable(of Date), Convert.ToDateTime(OpenDate))

Then make your SP parameter as Allow null able Ex
@CLOSE_DATE datetime=null

I hope this will work.

Thanks & Regards,
Prakash M

Hi,
I came across this situation and because I couldn’t find a straight answer, I thought to paste here - what I consider to be - the solution.
SQL doesn’t know what type UiPath is pushing. SQL will try to cast to the type it’s required at the time of the table creation.
So for example, let’s say you have a table in SQL, with a column of DateTime type. When you push it through UiPath, you can do:

  1. Type String, variable: Now().ToString
  2. or, you can push a DateTime Type (SQL is not aware at this point), and a variable like: Today()
    I’ve just invented 2 scenarios above. SQL will try to parse the input (the above from UiPath) into its own data type.

Therefore:

  • in the first scenario you might push a ‘6/5/2020’ as May 6, but SQL to parse it as June 5th, because it depends on your regional and config settings. So you have to be careful with the conversion.
  • in the second scenario above, everything is converted successfully (direct) way.

Now, back to your point, about the NULLs:
If you take care of your instantiations carefully in UiPath, why not pushing it as an Object type (as remember, SQL will do the rest)

I will give some examples below:


In the above examples:

  1. If you are absolutely sure that you don’t have NULLs in your DateTime, you can keep the type set as ‘DateTime’.
  2. If you know that you might have missing dates and you want the NULL in your DB, then make it Object.

I wanted to prove a point - there are many examples we can give. The idea is to make sure you are pushing a correct value for SQL to parse. Don’t just assume the date is correct, because Object will not through you an error.

  1. The same goes with other types, like I did above with Arg3 and Arg4. So if you know that you might have NULLs, the DB will take the value pushed by UiPath and try to cast it to float let’s say. So, in case you have myDouble sent as null, you will have in your DB the following
    - 0 (zero) (if Type is Double - Arg3)
    - NULL (if Type is Object - Arg4)

I tend to write a lot - so apologies - but if you pay attention, you should be able to implement it successfully.

1 Like

The example stored procedure syntax (in the image) helps me in handling 2 scenarios:

1. Handling optional parameters (e.g., if I have 5 parameters but only want to pass in 2 of the 5) - with the syntax shown I can simply not pass in a value for a parameter within UiPath Studio and it will be ignored

2. Being able to search for “Null” values - with the syntax shown I can search for nulls by passing in an empty string (e.g., “”)

For a more advanced use case - one where the parameters are passed in from a UiPath Form, and you therefore are always passing in a value to the query - there is another pattern you can use (shown in the image):

  1. The ISNULL statement lets us pass in a blank string, and if we do then it will return all the results
  2. The IIF statement lets us pass in a string called “NULL” and it will return all values that are explicitly labeled NULL. It also covers the use case where you don’t pass in a paremeter at all.

1 Like

For anyone still looking for a solution for passing null value to a stored procedure:

Change the parameter type to Object, value is DBValue.Null

This works very well in my case.

Hope that helps

1 Like

Hi all,

Appreciate the solution provided however how would I apply this when reading in data from Excel and storing as a datatable in UIPath before then inserting into SQL Server?

I have tried using the Build Data Table first to alow me to set the Data Type for my datetime columns to Object but still the Insert activity throws an error : “Insert: Failed to convert parameter value from a String to a DateTime.” I assume when I read in my data and output it to the same data table built before it just resets my Data Types?

Thanks