Re: Problem passing empty form fields to access parameter query


<bjornkamlin@xxxxxxxxxxxxxx> wrote in message
news:1170103162.670755.171930@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: access 2003
    ... Dim ctl As Control ... Dim rs As Recordset ... This sets the query definitions for choosing data to create an invoice using ... Event on combo box: Private Sub ChooseCust_AfterUpdate ...
    (microsoft.public.access.conversion)
  • Re: Query Problem in Test.
    ... You've declared rs as an ADO Recordset, yet you're trying to use DAO to ... Dim paramMiles As ADODB.Parameter ... query works in the query designer. ... MsgBox "No Churches meet your criteria.", vbInformation, ...
    (microsoft.public.access.formscoding)
  • Re: Need help with Code Please!!!
    ... putting the query SQL itself in here maybe it will help you understand what I ... the actual string that gets built at the end of the SQ1 build process. ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ... Dim qd As QueryDef 'object ref to query item ...
    (microsoft.public.access.formscoding)
  • Re: Exporting data from continuous form to Excel spreadsheet
    ... I have overcome it by creating a Make Table Query. ... You can also use excel's copy from recordset method with a recordset. ... Dim sqlStatement As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Newbie VBA question: Opening parameter queries from code
    ... Dim rs as DAO.Recordset ... But then what are you going to do with that recordset? ... How do I actually run the query? ... is there a way I could directly execute an SQL statement in my ...
    (comp.databases.ms-access)
Loading