Problem passing empty form fields to access parameter query

I have seen a lot of good discussions concerning parameter queries in
Access2000 and how to pass parameters from forms in ASP. I have tried
many of the solutions but I can't get my application to work.

I hope that someone can straight out my problems below.

I have the following query in Access 2000;

SELECT measure.txt_measurement, measure.nr_value AS [value]
FROM measure
WHERE (((measure.txt_measurement) Like "*" & [measure] & "*") AND
((measure.nr_value)>[param_minvalue])) OR ((([measure]) Is Null) AND
(([param_minvalue]) Is Null));

The query should return all records if the parameters are empty.This
works fine in Access.

I send the parameters from an ASP form page and want to return the
recordset with the following code

<%
dim measure
measure = ""
if Request("measurement")<>"" then measure = Request("measurement")

dim param_minvalue
param_minvalue = ""
if Request("minvalue")<>"" then param_minvalue = Request("minvalue")


set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_market_STRING
Command1.CommandText = "query_test_parameter '" & measure & "', " &
param_minvalue
Command1.CommandType = 4
Command1.CommandTimeout = 0
Command1.Prepared = true
set bktst = Command1.Execute
bktst_numRows = 0

%>

Of course other code comes after this, but I assume this part is what
is doing the job.

-------The problem-----------

When I try to run the ASP-page, I get the following error.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in parameters
clause. Make sure the parameter exists and that you typed its value
correctly.

However, if I change the row of

param_minvalue = ""

to

param_minvalue = "0"

or make sure that the number parameter contains any number, the
parameters are passed correctly and runs properly.

Obviously the param_minvalue "get lost" on the way, and is not
delivered as Null to the database.

What am I doing wrong?

Bjorn

Re: Problem passing empty form fields to access parameter query

wrote in message
news:1170103162.670755.171930@a34g2000cwb.googlegroups.com.. .
>I have seen a lot of good discussions concerning parameter queries in
> Access2000 and how to pass parameters from forms in ASP. I have tried
> many of the solutions but I can't get my application to work.
>
> I hope that someone can straight out my problems below.
>
> I have the following query in Access 2000;
>
> SELECT measure.txt_measurement, measure.nr_value AS [value]
> FROM measure
> WHERE (((measure.txt_measurement) Like "*" & [measure] & "*") AND
> ((measure.nr_value)>[param_minvalue])) OR ((([measure]) Is Null) AND
> (([param_minvalue]) Is Null));
>
> The query should return all records if the parameters are empty.This
> works fine in Access.
>
> I send the parameters from an ASP form page and want to return the
> recordset with the following code
>
> <%
> dim measure
> measure = ""
> if Request("measurement")<>"" then measure = Request("measurement")
>
> dim param_minvalue
> param_minvalue = ""
> if Request("minvalue")<>"" then param_minvalue = Request("minvalue")
>
>
> set Command1 = Server.CreateObject("ADODB.Command")
> Command1.ActiveConnection = MM_market_STRING
> Command1.CommandText = "query_test_parameter '" & measure & "', " &
> param_minvalue
> Command1.CommandType = 4
> Command1.CommandTimeout = 0
> Command1.Prepared = true
> set bktst = Command1.Execute
> bktst_numRows = 0
>
> %>
>
> Of course other code comes after this, but I assume this part is what
> is doing the job.
>
> -------The problem-----------
>
> When I try to run the ASP-page, I get the following error.
>
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in parameters
> clause. Make sure the parameter exists and that you typed its value
> correctly.
>
> However, if I change the row of
>
> param_minvalue = ""
>
> to
>
> param_minvalue = "0"
>
> or make sure that the number parameter contains any number, the
> parameters are passed correctly and runs properly.
>
> Obviously the param_minvalue "get lost" on the way, and is not
> delivered as Null to the database.
>
> What am I doing wrong?
>

Nothing to do with your problem, but you should really try to wean yourself
off this horrid Dreamweaver generated ADO code. For one thing, Dreamweaver
seems to push you to using the deprecated ODBC driver (if your error message
is anyting to go by).

Try this, which will shortcut most of the bloat produced by DW, and also
remove the need to delimit the parameter values (which is where I believe
your problem arises)

<%
dim measure, param_minvalue
'Don't initialise them to empty strings
if Request("measurement")<>"" then measure = Request("measurement")
if Request("minvalue")<>"" then param_minvalue = Request("minvalue")
'Request.Form? Request.QueryString? Specify.

Set conn = Server.CreateObject("ADODB.Connection")
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Server.Mappath("path_to_database")

Set rs = Server.CreateObject("ADODB.Recordset")
conn.Open connstring
conn.query_test_parameter measure, param_minvalue, rs
%>

Your resultset will be returned in the recordset rs.

If you want to use other than the default cursors (for paging through a
disconnected recordset, eg) you can do so before passing the recordset as
the final parameter to the saved query.

--
Mike Brind

Re: Problem passing empty form fields to access parameter query

"Mike Brind" wrote in message
news:OEFRpC$QHHA.1200@TK2MSFTNGP02.phx.gbl...
>


>> However, if I change the row of
>>
>> param_minvalue = ""
>>
>> to
>>
>> param_minvalue = "0"
>>
>> or make sure that the number parameter contains any number, the
>> parameters are passed correctly and runs properly.
>>
>> Obviously the param_minvalue "get lost" on the way, and is not
>> delivered as Null to the database.
>>
>> What am I doing wrong?

One other thing, param_minvalue is never going to be null.

<% param_minvalue="" %>

stops it being null. It now has a value - that of an empty string.