By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

Default query parameter values

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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" <pl******************@hotmail.com> wrote in message
news:3f***********************@dread12.news.tele.d k...
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.

Nov 12 '05 #2

P: n/a
-----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_parameter VARCHAR(32) = 'business'
@second_parameter 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_AnotherExample
@ShipCountry VARCHAR(4) = NULL
AS
.....

WHERE @ShipCountry IS NULL
OR (Orders.ShipCountry=@ShipCountry AND @ShipCountry IS NOT NULL )

HTH,

MGFoster:::mgf
Oakland, CA (USA)

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

iQA/AwUBP9TzzIechKqOuFEgEQL/ggCg9aXgPgKZWKdIsRNnsuOM5lisb0cAoPyh
PK4Y6I9/36a+Uoq2FlH3KUoM
=8YF3
-----END PGP SIGNATURE-----
The Plankmeister wrote:
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.


Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.