SPs/Command Object/Parameters Problem

[Chopper]

I've searched through google groups and cannot find an answer to this:
My application passes values from a form to a page that requests them and
sets them as parameters which are then passed to the SP. The SP then updates
a table with these values.

The problem is that not all of the values need to be entered on the form and
therefore there can be empty parameters being passed to the stored
procedure.
In the SP I've made sure all the default values are set so there is no
problem there but it's the calling of the SP with empty parameters that is
causing problems.

What would be ideal is if I could conditionally pass parameters, e.g.:
if varTitle <> "" then
.Parameters.Append
..CreateParameter("@title",adLongVarChar,adParamIn put,len(varTitle),varTitle)
end if

But this gives errors.

Please can anyone tell me if there is a way of doing this or if I am doing
something wrong? I understand I could dynamically build and execute an
update query in ASP but would prefer to use SPs as everything else uses
them.

TIA

chopper

[Bob Barrows]

Pass the missing values as Nulls. Instead of this:

> if varTitle <> "" then
> .Parameters.Append
>

..CreateParameter("@title",adLongVarChar,adParamIn put,len(varTitle),varTitle)

> end if

You want to do this:
if varTitle = "" then
varTitle = Null
end if
.Parameters.Append
..CreateParameter("@title",adLongVarChar,adParamIn put,len(varTitle),varTitle)

Do you really need a Command object? If your SP doesn't have any output
parameters and you're not interested in evaluating the Return value, then
you don't need a Command object. You can use the
stored-procedure-as-connection-method technique. A quick Google search
should provide details.

HTH,
Bob Barrows

Chopper wrote:

> I've searched through google groups and cannot find an answer to this:
> My application passes values from a form to a page that requests them
> and sets them as parameters which are then passed to the SP. The SP
> then updates a table with these values.
>
> The problem is that not all of the values need to be entered on the
> form and therefore there can be empty parameters being passed to the
> stored procedure.
> In the SP I've made sure all the default values are set so there is no
> problem there but it's the calling of the SP with empty parameters
> that is causing problems.
>
> What would be ideal is if I could conditionally pass parameters, e.g.:
> if varTitle <> "" then
> .Parameters.Append
>

..CreateParameter("@title",adLongVarChar,adParamIn put,len(varTitle),varTitle)

> end if
>
> But this gives errors.
>
> Please can anyone tell me if there is a way of doing this or if I am
> doing something wrong? I understand I could dynamically build and
> execute an update query in ASP but would prefer to use SPs as
> everything else uses them.
>
> TIA
>
> chopper

[Chopper]

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OeZsNRljDHA.2140@TK2MSFTNGP09.phx.gbl...

> Pass the missing values as Nulls. Instead of this:

> > if varTitle <> "" then
> > .Parameters.Append
> >

>

..CreateParameter("@title",adLongVarChar,adParamIn put,len(varTitle),varTitle)

> > end if

>
> You want to do this:
> if varTitle = "" then
> varTitle = Null
> end if
> .Parameters.Append
>

..CreateParameter("@title",adLongVarChar,adParamIn put,len(varTitle),varTitle)

>
> Do you really need a Command object? If your SP doesn't have any output
> parameters and you're not interested in evaluating the Return value, then
> you don't need a Command object. You can use the
> stored-procedure-as-connection-method technique. A quick Google search
> should provide details.
>
> HTH,
> Bob Barrows
>

Thanks for your reply.
I get a type mismatch error when creating the parameter when the argument is
null :(
Are you sure null is a valid argument with the adLongVarChar type?

chopper

[Bob Barrows]

Chopper wrote:

>
> Thanks for your reply.
> I get a type mismatch error when creating the parameter when the
> argument is null :(
> Are you sure null is a valid argument with the adLongVarChar type?
>
> chopper

Oh, my bad. I did not read closely enough.

With a string parameter, there should be no problem passing an empty string.
You do need to make sure your variable contains an empty string (and isn't
simply empty). You can use CStr to guarantee that.

With number or datetime types, I have found the need to set the values to
Null when values were not supplied by the user.

Bob Barrows

[Chopper]

<snip>

> With a string parameter, there should be no problem passing an empty

string.

> You do need to make sure your variable contains an empty string (and isn't
> simply empty). You can use CStr to guarantee that.
>
> With number or datetime types, I have found the need to set the values to
> Null when values were not supplied by the user.
>
> Bob Barrows
>
>

That did it!
Many thanks for your help.