Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    41

    Unanswered: SQL SELECT statement problem

    I am using the following statement in ASP and trying to use the variable PLANT in a select statement connecting to an Access Database.

    rs.Open "SELECT * FROM plant_list WHERE grp_name = "plant" and available = True ORDER BY name", conn

    The statement as above generates the following:

    "Microsoft VBScript compilation error '800a0401'

    Expected end of statement "

    I know this is because of the quotations around the variable plant. I have tried other ways, such as, (plant), ('plant'), ""plant"", and more. Can't seem to find the right syntax.

    Any help is appreciated.

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    okie your line is....

    Code:
    rs.Open "SELECT * FROM plant_list WHERE grp_name = "plant" and available = True ORDER BY name", conn
    try

    Code:
    rs.Open "SELECT * FROM plant_list WHERE grp_name = '" & plant & "' and available = True ORDER BY name", conn
    or

    Code:
    rs.Open "SELECT * FROM plant_list WHERE grp_name = """ & plant & """ and available = True ORDER BY name", conn
    The first one should be better then the second though....

  3. #3
    Join Date
    Feb 2004
    Posts
    41

    Smile

    Thank you Rokslide!

    The first one worked. I had tried that syntax with the DOUBLE quote, but not adding the single quotes too.

    Again, thanks!

    While you are here...

    What can I do to get this statement to realize that 8" should be listed before 10" in a sort? It thinks 10 is first because the number 1 < 8

    Don't know if there is a simple answer for this. But if you have one, thanks.

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Assuming you are using MS SQL as your back end database, use the Convert or Cast statement to change it to an int.

    Currently it thinks it is a character value, that's why you get the strange sort order.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •