Loop Problem

1. Junior Member
Join Date
Dec 1969
Posts
11

## Loop Problem

The purpose of the loop is to form a valid SQL statement such as <BR>"SELECT * FROM Customers WHERE country = &#039;US&#039; AND country = &#039;UK&#039; AND country = &#039;IE&#039;". <BR>It takes in a Querystring for the number of countries (no) and a querystring for every country (c1,c2,c3,c4 etc). I use an If statement to see which is the last country and remove the and after that clause.<BR><BR><BR><BR>dim clauses,SQLand,num<BR><BR>num = Request.QueryString("no")<BR><BR>for i = 1 to num<BR>If i = num Then<BR>SQLand = ""<BR>Else SQLand = " AND "<BR>End If<BR>clauses = clauses & "county = &#039;" & Request.QueryString("c"&i) & "&#039;" & SQLand<BR>next<BR><BR>The problem is, the last clause has an AND after it making it an invalid SQL statement..<BR><BR>"SELECT * FROM Customers WHERE country = &#039;US&#039; AND country = &#039;UK&#039; AND country = &#039;IE&#039; AND ". <BR><BR>It seems to work when I hardcode the number of countries instead of using a querystring to pass in the number of countries even though when I Response.write the number of countries it&#039;s always the correct number..<BR><BR><BR>Thanks for any help

2. Senior Member
Join Date
Dec 1969
Location
Los Angeles, CA
Posts
21,192

## Ill teach you a little trick

SELECT * FROM Customers WHERE 1=1<BR><BR>then you loop<BR><BR>clauses = clauses & " and county = &#039;" & Request.QueryString("c"&i) & "&#039;"<BR><BR><BR>

3. Junior Member
Join Date
Dec 1969
Posts
17

## RE: Loop Problem

I was just wondering if the select statement actually returns any customers. Shouldn&#039;t you use "or" instead of "and"?

4. Senior Member
Join Date
Dec 1969
Location
Los Angeles, CA
Posts
21,192

## eh!!

so true.....totally missed that one<BR><BR><BR>

5. Junior Member
Join Date
Dec 1969
Posts
11

## RE: Loop Problem

Yes, it returns all the customers from a region containing several countries so I use AND.

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## You are wrong...

You don&#039;t understand what AND means in computer logic.<BR><BR>If you ask for all records where the country is &#039;US&#039; *AND* where the country is &#039;UK&#039;, you will *ALWAYS* get ZERO records.<BR><BR>And that is because no *single* record can possibly have two values.<BR><BR>You *MUST* use OR instead of AND here.<BR><BR>

7. Junior Member
Join Date
Dec 1969
Posts
11

## RE: You are wrong...

Of course, I didnt think of that thanks.<BR>But I still have the problem with the statement, it now reads something along the lines of <BR><BR>"SELECT * FROM Customers WHERE country = &#039;US&#039; OR country = &#039;UK&#039; OR country = &#039;IE&#039; OR "<BR><BR>

8. Senior Member
Join Date
Dec 1969
Location
Los Angeles, CA
Posts
21,192

## Did not care enough

9. Junior Member
Join Date
Dec 1969
Posts
11

## RE: Did not care enough

I read it but I didn&#039;t understand what u meant.<BR>What would "SELECT * FROM Customers WHERE 1=1" accomplish?<BR>Sorry i&#039;m quite new to ASP.<BR><BR>

10. Senior Member
Join Date
Dec 1969
Location
Los Angeles, CA
Posts
21,192

## Basically

that helps you put the OR BEFORE the whatever=something so you will never have one on the end<BR><BR><BR>For crying out loud.........TRY it and you will see.<BR><BR>Then ask for clarifications to what you do not understand<BR><BR><BR>

#### Posting Permissions

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