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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.