ASP and Access, problem with LIKE statement

#1 (**permalink**) 28-Jul-2006, 08:41 AM

Hi,

I have an ASP page which uses a LIKE statement to get products back.
The syntax as far as I can tell is near perfect but it just doesn't
return any records, grrrr.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/13925-asp-and-access-problem-like-statement.html

By running it directly in Access (Using * in place of %) it works like
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13925
a charm.

I am using Access 2003, my connection string is:
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
strDabaseName & ";"

The SQL String comes out as:
Select * from tbl_products Where name like '%Roadster%' And active = 1
order by name

Any ideas?

Cheers

#2 (**permalink**) 28-Jul-2006, 08:41 AM

There's nothing visibly wrong with that SQL statement. 'Name' is a reserved
word, and using it as a field name can cause problems, but those problems
generally result in an error message. You might want to put square brackets
around the two instances of 'name' in the SQL statement. I don't expect it
to solve this problem, but it may prevent other problems in the future ...

Select * from tbl_products Where [name] like '%Roadster%' And active = 1
order by [name]

Have you tested the SQL statement without the second part of the WHERE
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13925
clause ('And active = 1')? Does the following SQL statement return any
records? ...

Select * from tbl_products Where [name] like '%Roadster%' order by [name]

--
Brendan Reynolds
Access MVP

wrote in message
news:1153863598.607902.327400@i42g2000cwa.googlegr oups.com...
> Hi,
>
> I have an ASP page which uses a LIKE statement to get products back.
> The syntax as far as I can tell is near perfect but it just doesn't
> return any records, grrrr.
>
>
> By running it directly in Access (Using * in place of %) it works like
> a charm.
>
>
> I am using Access 2003, my connection string is:
> connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
> strDabaseName & ";"
>
>
> The SQL String comes out as:
> Select * from tbl_products Where name like '%Roadster%' And active = 1
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13925
> order by name
>
>
> Any ideas?
>
>
> Cheers
>

#3 (**permalink**) 28-Jul-2006, 08:41 AM

Brendan Reynolds wrote:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13925
> There's nothing visibly wrong with that SQL statement. 'Name' is a reserved
> word

NAME is a reserved word in Access but not a reserved word in Jet 4.0,
standard SQL, ODBC etc.

Jamie.

--

#4 (**permalink**) 28-Jul-2006, 08:42 AM

It is safer however to allways prefix Fields, and stay away from "dangerous"
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13925
names

ie SELECT A.NAME FROM MYTABLE A

Pieter

"Jamie Collins" wrote in message
news:1153919361.746534.176120@75g2000cwc.googlegro ups.com...
>
> Brendan Reynolds wrote:
>> There's nothing visibly wrong with that SQL statement. 'Name' is a
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13925
>> reserved
>> word
>
> NAME is a reserved word in Access but not a reserved word in Jet 4.0,
> standard SQL, ODBC etc.
>
> Jamie.
>
> --
>

--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4367 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len

#5 (**permalink**) 28-Jul-2006, 08:42 AM

Pieter Wijnen wrote:
> It is safer however to allways prefix Fields, and stay away from "dangerous"
> names
>
> ie SELECT A.NAME FROM MYTABLE A

In you example, A is a 'correlation name' or 'alias' to use the
vernacular. I think of 'prefix' as being something quite different e.g.
a representative term or qualifier in a data element name (e.g. the
'last' in 'last_name') or possibly less salubrious connotations e.g.
Hungarian notation to indicate aspects the physical implementation or
the Access UI. Semantic, yes, but then we are discussing SQL syntax

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13925

I agree that if you consider a name to be dangerous then you should
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13925
avoid it, as would I. I consider 'Name' to be vague (e.g. missing a
representative term or qualifier) rather than dangerous.

I do not consider that using a table correlation name makes a dangerous
column name less dangerous.

I don't agree that a correlation name should *always* be used. For
example:

DELETE
FROM MYTABLE AS A;

According to the SQL-92 standard, this should materialize a new table,
remove all its rows then disappear, leaving MYTABLE untouched. As we
know, Access/Jet violates the standards, i.e. all rows will be removed
from MYTABLE, but that's no excuse to write non-standard SQL when the
standard syntax is also supported.

However, for vanilla SELECT queries I agree that always using a
correlation name is a good habit to get into, even for one-table
queries (e.g. makes life easier when you need to change it to a
two-table query). I like the idea of specifying each table's
correlation name in the data dictionary for consistency but admit I
usually just use a single letter (not always consistent between
queries) with an incremental number based on nesting e.g. T1, T2, etc.

Jamie.

--

#6 (**permalink**) 28-Jul-2006, 08:42 AM

The A.NAME contains a prefix though.
DELETE FROM MYTABLE does not include fields, ergo no aliasing needed/wanted
anyway.
I Always Alias my tables in SELECT queries for readability/editability of
the SQL,
as the GUI version of the Query builder tends to break a lot of my Queries
(the dreaded 'Query to complex')
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13925

Check this kind of SQL's before & After you've made an edit in the GUI:

SELECT ....
FROM ...
WHERE (A.ID=0 OR A.ID=Forms!x!AID)
AND (B.ID=0 OR B.ID=Forms!x!BID)
AND (C.ID=0 OR C.ID=Forms!x!CID)
etc

SELECT ....
FROM ...
WHERE EXISTS (SELECT 'X' .....)

SELECT ....
FROM ...
WHERE NOT EXISTS (SELECT 'X' .....)

And, Yes as You I Use "Standarized" Aliases too, and is also not 100%
consistent about it
I think therefore we are pretty much on the same level regarding the way SQL
should look

Pieter

"Jamie Collins" wrote in message
news:1153986408.804034.52710@i3g2000cwc.googlegrou ps.com...
>
> Pieter Wijnen wrote:
>> It is safer however to allways prefix Fields, and stay away from
>> "dangerous"
>> names
>>
>> ie SELECT A.NAME FROM MYTABLE A
>
> In you example, A is a 'correlation name' or 'alias' to use the
> vernacular. I think of 'prefix' as being something quite different e.g.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13925
> a representative term or qualifier in a data element name (e.g. the
> 'last' in 'last_name') or possibly less salubrious connotations e.g.
> Hungarian notation to indicate aspects the physical implementation or
> the Access UI. Semantic, yes, but then we are discussing SQL syntax

>
> I agree that if you consider a name to be dangerous then you should
> avoid it, as would I. I consider 'Name' to be vague (e.g. missing a
> representative term or qualifier) rather than dangerous.
>
> I do not consider that using a table correlation name makes a dangerous
> column name less dangerous.
>
> I don't agree that a correlation name should *always* be used. For
> example:
>
> DELETE
> FROM MYTABLE AS A;
>
> According to the SQL-92 standard, this should materialize a new table,
> remove all its rows then disappear, leaving MYTABLE untouched. As we
> know, Access/Jet violates the standards, i.e. all rows will be removed
> from MYTABLE, but that's no excuse to write non-standard SQL when the
> standard syntax is also supported.
>
> However, for vanilla SELECT queries I agree that always using a
> correlation name is a good habit to get into, even for one-table
> queries (e.g. makes life easier when you need to change it to a
> two-table query). I like the idea of specifying each table's
> correlation name in the data dictionary for consistency but admit I
> usually just use a single letter (not always consistent between
> queries) with an incremental number based on nesting e.g. T1, T2, etc.
>
> Jamie.
>
> --
>

#7 (**permalink**) 28-Jul-2006, 08:42 AM

The A.NAME contains a prefix though.
DELETE FROM MYTABLE does not include fields, ergo no aliasing needed/wanted
anyway.
I Always Alias my tables in SELECT queries for readability/editability of
the SQL,
as the GUI version of the Query builder tends to break a lot of my Queries
(the dreaded 'Query to complex')

Check this kind of SQL's before & After you've made an edit in the GUI:

SELECT ....
FROM ...
WHERE (A.ID=0 OR A.ID=Forms!x!AID)
AND (B.ID=0 OR B.ID=Forms!x!BID)
AND (C.ID=0 OR C.ID=Forms!x!CID)
etc

SELECT ....
FROM ...
WHERE EXISTS (SELECT 'X' .....)

SELECT ....
FROM ...
WHERE NOT EXISTS (SELECT 'X' .....)

And, Yes as You I Use "Standarized" Aliases too, and is also not 100%
consistent about it
I think therefore we are pretty much on the same level regarding the way SQL
should look

Pieter

"Jamie Collins" wrote in message
news:1153986408.804034.52710@i3g2000cwc.googlegrou ps.com...
>
> Pieter Wijnen wrote:
>> It is safer however to allways prefix Fields, and stay away from
>> "dangerous"
>> names
>>
>> ie SELECT A.NAME FROM MYTABLE A
>
> In you example, A is a 'correlation name' or 'alias' to use the
> vernacular. I think of 'prefix' as being something quite different e.g.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13925
> a representative term or qualifier in a data element name (e.g. the
> 'last' in 'last_name') or possibly less salubrious connotations e.g.
> Hungarian notation to indicate aspects the physical implementation or
> the Access UI. Semantic, yes, but then we are discussing SQL syntax

>
> I agree that if you consider a name to be dangerous then you should
> avoid it, as would I. I consider 'Name' to be vague (e.g. missing a
> representative term or qualifier) rather than dangerous.
>
> I do not consider that using a table correlation name makes a dangerous
> column name less dangerous.
>
> I don't agree that a correlation name should *always* be used. For
> example:
>
> DELETE
> FROM MYTABLE AS A;
>
> According to the SQL-92 standard, this should materialize a new table,
> remove all its rows then disappear, leaving MYTABLE untouched. As we
> know, Access/Jet violates the standards, i.e. all rows will be removed
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13925
> from MYTABLE, but that's no excuse to write non-standard SQL when the
> standard syntax is also supported.
>
> However, for vanilla SELECT queries I agree that always using a
> correlation name is a good habit to get into, even for one-table
> queries (e.g. makes life easier when you need to change it to a
> two-table query). I like the idea of specifying each table's
> correlation name in the data dictionary for consistency but admit I
> usually just use a single letter (not always consistent between
> queries) with an incremental number based on nesting e.g. T1, T2, etc.
>
> Jamie.
>
> --
>

--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4367 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len