Don't pass a parameter to SP 
Author Message
 Don't pass a parameter to SP

I am using the following VBA statements to evaluate if a parameter is to be
specified.
.
.
.
.
  If ohDictionary.Exists(ShippingInstructionsKeyName) Then

adVarChar, adParamInput, _

Len(PurchaseOrderHeaderDictionary.Item(ShippingInstructionsKeyName)), _

PurchaseOrderHeaderDictionary.Item(ShippingInstructionsKeyName))
  End If

  If ohDictionary.Exists(ShippingMethodKeyName) Then

adParamInput, _

Len(PurchaseOrderHeaderDictionary.Item(ShippingMethodKeyName)), _

PurchaseOrderHeaderDictionary.Item(ShippingMethodKeyName))
  End If
.
.
.
.

what happens is if the first conditional statement evaluates false VB/ADO is
placing the value for the second conditional statement (since it evaluates

correct variable).  If a value is not passed for a variable I want the SP to
substitute its default value in place of which I have specified in my SP.

My stored procedure is as follows:



as
insert into oh (id,......,shippinginstructions,shippingmethod,.....)

Thanks.



Mon, 22 Mar 2004 00:56:30 GMT  
 Don't pass a parameter to SP
The command object will require you to use a complete param collection for
the sp that you are calling. Since you are already checking, in the else
section, pass your default value or a value of null.

Eg.
If condition Then
    'store your value
Else

                        adVarChar, adParamInput, _

Len(PurchaseOrderHeaderDictionary.Item(ShippingInstructionsKeyName)), _
                        null)
End If


Quote:
> I am using the following VBA statements to evaluate if a parameter is to
be
> specified.
> .
> .
> .
> .
>   If ohDictionary.Exists(ShippingInstructionsKeyName) Then

> adVarChar, adParamInput, _

> Len(PurchaseOrderHeaderDictionary.Item(ShippingInstructionsKeyName)), _

> PurchaseOrderHeaderDictionary.Item(ShippingInstructionsKeyName))
>   End If

>   If ohDictionary.Exists(ShippingMethodKeyName) Then

adVarChar,
> adParamInput, _

> Len(PurchaseOrderHeaderDictionary.Item(ShippingMethodKeyName)), _

> PurchaseOrderHeaderDictionary.Item(ShippingMethodKeyName))
>   End If
> .
> .
> .
> .

> what happens is if the first conditional statement evaluates false VB/ADO
is
> placing the value for the second conditional statement (since it evaluates

> correct variable).  If a value is not passed for a variable I want the SP
to
> substitute its default value in place of which I have specified in my SP.

> My stored procedure is as follows:

> create procedure dbo.test


> as
> insert into oh (id,......,shippinginstructions,shippingmethod,.....)

> Thanks.



Mon, 22 Mar 2004 04:07:51 GMT  
 Don't pass a parameter to SP
I have already tried this.  Doesn't work.

Thanks.


Quote:
> The command object will require you to use a complete param collection for
> the sp that you are calling. Since you are already checking, in the else
> section, pass your default value or a value of null.

> Eg.
> If condition Then
>     'store your value
> Else

>                         adVarChar, adParamInput, _

> Len(PurchaseOrderHeaderDictionary.Item(ShippingInstructionsKeyName)), _
>                         null)
> End If



> > I am using the following VBA statements to evaluate if a parameter is to
> be
> > specified.
> > .
> > .
> > .
> > .
> >   If ohDictionary.Exists(ShippingInstructionsKeyName) Then

> > adVarChar, adParamInput, _

> > Len(PurchaseOrderHeaderDictionary.Item(ShippingInstructionsKeyName)), _

> > PurchaseOrderHeaderDictionary.Item(ShippingInstructionsKeyName))
> >   End If

> >   If ohDictionary.Exists(ShippingMethodKeyName) Then

> adVarChar,
> > adParamInput, _

> > Len(PurchaseOrderHeaderDictionary.Item(ShippingMethodKeyName)), _

> > PurchaseOrderHeaderDictionary.Item(ShippingMethodKeyName))
> >   End If
> > .
> > .
> > .
> > .

> > what happens is if the first conditional statement evaluates false
VB/ADO
> is
> > placing the value for the second conditional statement (since it
evaluates

> > correct variable).  If a value is not passed for a variable I want the
SP
> to
> > substitute its default value in place of which I have specified in my
SP.

> > My stored procedure is as follows:

> > create procedure dbo.test


> > as
> > insert into oh (id,......,shippinginstructions,shippingmethod,.....)

> > Thanks.



Mon, 22 Mar 2004 21:03:56 GMT  
 Don't pass a parameter to SP
You must specify all the parameters, whether or not you want to use the
default.

Ignore the value property when you create the parameter.  It defaults to
adEmpty.

Once the parameters have been created then you specify the values.

If ohDictionary.Exists(ShippingInstructionsKeyName) Then

Else

End If

The else is there if you are executing the statement a number of times and
need to reset the parameter value more than once.

HTH

Bruce


Quote:
> I am using the following VBA statements to evaluate if a parameter is to
be
> specified.
> .
> .
> .
> .
>   If ohDictionary.Exists(ShippingInstructionsKeyName) Then

> adVarChar, adParamInput, _

> Len(PurchaseOrderHeaderDictionary.Item(ShippingInstructionsKeyName)), _

> PurchaseOrderHeaderDictionary.Item(ShippingInstructionsKeyName))
>   End If

>   If ohDictionary.Exists(ShippingMethodKeyName) Then

adVarChar,
> adParamInput, _

> Len(PurchaseOrderHeaderDictionary.Item(ShippingMethodKeyName)), _

> PurchaseOrderHeaderDictionary.Item(ShippingMethodKeyName))
>   End If
> .
> .
> .
> .

> what happens is if the first conditional statement evaluates false VB/ADO
is
> placing the value for the second conditional statement (since it evaluates

> correct variable).  If a value is not passed for a variable I want the SP
to
> substitute its default value in place of which I have specified in my SP.

> My stored procedure is as follows:

> create procedure dbo.test


> as
> insert into oh (id,......,shippinginstructions,shippingmethod,.....)

> Thanks.



Tue, 23 Mar 2004 03:59:36 GMT  
 Don't pass a parameter to SP
Something else must be going on then, because I use SPs all the time and use
the Command object about 99% of the time. I have never had any problem.


Quote:
> I have already tried this.  Doesn't work.

> Thanks.



> > The command object will require you to use a complete param collection
for
> > the sp that you are calling. Since you are already checking, in the else
> > section, pass your default value or a value of null.

> > Eg.
> > If condition Then
> >     'store your value
> > Else

> >                         adVarChar, adParamInput, _

> > Len(PurchaseOrderHeaderDictionary.Item(ShippingInstructionsKeyName)), _
> >                         null)
> > End If



> > > I am using the following VBA statements to evaluate if a parameter is
to
> > be
> > > specified.
> > > .
> > > .
> > > .
> > > .
> > >   If ohDictionary.Exists(ShippingInstructionsKeyName) Then

> > > adVarChar, adParamInput, _

> > > Len(PurchaseOrderHeaderDictionary.Item(ShippingInstructionsKeyName)),
_

> > > PurchaseOrderHeaderDictionary.Item(ShippingInstructionsKeyName))
> > >   End If

> > >   If ohDictionary.Exists(ShippingMethodKeyName) Then

> > adVarChar,
> > > adParamInput, _

> > > Len(PurchaseOrderHeaderDictionary.Item(ShippingMethodKeyName)), _

> > > PurchaseOrderHeaderDictionary.Item(ShippingMethodKeyName))
> > >   End If
> > > .
> > > .
> > > .
> > > .

> > > what happens is if the first conditional statement evaluates false
> VB/ADO
> > is
> > > placing the value for the second conditional statement (since it
> evaluates

is
> > > correct variable).  If a value is not passed for a variable I want the
> SP
> > to
> > > substitute its default value in place of which I have specified in my
> SP.

> > > My stored procedure is as follows:

> > > create procedure dbo.test


> > > as
> > > insert into oh (id,......,shippinginstructions,shippingmethod,.....)

> > > Thanks.



Tue, 23 Mar 2004 21:47:26 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Don't pass a parameter

2. ADO don't catch sp error

3. pass parameter to SQL SP from VB and return recordset

4. Passing parameters to IF-Statement SP

5. Problem passing the default parameters in sp

6. Passing a adGuid parameters to a SP...

7. Passing a adGuid parameter to a SP...

8. Passing Parameters to IF-Statement SP

9. I can't call Oracle SP from VB5 with over 10 parameters

10. can't get output parameter from SP using ADO

11. Passing Parameters to stored parameter queries using VB 5's Data Controls

12. File search parameters don't work

 

 
Powered by phpBB® Forum Software