Hi all,

I am trying to use a wildcard character as the value associated with the "Any" option in a select box for a year value. My code works fine when I choose an actual value ("2002" or "2003"). However, when I choose "Any" I get the empty recordset error:

"ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."



Here is my code:
In the source script for the search script (method=post and calls my search script:

...
<td>
<select name="year">
<option selected value="*">Any</option>
<option value="2002">2002</option>
<option value="2003">2003</option>
</select>
</td>
...

On the search script I call a parameterized query that I created within Access:

...
'***************** Setup the Command Object for this query ********************************
objCommand.ActiveConnection = connDB
objCommand.CommandText = "qrySelectCalendar"
objCommand.CommandType = adCmdStoredProc
objCommand.Parameters.Append _
  objCommand.CreateParameter("BRAND_ID", adInteger, adParamInput, 0, numBrand)
objCommand.Parameters.Append _
  objCommand.CreateParameter("YEAR_NUM", adVariant, adParamInput, 0, Request.Form("year"))

Set recordSet = objCommand.Execute
...


Within Access, in the query design grid, for the criteria for the "Year" column, I have "LIKE [YEAR_NUM]".

When I run this query in Access and use "*" as the parameter value in the popup dialog the query runs fine.


Does anyone have any ideas as to why my query is failing when called within an ASP script? Let me know if I left out any info you might need. Thank you for any help you can provide.

Well,

I believe I found the problem. I changed the wildcard character passed by the <select> "statement" from "*" to "%". This appears to be working though I will have to double check the results against the database.

From my testing, when running the query in MS Access, the asterisk will work as a wild-card, but the percent-sign returns an empty set.
However, passing the parameters in the ASP script, the percent-sign works as a wild-card, while the asterisk does not.

The MS Access (2000 btw) help file for "Using Wildcard Characters in String Comparisons" indicates that these two symbols are equivalent when used in "Like" comparisons. Anyone know why these two symbols would behave as I have described above?

As near as I can tell, in the access program, you are using dao and the * is the wildcard character.

In asp, you are using ado. The % is the wild card character. The % is the sql standard.

This is also true in writing VB programs. If you are using dao, you have to use the *. if ado, you have to use %

That makes sense. However, the ASP code is passing these parameters to a query that was setup in MS Access. So, I am essentially just passing values to a stored procedure in Access. Perhaps Access is doing some conversions before passing the arguments to and executing the query? So, it see's a "*" coming from an ASP call and converts it to something else (not necessarily "%", just something that doesn't work). When it see's the "%" coming from an ASP script it "knows" that the call is asking for a wildcard and converts it to a "*" before executing the procedure?


Oh well, it is not too important since the program is actually working, but I am still curious as to what is going on under the hood :)

The Jet OLEDB Provider supports ANSI SQL, which means it requires the % operator. The older ODBC driver supported Jet SQL, which means you can use the * operator instead. Internally, the Jet Engine uses Jet SQL which is why * is working inside Access.

Cheers
Ken

www.adOpenStatic.com