Multiple Conditions and BETWEEN

[ehambrey]

Hi every one I have written the bellow statement and when executed it returns no rows when a third condition is added e.g. the between statement.

If I take one the conditions out (this could be one of the = or the between) it returns rows form the database. The trouble is I need the retuned rows to be sorted by all three conditions not just the two = or an = and the between.

If anyone could help would be great!

Thanks edd

("SELECT MANUFACTURES.MANUFACTURES_NAME, PRODUCT.PRODUCT_ID, PRODUCT.PRODUCT_NAME, PRODUCT.PRODUCT_PICTURE, PRODUCT.PRODUCT_PRICE, PRODUCT.PRODUCT_STOCK FROM [SUBCATERGORY] INNER JOIN [PRODUCT] ON SUBCATERGORY.SUBCATERGORY_ID = PRODUCT.SUBCATERGORY_ID INNER JOIN [MANUFACTURES] ON PRODUCT.MANUFACTURES_ID = MANUFACTURES.MANUFACTURES_ID WHERE SUBCATERGORY.SUBCATERGORY_ID = '" & Replace(Request.QueryString("SubCatergory"), "'", "''") & " ' AND MANUFACTURES.MANUFACTURES_ID = '" & Replace(Request.QueryString("Manufcatures"), "'", "''") & " ' AND PRODUCT.PRODUCT_PRICE BETWEEN '" & Replace(Request.QueryString("Low"), "'", "''") & "' AND '" & Replace(Request.QueryString("High"), "'", "''") & " ' ORDER BY PRODUCT.PRODUCT_PRICE DESC", conn)

 

[r937]

could you please show the query that is sent to the database, i.e. after those variables have been substituted

r937.com | rudy.ca

 

[ehambrey]

How about this?

"SELECT * FROM SUBCATERGORY INNER JOIN PRODUCT ON SUBCATERGORY.SUBCATERGORY_ID = PRODUCT.SUBCATERGORY_ID INNER JOIN MANUFACTURES ON PRODUCT.MANUFACTURES_ID = MANUFACTURES.MANUFACTURES_ID WHERE SUBCATERGORY.SUBCATERGORY_ID = "321311" AND MANUFACTURES.MANUFACTURES_ID = "1232132" AND PRODUCT.PRODUCT_PRICE BETWEEN "213213" AND "5454554" ORDER BY PRODUCT.PRODUCT_PRICE DESC

 

[r937]

other than the fact that the double quotes should be single quotes, the query looks okay

if this is microsoft access (you didn't say, and of course microsoft access sql is different from ANSI SQL), you need to parenthesize your joins

...  FROM ( SUBCATERGORY 
    INNER JOIN PRODUCT 
       ON SUBCATERGORY.SUBCATERGORY_ID 
        = PRODUCT.SUBCATERGORY_ID )  
    INNER JOIN MANUFACTURES 
       ON PRODUCT.MANUFACTURES_ID 
        = MANUFACTURES.MANUFACTURES_ID

r937.com | rudy.ca

 

[ehambrey]

Hi,

Ok thanks for your help but i still get the same problem, i will look in to it and get back to you on this space if anything comes up once again thanks.

edd

 

[ehambrey]

Hi,

After your advice that the SQL was ok, i went back through my page code and app code and found a small type - o in the code. Everything works fine now

Many Thanks