Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

ASP and Access, problem with LIKE statement

Discussion in 'Information Technology' started by signups@creativejam.co.uk, Jul 28, 2006.

  1. signups@creativejam.co.uk

    signups@creativejam.co.uk
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Sikhism Sikhism In Iran – A Part Of Cultural Diaspora Sikh Sikhi Sikhism May 18, 2016
    Sikhism Sikhs say Giving only One Aspect of Sikh Faith Sikh Sikhi Sikhism Jan 7, 2016
    How Asperger's reignited a passion for art (interview with Raj Singh Tattal) Face to Face Apr 2, 2014
    S Asia Temples, Gurdwaras targeted in Pakistan over alleged blasphemy Breaking News Mar 17, 2014
    Political aspects of Sikh Dharam Essays on Sikhism Jan 29, 2014

  3. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    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]

    --
    Brendan Reynolds
    Access MVP

    <signups@creativejam.co.uk> wrote in message
    news:1153863598.607902.327400@i42g2000cwa.googlegroups.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
    > order by name
    >
    >
    > Any ideas?
    >
    >
    > Cheers
    >
     
  4. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Brendan Reynolds wrote:
    > 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.

    --
     
  5. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

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

    ie SELECT A.NAME FROM MYTABLE A

    Pieter

    "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    news:1153919361.746534.176120@75g2000cwc.googlegroups.com...
    >
    > Brendan Reynolds wrote:
    >> 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.
    >
    > --
    >




    --
    ----------------------------------------
    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
     
  6. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    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 :)

    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. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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

    "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    news:1153986408.804034.52710@i3g2000cwc.googlegroups.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.
    > 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.
    >
    > --
    >
     
  8. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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

    "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    news:1153986408.804034.52710@i3g2000cwc.googlegroups.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.
    > 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.
    >
    > --
    >




    --
    ----------------------------------------
    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
     

Share This Page