sql_getVAR = "SELECT DISTINCT help

[tns123j]

please bare with me here:
<%
dim VAR(2)
VAR(1)="nba"
VAR(2)="mlb"

sql_getVAR = "SELECT DISTINCT pcity FROM VAR"
Set getVAR = Conn.Execute(sql_getVAR) %>

I want several table according to the sport (nba,mlb, nfl etc...) I thought if i put the above code on an asp page (page02.asp) and then specified VAR on page01.asp with the link for NBA being /teams_02.asp?VAR=1 it would display the fields from the "nba" table.
Of course it did not saying the error was that VAR was not defined.
Can someone tell me what I am doin wrong or if you understand it, explain a better way to me. Thanks.

[glenngv]

Array index starts at 0 not 1.

<%
dim VAR(1) '1 means the upper bound of the array (the last index). so an upper bound of 1 means 2 (ubound + 1) elements.
VAR(0)="nba"
VAR(1)="mlb"

sql_getVAR = "SELECT DISTINCT pcity FROM " & Join(VAR, ",")
Set getVAR = Conn.Execute(sql_getVAR) 
%>

The sql statement will be:

SELECT DISTINCT pcity FROM nba,mlb

[tns123j]

ok i understand that. however i want the VAR to be declared in a link seperate of that .asp doc. can that be done?

i also no got an error saying the subscript was out of range.

<%
dim VAR(0)  

VAR(0)="mlb"
VAR(1)="nba"

sql_getVAR = "SELECT DISTINCT pcity FROM " & Join(VAR, ",")
Set getVAR = Conn.Execute(sql_getVAR) 
%>

[glenngv]

Why did you use dim VAR(0), I used 1 not 0 in my previous post.

Ok, i read your original post again and I think this i what you need.

page01.asp:
<a href="teams_02.asp?VAR=1">NBA</a>
<a href="teams_02.asp?VAR=2">MLB</a>
<a href="teams_02.asp?VAR=3">NFL</a>

teams_02.asp:

<%
select case Request.QueryString("VAR")
  case "1"
    sql_getVAR = "SELECT DISTINCT pcity FROM NBA"
  case "2"
    sql_getVAR = "SELECT DISTINCT pcity FROM MLB"
  case "3"
    sql_getVAR = "SELECT DISTINCT pcity FROM NFL"
  case else
    'invalid VAR, stop processing
    response.write "Invalid request!"
    response.end

    'or you may want to query a default table instead
    'sql_getVAR = "SELECT DISTINCT pcity FROM NBA"
end select
response.write "sql:" & sql_getVAR
%>

[tns123j]

as you can tell i am very new to this and well, i suck. it didn't work saying line 40 had an error. this is line 40:

<%	Do Until getVAR.EOF %>

i assume if that had a problem then the following will as well:

<%	getVAR.MoveNext
	Loop %>
</table>
</body>
</html>
<%	getVAR.Close
	Set getVAR = nothing
	Conn.Close
	Set Conn = nothing %>

Thanks for helping .

[glenngv]

Where is line 40? and what error message? Can you post the whole code?

[gwendaal]

why don't you make it easier


const var ="mlb,nba,nlf"
dim sql_getVAR

sql_getVAR = "SELECT DISTINCT pcity FROM " + split(var,",")(cint(Request.QueryString("var")))

................

you must of course control that you get a valid Request.QueryString

then just do >>>>


if (getVAR.BOF = false) and (getVAR.EOF = false) then
getVAR.MoveFirst
While (NOT getVAR.EOF)
'...get your rows here
getVAR.MoveNext()
Wend
end if

[tns123j]

i understood the Case statement post. However I do not understand the last post. could you possily explain this:

const var ="mlb,nba,nlf"
dim sql_getVAR

sql_getVAR = "SELECT DISTINCT pcity FROM " + split(var,",")(cint(Request.QueryString("var")))

[gwendaal]

you dont need at all the case statement >>>

------------------
select case Request.QueryString("VAR")
case "1"
sql_getVAR = "SELECT DISTINCT pcity FROM NBA"
case "10000000000"
.....
---------------

my code is just a shortcut for it >>>

const varS ="mlb,nba,nlf,as many as you like,...."

sql_getVAR = "SELECT DISTINCT pcity FROM " + + split(varS,",")(cint(Request.QueryString("var")))

you just split on the coma and with cint(Request.QueryString("var")) you get an integer to return the value of your array

with "mlb,nba,nlf"
mlb = 0
nba = 1
nlf =2

imagine you have 1000 possibilities !! ... case 1 to case 1000 is not realist

split(myArrayofValues,",")(number) will return it in just one line of code

[glenngv]

Why not make an array so you don't have to split?

page01.asp:
<a href="teams_02.asp?VAR=0">NBA</a>
<a href="teams_02.asp?VAR=1">MLB</a>
<a href="teams_02.asp?VAR=2">NFL</a>
...

teams_02.asp:

<%
dim dbtable, index
dbtable = Array("nba", "mlb", "nfl", "...") 
index = Request.QueryString("VAR")
if isnumeric(index) then 
  index = CInt(index)
else
  index = 0
end if
if index < 0 or index > ubound(dbtable) then 'not within range
  index = 0
end if

sql_getVAR = "SELECT DISTINCT pcity FROM " & dbtable(index)
response.write "sql:" & sql_getVAR
%>

[gwendaal]

yes of course (but to split produce an array when needed) it was just a lazy way to write it .. the effect at the end is the same

any kind of array ... the main thing is just to avoid a select case

on error resume next
index = Cint(Request.QueryString("VAR"))
If Err.Number <> 0 then
index = 0
end if

no matter the method

or a Function writed one time

Function QueryControler(QueryString)

Dim index
index = Request.QueryString(QueryString)

if isNumeric(index) then
QueryControler = index
else
QueryControler = 0
end if

End Function


or

Function QueryToArray(QueryString, anyArray)

Dim index

if isNumeric(Request.QueryString(QueryString)) then
index = Request.QueryString(QueryString)
else
index = 0
end if

QueryToArray = anyArray(index)
End Function

sql_getVAR = "SELECT DISTINCT pcity FROM " & QueryToArray("VAR",dbtable)