ASP and Access, problem with LIKE statement

[signups]

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
order by name


Any ideas?


Cheers

 

[Brendan Reynolds]

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
clause ('And active = 1')? Does the following SQL statement return any
records? ...

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

 

[Jamie Collins]

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.

--

 

[Pieter Wijnen]

It is safer however to allways prefix Fields, and stay away from "dangerous"
names

ie SELECT A.NAME FROM MYTABLE A

Pieter

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

Jamie.

--

 

[Jamie Collins]

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

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.

--

 

[Pieter Wijnen]

The A.NAME contains a prefix though. <g>
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 <g>
I think therefore we are pretty much on the same level regarding the way SQL
should look

Pieter

 

[Pieter Wijnen]

The A.NAME contains a prefix though. <g>
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 <g>
I think therefore we are pretty much on the same level regarding the way SQL
should look

Pieter

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

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.

--

 

[Larry Linson]

"Name" is an Access reserved word. It is "dangerous" in the sense that it
may be confusing, either to Access or to the developer, and, thus, should be
avoided.

MVP Allen Browne has both a downloadable list of reserved words on his site,
if I recall correctly, at http://allenbrowne.com/AppIssueBadWord.html. My
other recollection is that he does not claim that is a _complete_ list of
reserved words.

Larry Linson
Microsoft Access MVP