Use multiple Loop statements within an IF statement

Hi EE Brainstrust,

I would like to do something like this however, cannot seem to make it work

If Request.QueryString("departure")>0 Then
	Set CatProdDep=oConn.Execute("SELECT * FROM prods,prod_deps WHERE prods.brand_ID='"&SubDomain("sites.brand_ID")&"' AND prods.prod_ID="&Request.QueryString("product"))
ElseIf Request.QueryString("product")>0 Then
	Set CatProdDep=oConn.Execute("SELECT * FROM prods,prod_deps WHERE prods.brand_ID='"&SubDomain("sites.brand_ID")&"' AND prods.prod_ID=prod_deps.prod_ID AND (dep_date>Now() OR ISNULL(dep_date)) AND prod_live=TRUE AND dep_live=TRUE AND prods.prod_ID="&Request.QueryString("product")&" ORDER BY dep_date")			
ElseIf Request.QueryString("category")>0 Then
	Set CatProdDep=oConn.Execute("SELECT * FROM prods,prod_deps WHERE prods.brand_ID='"&SubDomain("sites.brand_ID")&"' AND prods.prod_ID=prod_deps.prod_ID AND (dep_date>Now() OR ISNULL(dep_date)) AND prod_live=TRUE AND dep_live=TRUE AND (prod_type="&Request.QueryString("Category")&" OR dep_type="&Request.QueryString("Category")&") ORDER BY dep_date")
	Do Until CatProdDep.EOF		
End If

Response.Write("Data here<br>")

If Request.QueryString("departure")>0 Then
	response.write("departure")

ElseIf Request.QueryString("product")>0 Or Request.QueryString("category")>0 Then
	CatProdDep.MoveNext
		Loop	
End If

Open in new window


The Statements work in singular, pulled out of the IF statement, however I want to use a format that would be intricate and identical for all 3

If there a way to use it like this, but a different way to show it or is not possible?
Graemewebber4technologiesAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
*outch*

Depending on the query strings you have different filters, but you want to output the departures??

In this case you need a clean approach:

1. Build your query string first.
2. Execute your query.
3. Output the result.

Your If statements are then separated in step 1 from the rest. And your loop is separated from the rest in step 3.

E.g. something like this:

Dim BaseQuery
Dim CommonPredicate
Dim Query

Dim Category
Dim Departure
Dim Product

BaseQuery = "SELECT * FROM prods, prod_deps"
CommonPredicate = " AND prods.prod_ID=prod_deps.prod_ID AND (dep_date>Now() OR ISNULL(dep_date)) AND prod_live=TRUE AND dep_live=TRUE "

Category = Request.QueryString("category")
Departure = Request.QueryString("departure")
Product = Request.QueryString("product")

Query = BaseQuery & "WHERE prods.brand_ID='" & SubDomain("sites.brand_ID")
If Departure > 0 Then
  Query = Query &  "' AND prods.prod_ID = " & Product
ElseIf Product > 0 Then
  Query = Query & "'" & CommonPredicate & " AND prods.prod_ID = " & Product & " ORDER BY dep_date"
ElseIf Category > 0 Then
  Query = Query & "'" & CommonPredicate & " AND (prod_type = " & Category & " OR dep_type = " & Category & ") ORDER BY dep_date"
End If

Set CatProdDep = oConn.Execute(Query)
Response.Write("Date here<br>")
Do Until CatProdDep.EOF
  If Departure>0 Then
    response.write(CatProdDep![yourColumnName])
    response.write()
  End If

  CatProdDep.MoveNext
Loop

Set CatProdDep = Nothing

Open in new window

CAVEAT: your query is a Cartesian product. Shouldn't be there a JOIN between prod and prod_deps? Is there a relationship?
0
 
Graemewebber4technologiesAuthor Commented:
The departure is a single item, whereas category and product are multiple.

Just want to use the same template for the output

The strings work. So they don't need touching. I'm just wondering how I configure this so if one of request.querystrings is called the correct output come and does error, like it does now.
0
 
Graemewebber4technologiesAuthor Commented:
I will have a play in your build query, however I don't think it would work when I want just 1 departure
0
 
ste5anSenior DeveloperCommented:
Well, the problem is, that I don't see from you code sample what you want as output.. also when you want a single row, then you don't need a loop at all.

Maybe you should post a outline of the desired result (HTML).
0
 
Graemewebber4technologiesAuthor Commented:
After taking your advice, I figured that considering I would be selecting 1 departure your code with the loop would work :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.