Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

proper sql syntax for recordset select code

Status
Not open for further replies.

ringadeal

Technical User
Jan 16, 2008
67
US
what is the proper syntax for the following query:

Code:
colours.Source = "SELECT productfam, productid_img, ColourAtt1, ColourAtt2, ColourAtt3, sub, FROM images  WHERE productid_img = MMColParam OR (ProductFam=1) OR (sub=10)  AND  (Not IsNull(ColourAtt1)or Not IsEmpty(ColourAtt2))  ORDER BY sort_img_prd DESC"
 
Depends on what you are trying to accomplish. E.g., you might need parenthesis to group some the clauses in your where clause.

You are using ISNULL incorrectly and it might not be what you want anyway.

Try: ColourAtt1 IS NOT NULL

I don't think ISEMPTY is a T-SQL function. Try: ColourAtt2 <> ""

Why are you looking for nulls in one case and empty in another?

If empty and null should give the same result, then use ISNULL like this:

ISNULL(ColourAtt2, "") <> ""

 
the following query is bringing me a syntax error:
Code:
colours.Source = "SELECT url_img, alt_img, sort_img_prd, productfam, productid_img, ColourAtt1, ColourAtt2, ColourAtt3, sub, desc_fam, title_fam  FROM images  WHERE (productid_img = MMColParam OR ProductFam=1 OR sub=10)  AND  (ColourAtt1 IS NOT NULL) AND (ColourAtt1 <> "")  ORDER BY sort_img_prd DESC"
The reason I'm trying both IS NOT NULL and <>"" is because I don't know how to tell which one applies over here.

Thanks for your help!
 
Is MMColParam a VBScript variable? Then use

WHERE (productid_img = '" & replace(MMColParam, "'","''") & "' OR etc.

Also, check if "sub" is a reserved word in SQL. It will still work as a field name, but can cause problems in certain circumstances.
 
NULL means undetermined. "" means a blank string. They are not the same thing.

If you try to compare something to a NULL (even another NULL) you will get NULL for an answer--undetermined. Whereas comparing "" to another string will alway get you true or false.

My guess is that you should use:

ISNULL(ColourAtt1, "") <> ""

That picks up both nulls and empty strings by converting NULL to "".

But watch out when using ISNULL with different datatypes. You might have to use CAST to get it to work. See previous post here:

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top