...

View Full Version : sql_getVAR = "SELECT DISTINCT help



tns123j
12-17-2004, 03:36 AM
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
12-17-2004, 04:34 AM
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
12-17-2004, 05:00 AM
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
12-17-2004, 05:23 AM
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
12-17-2004, 05:49 AM
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
12-17-2004, 06:21 AM
Where is line 40? and what error message? Can you post the whole code?

gwendaal
12-17-2004, 06:52 AM
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
12-18-2004, 08:53 PM
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
12-20-2004, 04:42 PM
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
12-21-2004, 02:10 AM
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
12-21-2004, 09:08 AM
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)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum