handling nulls with inserts

Hi,

I've got a program that has a form. ?On the form there are several fields which are optional that I would prefer to remain as NULL in the database (rather than a default value) if the user doesn't fill them out. ?I'm using stored procedures for my inserts (sql 2000 + C#). ?

How do most people handle these situations? ?Right now I have a seperate function which receives the parameter values as params and then handles creating the parameters and executing the stored procedure. ?I'm not sure how to handle passing that I want one of those to be null, at least not without having multiple functions and possibly various stored procedures for different possibilities.

Thanks.

I would recommend not using stored procedures if this is what you want to accomplish. Like you said you would have to create multiple stored procedures for each variation. Instead create the insert in your code. I use a string builder to build an SQL statement. Within the logic of your system you can put if then statements to determine what the user inputed and therefore how to build the SQL and correlating params.

Have you tried to pass the value DBNull.Value to the stored procedure? Not sure that this would work, but it might be worth a try if you want to keep your stored procedures.

Hope that helps.

Handling nulls in matrix sub totals

I have a matrix where I am using the inscope function to return different
calculations depending on which group it is in (ie the detail is just the
value and the sub totals are sum(values)).
In for the subtotal group I have used:
iif(isnothing(sum(Fields!CurrentCount.Value)), 0,
sum(Fields!CurrentCount.Value)
But this is still returning blanks where there are no values in the group to
sum together.
Can anyone suggest how to get around this issue as it ?
TIA
Andrewyou might try iif(sum(Fields!CurrentCount.Value = nothing)
"Andrew Murphy" <AndrewMurphy@.discussions.microsoft.com> wrote in message
news:56B0AB62-6CDE-4F41-BF39-AC72FABAC2C0@.microsoft.com...
>I have a matrix where I am using the inscope function to return different
> calculations depending on which group it is in (ie the detail is just the
> value and the sub totals are sum(values)).
> In for the subtotal group I have used:
> iif(isnothing(sum(Fields!CurrentCount.Value)), 0,
> sum(Fields!CurrentCount.Value)
> But this is still returning blanks where there are no values in the group
> to
> sum together.
> Can anyone suggest how to get around this issue as it ?
> TIA
> Andrew
>|||also if this formula you put on here looks just like the one you have in the
expression then you need to add a perenthesis onto the end. I tried to
recreate your problem and I get a 0. Weird
"Andrew Murphy" <AndrewMurphy@.discussions.microsoft.com> wrote in message
news:56B0AB62-6CDE-4F41-BF39-AC72FABAC2C0@.microsoft.com...
>I have a matrix where I am using the inscope function to return different
> calculations depending on which group it is in (ie the detail is just the
> value and the sub totals are sum(values)).
> In for the subtotal group I have used:
> iif(isnothing(sum(Fields!CurrentCount.Value)), 0,
> sum(Fields!CurrentCount.Value)
> But this is still returning blanks where there are no values in the group
> to
> sum together.
> Can anyone suggest how to get around this issue as it ?
> TIA
> Andrew
>|||and aslo maybe you should try to set the null value to 0 before you sum it.
"Andrew Murphy" <AndrewMurphy@.discussions.microsoft.com> wrote in message
news:56B0AB62-6CDE-4F41-BF39-AC72FABAC2C0@.microsoft.com...
>I have a matrix where I am using the inscope function to return different
> calculations depending on which group it is in (ie the detail is just the
> value and the sub totals are sum(values)).
> In for the subtotal group I have used:
> iif(isnothing(sum(Fields!CurrentCount.Value)), 0,
> sum(Fields!CurrentCount.Value)
> But this is still returning blanks where there are no values in the group
> to
> sum together.
> Can anyone suggest how to get around this issue as it ?
> TIA
> Andrew
>

Handling nulls in calculated rport items

Hello,
How do I block null values from displaying "#Error" when I format a
datetime.
dbuchananWould an IIF statement not work in the expression
for example:
=IIF (Fields!Date.Value = "NULL", "",Fields!Date.Value)
This would bring up a blank space (or whatever you want it the output to be)
if the value is NULL, otherwise it just brings out the value.
Im fairly new to all of this so its just a thought.
"dbuchanan" wrote:
> Hello,
> How do I block null values from displaying "#Error" when I format a
> datetime.
> dbuchanan
>|||Theoretically yes that would work but in practice it doesn't as you have to
write
=iif(Fields!Date.Value is system.dbnull.Value, "", Fields!Date.Value)
otherwise deal with it in the initial query.
Greg
"Robert Seal" <RobertSeal@.discussions.microsoft.com> wrote in message
news:AB5060A6-88AE-4FB4-A224-C1C548418EFB@.microsoft.com...
> Would an IIF statement not work in the expression
> for example:
> =IIF (Fields!Date.Value = "NULL", "",Fields!Date.Value)
> This would bring up a blank space (or whatever you want it the output to
> be)
> if the value is NULL, otherwise it just brings out the value.
> Im fairly new to all of this so its just a thought.
> "dbuchanan" wrote:
>> Hello,
>> How do I block null values from displaying "#Error" when I format a
>> datetime.
>> dbuchanan
>>

Handling NULLS - help!

I would like to display some text in a textbox conditional on the null/not
null value of a database field (called "ID").
I have tried various versions of the following:
=iif(len(trim(Fields!ID.Value))<1 OR (Fields!ID.Value IS
system.dbnull.value),"ID is null","ID is not null")
but I still get this error:
"The query returned no rows for the data set. The expression therefore
evaluates to null."
any suggestions?Try =IIF(IsNothing(Fields!ID.Value), ...|||Thanks Rose - that worked perfectly :)