Default query parameter values

[The Plankmeister]

Hi...

I have a query which I'm accessing through PHP as a stored procedure.
However, I need to be able not to pass a couple of parameters in certain
situations. When I do this, I get an error:

[Microsoft][ODBC Microsoft Access Driver] Parameter [@str_year] has no
default value.

But it works fine in Access when I run the query from there and just hit
enter on the relevant parameter dialog. Is there a way of setting a default
value for a query parameter? I can't find out anything about it...

TIA.


Plankmeister.

[MacDermott]

Re: Default query parameter values

AFAIK Access has no way to set/store default parameter values.
When you "just hit enter", you are entering the empty string which was
displayed in the parameter dialog.
You might try setting that parameter to a blank string programatically .

HTH
- Turtle

"The Plankmeister" <plankmeister_N OSPAM_@hotmail. com> wrote in message
news:3fd4634c$0 $70000$edfadb0f @dread12.news.t ele.dk...[color=blue]
> Hi...
>
> I have a query which I'm accessing through PHP as a stored procedure.
> However, I need to be able not to pass a couple of parameters in certain
> situations. When I do this, I get an error:
>
> [Microsoft][ODBC Microsoft Access Driver] Parameter [@str_year] has no
> default value.
>
> But it works fine in Access when I run the query from there and just hit
> enter on the relevant parameter dialog. Is there a way of setting a[/color]
default[color=blue]
> value for a query parameter? I can't find out anything about it...
>
> TIA.
>
>
> Plankmeister.
>
>[/color]

[MGFoster]

Re: Default query parameter values

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm assuming you mean that you have a stored procedure (SP) in MSDE or
MS SQL Server, not a SP in PHP. Here is how to define a default
parameter value in a MS SQL Server SP:

CREATE PROCEDURE usp_thisSP
@first_paramete r VARCHAR(32) = 'business'
@second_paramet er MONEY = 200.00
AS
.....

I believe you can use a method described in KB103181 (my modification
for MS SQL Server - untested - don't know if setting a variable to a
NULL will work):

CREATE PROCEDURE usp_AnotherExam ple
@ShipCountry VARCHAR(4) = NULL
AS
.....

WHERE @ShipCountry IS NULL
OR (Orders.ShipCou ntry=@ShipCount ry AND @ShipCountry IS NOT NULL )



HTH,

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP9TzzIechKq OuFEgEQL/ggCg9aXgPgKZWKd IsRNnsuOM5lisb0 cAoPyh
PK4Y6I9/36a+Uoq2FlH3KUo M
=8YF3
-----END PGP SIGNATURE-----


The Plankmeister wrote:[color=blue]
> Hi...
>
> I have a query which I'm accessing through PHP as a stored procedure.
> However, I need to be able not to pass a couple of parameters in certain
> situations. When I do this, I get an error:
>
> [Microsoft][ODBC Microsoft Access Driver] Parameter [@str_year] has no
> default value.
>
> But it works fine in Access when I run the query from there and just hit
> enter on the relevant parameter dialog. Is there a way of setting a default
> value for a query parameter? I can't find out anything about it...
>
> TIA.
>
>
> Plankmeister.
>
>[/color]