Optional fields in SQL server INSERT

Help
1

Dear Community,
In one of my applications, I am inserting variables into a SQL server table. Some of these parameters are optional and thus sometimes don’t have any values. For integers, even if the default value of the variable is null, my bot will still push a value of 0 into the table. This is not good for me as 0 and null have very different meanings for this table. Any idea how I can make the bot pass a null value? Thanks!

Have a nice day,
Phil

2

If i understand you well. woudn’t a simple IF statement solve this. If value.contains(“0”) OR value.contains(“”) then assign the value to en ampty string. Which will make it fill in nothing. And else enter your normal data?

3

Hi Joeri,
Thanks for the reply.

Right now, I have a variable called val_mes (measured value). Sometimes, there is a value measured, sometime there isn’t. By default, the value is Nothing (which I understand makes it Null).

If there isn’t a value to be measured, the bot will never go down the process that modifies the value, and so it should remain nothing. When it comes to the Execute Non Query activity, it will pass the value from the parameter val_mes, which is equal to the variable val_mes (which should be null). However, when I check the results on the server, val_mes is equal to 0. I’m having the same issue with all other optional integer variables, so I’m guessing I’m doing something wrong. Any thoughts?

Thanks!

Phil

4

Also, I’m pretty sure I will have a conversion error if I try to pass an empty string into an integer field.

5

Try to check which value you get from both scenarios. If you use the .ToString function. You can change it back to an int with CType later on in the progress.

6

Any update on this Phil? I managed to run a small test for this. Made a small sequence that has an int variable. Tried printing to a Message Box and it returned 0. To me this seems counter intuitive, how does the Int32 variable even have a ToString method if its set to Nothing? To test further I did a Nothing.ToString to a MessageBox. This had no validation error but returned “Object reference not set to an instance of an object.” error. One solution that I found was to treat the variable as GenericValue. This would return an int if present or error out if printed. I’m unable to test this when being sent to DB but figured I’d let you know in case you can test!

7

Update! I actually found this on Stackoverflow https://stackoverflow.com/questions/18247455/pass-null-value-to-integer-variable-in-vb-net
What worked for me was making the variable of type Nullable for my small test sequence. Again, not able to test on SQL but let me know if that works!!

1 Like