> I have a SELECT statement:

> "SELECT * FROM Customers WHERE Address LIKE " & Chr(34) & "*" &
strVariable
> & "*" &  Chr(34)

> - that returns records as expected when the resulting statement (e.g.
> "SELECT * FROM Customers WHERE Address Like "*PO Box*") is pasted into a
> query designer.

> When run in an ASP page, however, I get EOF - nothing.  If I
response.write
> the SQL statement out to the page and paste the results into a database
> query statement, it returns the records expected.

> Any ideas on why this is happening would be greatly appreciated.

> I have a SELECT statement:

> "SELECT * FROM Customers WHERE Address LIKE " & Chr(34) & "*" &
strVariable
> & "*" &  Chr(34)

> - that returns records as expected when the resulting statement (e.g.
> "SELECT * FROM Customers WHERE Address Like "*PO Box*") is pasted into a
> query designer.

> When run in an ASP page, however, I get EOF - nothing.  If I
response.write
> the SQL statement out to the page and paste the results into a database
> query statement, it returns the records expected.

> Any ideas on why this is happening would be greatly appreciated.

> You need to use % instead of *, ado uses more normal syntax, you're not
> using Access so don't need Acces' syntax. Secondly I would use a single
> quote to wrap the string:
> SELECT * FROM Customers WHERE Address Like '%PO Box%'

> Joe


> > I have a SELECT statement:

> > "SELECT * FROM Customers WHERE Address LIKE " & Chr(34) & "*" &
> strVariable
> > & "*" &  Chr(34)

> > - that returns records as expected when the resulting statement (e.g.
> > "SELECT * FROM Customers WHERE Address Like "*PO Box*") is pasted into a
> > query designer.

> > When run in an ASP page, however, I get EOF - nothing.  If I
> response.write
> > the SQL statement out to the page and paste the results into a database
> > query statement, it returns the records expected.

> > Any ideas on why this is happening would be greatly appreciated.

> Exactly, so, Define a variable and make that variable = to your SQL string
> and then use the variable to make the SQL call.

> > I have a SELECT statement:

> > "SELECT * FROM Customers WHERE Address LIKE " & Chr(34) & "*" &
> strVariable
> > & "*" &  Chr(34)

> > - that returns records as expected when the resulting statement (e.g.
> > "SELECT * FROM Customers WHERE Address Like "*PO Box*") is pasted into a
> > query designer.

> > When run in an ASP page, however, I get EOF - nothing.  If I
> response.write
> > the SQL statement out to the page and paste the results into a database
> > query statement, it returns the records expected.

> > Any ideas on why this is happening would be greatly appreciated.

> It is an Access database.


> > You need to use % instead of *, ado uses more normal syntax, you're not
> > using Access so don't need Acces' syntax. Secondly I would use a single
> > quote to wrap the string:
> > SELECT * FROM Customers WHERE Address Like '%PO Box%'

> > Joe


> > > I have a SELECT statement:

> > > "SELECT * FROM Customers WHERE Address LIKE " & Chr(34) & "*" &
> > strVariable
> > > & "*" &  Chr(34)

> > > - that returns records as expected when the resulting statement (e.g.
> > > "SELECT * FROM Customers WHERE Address Like "*PO Box*") is pasted into
a
> > > query designer.

> > > When run in an ASP page, however, I get EOF - nothing.  If I
> > response.write
> > > the SQL statement out to the page and paste the results into a
database
> > > query statement, it returns the records expected.

> > > Any ideas on why this is happening would be greatly appreciated.

> I may have mis-stated the problem.  Here's the essence of the routine:
> '=================================================
> Sub WriteRecords()
> dim cnStr
> Dim cn
> Dim rs
> Dim strH
> Dim strVariable
> strVariable = Request.QueryString("AddrLike")
> Path = Server.MapPath("Customers.mdb")
> cnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path &";Jet
> OLEDB:Database Password=PWD;admin;"
> Set cn = server.CreateObject("ADODB.Connection")
> cn.Open cnStr
> set RS = server.CreateObject("ADODB.Recordset")
> SQL = ""SELECT * FROM Customers WHERE Address LIKE " & Chr(34) & "*" &
SQL
> = SQL & strVariable & "*" &  Chr(34)
> rs.Open SQL,cn,3,3
> Do Until rs.EOF
>     strH = rs("CustomerName")
>      rs.MoveNext
> Loop
> rs.Close
> Set rs = nothing
> cn.close
> set cn = nothing
> response.write strH
> End Sub
> '==============================
> The value of the Varaible SQL equates to something like SELECT * FROM
> Customers WHERE Address Like "*PO Box*".  This SQL Statement, pasted as
such
> into the query designer of the actual database returns records (yes, I'm
> using Access on the web server).  When run by this asp page, however, I
get
> nothing.  All other types of queries appear to run as expected except such
> queries where I'm using Access's "*" wildcard character in the criteria.
> Yes, if I move the data to SQL Server and change the double quotes to
> singles and the wildcard character to % it works fine, but this is not an
> option on this clients' web server.  It would appear that the SQL
statement
> is not being parsed acurately when passed in the rs.Open argument to the
> OLEDB provider by IIS.

> > Exactly, so, Define a variable and make that variable = to your SQL
string
> > and then use the variable to make the SQL call.

> > > I have a SELECT statement:

> > > "SELECT * FROM Customers WHERE Address LIKE " & Chr(34) & "*" &
> > strVariable
> > > & "*" &  Chr(34)

> > > - that returns records as expected when the resulting statement (e.g.
> > > "SELECT * FROM Customers WHERE Address Like "*PO Box*") is pasted into
a
> > > query designer.

> > > When run in an ASP page, however, I get EOF - nothing.  If I
> > response.write
> > > the SQL statement out to the page and paste the results into a
database
> > > query statement, it returns the records expected.

> > > Any ideas on why this is happening would be greatly appreciated.

> I may have mis-stated the problem.

> The value of the Varaible SQL equates to something like SELECT * FROM
> Customers WHERE Address Like "*PO Box*".  This SQL Statement, pasted as such
> into the query designer of the actual database returns records (yes, I'm
> using Access on the web server).

>  When run by this asp page, however, I get
> nothing.

> Yes, if I move the data to SQL Server and change the double quotes to
> singles and the wildcard character to % it works fine,

> > I may have mis-stated the problem.

> No, you've misunderstood the solution.

> [...]
> > The value of the Varaible SQL equates to something like SELECT * FROM
> > Customers WHERE Address Like "*PO Box*".  This SQL Statement, pasted as
such
> > into the query designer of the actual database returns records (yes, I'm
> > using Access on the web server).

> Which uses * as a wildcard character.

> >  When run by this asp page, however, I get
> > nothing.

> Indeed. This is because, as has been previously stated, ADO uses % as a
> wildcard character. The fact that the underlying database is Access is
> irrelevant; you are using ADO to access the data, and thus must use % as
> a wildcard.

> [...]
> > Yes, if I move the data to SQL Server and change the double quotes to
> > singles and the wildcard character to % it works fine,

> Why the heck would you need to involve SQL Server? Follow standard SQL
> rules, but keep Access as the database.

> i.e. the *only* part of your code/setup that needs changing is the SQL
> statement. Use single quotes and % and ADO will be delighted to return
> you data *from* *your* *Access* *database*.

> It'll probably even work if you keep the double quotes. You only need to
> change *two* *characters* in your code. That's it. You certainly don't
> need to use SQL Server. Just change the wildcard to the one ADO is
> expecting (and, incidentally, the one that follows the agreed
> international standards for SQL, which Access doesn't).

> hth

> Adam
> --
> Every program has at least one bug and can be shortened by at least one
> instruction -- from which, by induction, one can deduce that every
> program can be reduced to one instruction which doesn't work.

> Thanks a bunch.